Convert .txt to Excel with different text file names.

Yiew33

New Member
Joined
Nov 24, 2015
Messages
49
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:
VBA Code:
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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:
VBA Code:
Dim fileChooser as FileDialog
Dim fileName as String
set fileChooser = Application.FileDialog(msoFileDialogFilePicker)
If fileChooser.Show = -1 Then fileName = fileChooser.SelectedItems.Item(1)
 
Upvote 0
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:
VBA Code:
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.
 
Upvote 0
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:
VBA Code:
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)
 
Upvote 0
Try:
VBA Code:
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
VBA Code:
"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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
What about
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top