Convert .txt to Excel with different text file names.

[Legacy 352679]

Hey everyone,

I received a lot of help here before so I thought I would post another question which I'm unsure on.

I have recorded a macro that converts a text file into a spreadsheet. However, it has obviously only recorded the specific file path and name of said text file. What I would like to achieve is to have the macro prompt the user to choose the specific text file from the file path (i.e. opening up the window and letting the user double click the specific text file each time the macro is run). The ultimate goal is to have the spreadsheet be used as a template stored in the file path in which all text data will be downloaded into, and then just open the spreadsheet, run macro and then choose the desired text file to convert (the text files will all have different names).

As an additional request, is it possible to prompt the user to Save As a macro enables textbook after this macro is run, just so the user doesn't keep running it and preventing the loss of data?

Alternative: Is it possible to create a macro to do this for multiple text files at the same time, but within individual template files? For example, I have Text File A and Text File B. Run macro from the template and have it work on both text files but have two templates open so the user can continue to run other respective macros.
The current template has additional macros that I would like to run afterwards, hence why I would require it to be in separate files, I'm just not sure that this could be done since the name of the template file is [Climate Data Converter Template], and you can't have two of the same file name open at once. - This is not essential, but it might be something I'm curious about exploring later on.

Recorded code so far:

Sub Macro1()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT[This is the file path and text file name]" 'The file path would be the same, but the text file name would be different each time _
, Destination:=Range("$A$1"))
'.CommandTyoe = 1 'For some reason when I null this section of code it works perfectly fine, so I'm not sure if this part is necessary even though it was recorded? 
.Name = "[Would change each time]"
.FieldNames = True
.RowNumbers = False
.RefreshOnFileOpen= False
.RefreshStyle = xlInsertDeletedCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStarRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifiedDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Please forgive any errors in the code as I unfortunately had to type it out manually.

Cheers for any feedback as I'm still a bit new to VBA and only learning the basics through Google and Recorder.

 

[arpd123]

The following would allow a user to choose a file in a dialogue box. It stores the full path to the selected file in the string variable fileName:

Dim fileChooser as FileDialog
Dim fileName as String
set fileChooser = Application.FileDialog(msoFileDialogFilePicker)
If fileChooser.Show = -1 Then fileName = fileChooser.SelectedItems.Item(1)

 

[Legacy 352679]

Cheers. I'll try it shortly while at work and get back to you with the results!

 

[Legacy 352679]

The following would allow a user to choose a file in a dialogue box. It stores the full path to the selected file in the string variable fileName:

Dim fileChooser as FileDialog
Dim fileName as String
set fileChooser = Application.FileDialog(msoFileDialogFilePicker)
If fileChooser.Show = -1 Then fileName = fileChooser.SelectedItems.Item(1)

Just had a chance to test it, and it works great, but is it possible to optimise it to read from a certain file path? Currently it just defaults to file path X and I would like it to open file path Y where all our downloads are automatically stored.

 

[arpd123]

Just had a chance to test it, and it works great, but is it possible to optimise it to read from a certain file path? Currently it just defaults to file path X and I would like it to open file path Y where all our downloads are automatically stored.

Try:

Dim fileChooser as FileDialog
Dim fileName as String
set fileChooser = Application.FileDialog(msoFileDialogFilePicker)
fileChooser.InitialFileName = "C:\Users\Default\Documents" 'Put your desired folder here
If fileChooser.Show = -1 Then fileName = fileChooser.SelectedItems.Item(1)

 

[Legacy 352679]

Try:

Dim fileChooser as FileDialog
Dim fileName as String
set fileChooser = Application.FileDialog(msoFileDialogFilePicker)
fileChooser.InitialFileName = "C:\Users\Default\Documents" 'Put your desired folder here
If fileChooser.Show = -1 Then fileName = fileChooser.SelectedItems.Item(1)

Perfect - The first part does exactly what I want it to do, thank you. But it seems because of the

"TEXT[This is the file path and text file name]" 'The file path would be the same, but the text file name would be different each time _
, Destination:=Range("$A$1"))
'.CommandTyoe = 1 'For some reason when I null this section of code it works perfectly fine, so I'm not sure if this part is necessary even though it was recorded? 
.Name = "[Would change each time]"

Part of my code, it was working fine for that one specific file path, but wasn't converting the data for any other file path.

So, using what you have written alone, I now have the choice of selecting which file I want from where I want, but I now have the issue of not being sure how to get the macro to then convert the data.

 

[Legacy 352679]

Sorry to bump this but I had to leave my office and can no longer work on it, but never managed to figure out how to combine arpd123's code with the recorded code. I'm not encountering an error, I'm just not able to convert the data.

 

[arpd123]

Sorry to bump this but I had to leave my office and can no longer work on it, but never managed to figure out how to combine arpd123's code with the recorded code. I'm not encountering an error, I'm just not able to convert the data.

What actions did you record when you created this macro? What is it supposed to do to the text file to convert it to a spreadsheet? Can you post your full code as it is now, as you did in your original post?

 

[Legacy 352679]

What actions did you record when you created this macro? What is it supposed to do to the text file to convert it to a spreadsheet? Can you post your full code as it is now, as you did in your original post?

The actions I recorded were selecting the appropriate sheet I wanted the data in, selecting the data tab, text to excel, selected the text file, delineated it with commas and all appropriate formatting, then finally selected where the output would be displayed in the spreadsheet.

So when it recorded, it recorded the specific file path and text file I used, which is what I'm trying to give the user the option to select (the respective file in the specific file path).

Your code that you provided lets me specify where to choose said file, but then it won't run the import of data part of the code.

All I have done with the code is copied and pasted yours at the start of my original, and didn't save any changes so my original recorded code (in my OP) is unchanged, as no matter how I played with it I couldn't get it to work as desired.

 

[arpd123]

What about

Sub Macro1()
Dim fileChooser As FileDialog
Dim fileName As String
Set fileChooser = Application.FileDialog(msoFileDialogFilePicker)
fileChooser.InitialFileName = "C:\Users\Default\Documents" 'Put your desired folder here
If fileChooser.Show = -1 Then fileName = fileChooser.SelectedItems.Item(1)


With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fileName, Destination:=Range("A1"))
.FieldNames = True
.Name = "QueryTable" 'Name it what you want
.FieldNames = True
.RowNumbers = False
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeletedCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileParseType = xlDelimited
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

You have to pass the variable fileName to the QueryTables.Add function, otherwise it doesn't know what to do and macros admit no ambiguity, unfortunately. I also deleted some of the parameter values you provided, because they did not work when I tried it.