Posts by hharvey

hharvey · Aug 30th 2016

Re: Allow user to choose which text file to import

Thanks mrmmickle1 - I have tried that but now I get a run time error 1004.

Code

Sub dce()
     '
     ' Open Text file Macro
     '
     ' Keyboard Shortcut: Ctrl+f
     '
Dim fNameAndPath As Variant
 
fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.TXT), *.TXT", Title:="Select File To Be Opened")
If fNameAndPath = False Then Exit Sub
 
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT; " & fNameAndPath, Destination:=Range("$A$1"))
        .Name = "example"
        .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 = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(23, 2, 25, 15)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveWindow.SmallScroll Down:=21
    Range("D35:D52").Select
    Selection.Copy
    If ActiveSheet.Index = Worksheets.Count Then
        Worksheets(1).Select
    Else
        ActiveSheet.Next.Select
    End If
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
End Sub

Display More

hharvey · May 26th 2016

Hi,

I am creating a macro that allows me to import data from a text file using the Text Import Wizard and some custom fixed width column settings.
It works great but....

... I would like it to pause at the 'open file' dialogue box to allow me to choose which text file to import.
At present this macro only works for the specific text file I used to create the macro, whereas I would like to be able to choose a different text file to import in future.

Any help much appreciated

Code

Sub dce()
'
' Open Text file Macro
'
' Keyboard Shortcut: Ctrl+f
'
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
          "TEXT;C:\Users\xxxxx\Documents\xxxxx\example.txt" _  , Destination:=Range("$A$1"))
        .Name = "example"
        .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 = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(23, 2, 25, 15)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveWindow.SmallScroll Down:=21
    Range("D35:D52").Select
    Selection.Copy
    If ActiveSheet.Index = Worksheets.Count Then
    Worksheets(1).Select
    Else
    ActiveSheet.Next.Select
    End If
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
End Sub

Display More