Using a macro to delimit a file that changes name

[goksufan]

I am trying to use a macro to delimit a file that is created daily so it changes name all the time. The formarmat of data remains the same. So I need to choose the file location and file name then run the macro.

Here is what I have for delimiting the file:


Sub Delimit_Letter_file()
'
' Delimit_Letter_file Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;X:\backupfileuploads\LTR20160226FNL3c.", Destination:=Range("$A$2"))
.CommandType = 0
.Name = "LTR20160226FNL3c."
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(8, 9, 9, 10, 20, 20, 20, 25, 25, 25, 25, 25, 25, 25, _
25, 25, 25, 15, 15, 15, 20, 20, 8, 10, 20, 40, 40, 20, 8, 8, 19, 30, 35, 35, 35, 13, 12, 5, 48, 20, 20, 15 _
, 5, 9, 5, 4, 4, 5, 9, 5, 20, 20, 20, 20, 15, 15, 15, 15, 15, 15, 50, 6, 25, 25, 10, 1, 6, 20, 10, 30, 1, 10, _
1, 1, 8, 11)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Joe4]

Welcome to the Board!

Try this code:

Sub Delimit_Letter_file()
' Delimit_Letter_file Macro

'   Browse for file name
    Dim myFile
    ChDrive "X:\"
    ChDir "[COLOR=#333333]X:\backupfileuploads\[/COLOR]"
    myFile = Application.GetOpenFilename("All Files,*.*")
    If myFile = False Then
        Exit Sub
    End If

'   Open file
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & myFile, Destination:=Range("$A$2"))
        .CommandType = 0
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
            1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
            , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(8, 9, 9, 10, 20, 20, 20, 25, 25, 25, 25, 25, 25, 25, _
            25, 25, 25, 15, 15, 15, 20, 20, 8, 10, 20, 40, 40, 20, 8, 8, 19, 30, 35, 35, 35, 13, 12, 5, 48, 20, 20, 15 _
            , 5, 9, 5, 4, 4, 5, 9, 5, 20, 20, 20, 20, 15, 15, 15, 15, 15, 15, 50, 6, 25, 25, 10, 1, 6, 20, 10, 30, 1, 10, _
            1, 1, 8, 11)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
End Sub

 

[goksufan]

Thanks Joe

I tried that code and I am getting a run-time error '5' invalid procedure call or argument at the command type =0



I did find another post and it works but the file has to be a text file to work. The files I am doing this with are Flat files and don't have the txt extension. so a bit of a pain to resave the file as .txt

Here is code that does work.

Sub ImportTextFile()
Dim fName As String
fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fName = "False" Then Exit Sub

With ActiveSheet.QueryTables.Add(Connection:= _
"Text;X:\backupfileuploads\LTR20160226FNL3c.", Destination:=Range("$A$3"))
.Name = "LTR20160226FNL3c."
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(8, 9, 9, 10, 20, 20, 20, 25, 25, 25, 25, 25, 25, 25, _
25, 25, 25, 15, 15, 15, 20, 20, 8, 10, 20, 40, 40, 20, 8, 8, 19, 30, 35, 35, 35, 13, 12, 5, 48, 20, 20, 15 _
, 5, 9, 5, 4, 4, 14, 5, 20, 20, 20, 20, 15, 15, 15, 15, 15, 15, 50, 6, 25, 6, 25, 10, 1, 20, 20, 10, 30, 1, 10, _
1, 1, 19)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Joe4]

The code does pretty much the same thing mine does, except it does not have the CommandType property, so try removing it and see if it works, i.e.

Sub Delimit_Letter_file()
' Delimit_Letter_file Macro

'   Browse for file name
    Dim myFile
    ChDrive "X:\"
    ChDir "[COLOR=#333333]X:\backupfileuploads\[/COLOR]"
    myFile = Application.GetOpenFilename("All Files,*.*")
    If myFile = False Then
        Exit Sub
    End If

'   Open file
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & myFile, Destination:=Range("$A$2"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
            1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
            , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(8, 9, 9, 10, 20, 20, 20, 25, 25, 25, 25, 25, 25, 25, _
            25, 25, 25, 15, 15, 15, 20, 20, 8, 10, 20, 40, 40, 20, 8, 8, 19, 30, 35, 35, 35, 13, 12, 5, 48, 20, 20, 15 _
            , 5, 9, 5, 4, 4, 5, 9, 5, 20, 20, 20, 20, 15, 15, 15, 15, 15, 15, 50, 6, 25, 25, 10, 1, 6, 20, 10, 30, 1, 10, _
            1, 1, 8, 11)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
End Sub