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