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.

 

[Legacy 352679]

Thank you. I'll plug it in and see how it all goes.
Greatly appreciated.

 

[Legacy 352679]

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.

This does exactly what I want it to do. Thank you very much.

I'm sure I can figure out how to prompt the user to SaveAs by playing around with the recorder again.

Thanks for taking the time to help!