I currently have a process where I have multiple files in a folder that need to be converted in legacy wizard to the correct format for upload.
The macro for this is a bit above my skill level, so could do with some help. I've pasted the below, which I know doesn't work, but gives you an idea of what I need to do (hopefully)
The steps at the start I need are -
Open new workbook
Data > Get Data > Legacy Wizard > From Text (Legacy)
Select file from Y:\3.BARCLAYS BACS TFR REPORT (there are multiple files that will need converting, and each needs to be converted - between 5 to 20 files)
Delimited > Next > untick Tab and tick Comma > Convert columns 1, 2 & 3 to text > Finish
Column D to 2 decimal places
Repeat for next file in the folder
As I said, the below doesn't work, it bugs out at .CommandType = 0, but I just pasted this for reference as to what I am trying to do.
Sub Convert_BACs_Files()
'
' Convert_BACs_Files Macro
' Macro to convert all files to correct format
'
'
Application.CutCopyMode = False
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Y:\3.BARCLAYS BACS TFR REPORT\JTCL WK44.csv", Destination:=Range("$A$1" _
)) *Won't always be this file name - needs to open every file in this folder
.CommandType = 0 *Bugs out here
.Name = "JTCL WK44"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Columns("D:D").Select
Selection.NumberFormat = "0.00"
Range("A1").Select
End Sub
The macro for this is a bit above my skill level, so could do with some help. I've pasted the below, which I know doesn't work, but gives you an idea of what I need to do (hopefully)
The steps at the start I need are -
Open new workbook
Data > Get Data > Legacy Wizard > From Text (Legacy)
Select file from Y:\3.BARCLAYS BACS TFR REPORT (there are multiple files that will need converting, and each needs to be converted - between 5 to 20 files)
Delimited > Next > untick Tab and tick Comma > Convert columns 1, 2 & 3 to text > Finish
Column D to 2 decimal places
Repeat for next file in the folder
As I said, the below doesn't work, it bugs out at .CommandType = 0, but I just pasted this for reference as to what I am trying to do.
Sub Convert_BACs_Files()
'
' Convert_BACs_Files Macro
' Macro to convert all files to correct format
'
'
Application.CutCopyMode = False
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Y:\3.BARCLAYS BACS TFR REPORT\JTCL WK44.csv", Destination:=Range("$A$1" _
)) *Won't always be this file name - needs to open every file in this folder
.CommandType = 0 *Bugs out here
.Name = "JTCL WK44"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Columns("D:D").Select
Selection.NumberFormat = "0.00"
Range("A1").Select
End Sub
