1

This is the VBA I currently have, I have to run it twice in order to properly format. I get no error boxes my first time running it just copies the unformatted text into my tab at my destination c6

I am using fileToOpen as the file changes daily and I import it into my spreadsheet.

Can anyone help I really want to know where I am going wrong!

Sub ImportText()

Dim fileToOpen As Variant 'set this up rather than a string
Dim fileFilterPattern As String
Dim wsMaster As Worksheet
Dim wbTextImport As Workbook

Application.ScreenUpdating = False ' this turns off the screen flicker and makes the macro quicker

fileFilterPattern = "Text File (*.txt; *.csv), *.txt; *csv" 'this gives us the list of files we can choose from

fileToOpen = Application.GetOpenFilename(fileFilterPattern) ' added the fileFilterPattern in the () as it is a variable

If fileToOpen = False Then 'This will not run anything above if we do not select a file

    MsgBox " No File Selected!!!!!!!" 'This is the msg box if we do not select a file to import
Else

    Workbooks.OpenText _
                Filename:=fileToOpen, _
                DataType:=xlDelimited, _
                Tab:=True, _
                FieldInfo:=Array(Array(1, xlTextFormat))
    Selection.TextToColumns Destination:=Range("C6"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 5), Array(12, 5), Array(13, 1 _
        ), Array(14, 1), Array(15, 1)), TrailingMinusNumbers:=True
    
                

Set wbTextImport = ActiveWorkbook 'our new workbook from the text file

'Set wsMaster = ThisWorkbook.Worksheets.Add 'Our Active worksheet where code is stored
Set wsMaster = ThisWorkbook.Worksheets("Details")

wbTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("C6") 'Destination on worksheet

wbTextImport.Close False

End If 'This will not run anything above if we do not select a file

Application.ScreenUpdating = True

End Sub

I have played it by F8 but cannot find where I am wrong. I get no error boxes my first time running it just copies the unformatted text into my tab at my destination c6

BigBen
  • 46,229
  • 7
  • 24
  • 40
  • The issue is likely your use of `Selection` in `Selection.TextToColumns`. Also, you should be able to specify the fields directly within `OpenText`, instead of using `TextToColumns` afterwards. – BigBen Mar 28 '23 at 18:42
  • Hi, thanks for the response. Not sure what you mean though, what changes would you suggest? – JuniorMacro Mar 28 '23 at 19:15

0 Answers0