Need a simple macro that will import any one of a number of text files in a folder

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I'm having problems coming up with a macro that will allow me to
import a text file of choice from my default file location that I have
set up with Excel which contains several text files. I want the macro
to open a "Open" file dialog window where I can then choose the text
file of choice. I also need it to open the file as delimited,
starting at row 23 and with the tab selected as the only delimiter.
Please help...


thanks,

steve
 
Steve
I have been using the following for several years to obtain a spreadsheet
from a .csv file downloaded from a bank.
You will need to add the steps for the deletion of the 22 rows not required.
Hope it helps - so long since I wrote it I cant remember why certain steps
are there - but it does work!

Sub ImportMyFile()
'**************************************************************
' Import My File Macro
' Macro to import csv file into Excel
'**************************************************************
myFile = Application.GetOpenFilename("All Files,*.*")
Workbooks.OpenText FileName:=myFile, _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
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))
End Sub


Cheers
Don
 
I like Don's suggestion to use getopenfilename to pause for the filename, but I
think I'd record a macro when I did it once manually.

There's an option inside that text to columns wizard that allows you to give it
a starting row. And you'll be assured that you parsed the data correctly.
 
thanks folks, it worked great, steve



Don Cameron said:
Steve
I have been using the following for several years to obtain a spreadsheet
from a .csv file downloaded from a bank.
You will need to add the steps for the deletion of the 22 rows not required.
Hope it helps - so long since I wrote it I cant remember why certain steps
are there - but it does work!

Sub ImportMyFile()
'**************************************************************
' Import My File Macro
' Macro to import csv file into Excel
'**************************************************************
myFile = Application.GetOpenFilename("All Files,*.*")
Workbooks.OpenText FileName:=myFile, _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
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))
End Sub


Cheers
Don
Click to expand...
 
Back
Top