Macro to import a flat file using 'text to columns'

**miniweld** · 09-03-2008, 08:44 AM

Hi there,

I have a macro which imports a flat file into Excel using the 'text to columns' function. The macro currently works on the basis that the filename is always the same. See text below:

Workbooks.OpenText Filename:="\\srv-apps1\C\\extract\Extract_file", _
Origin:=xlMSDOS, StartRow:=2, DataType:=xlFixedWidth, FieldInfo:=Array(

_ etc etc etc

My query is that the file I am importing is produced daily and has a date stamp within the name. The file format is always the same but I wondered whether there was code to prompt me to select the file I wish to import, rather than always looking for the same file name?

Thanks in advance

**Rubeus_Hagrid** · 09-03-2008, 09:46 AM

Hi,

Do you want something like this:

Filename = Application _
    .GetOpenFilename(, , "Select the file to import")

It will open the open file dialog box to let you select the file to open.

**Cheeky Charlie** · 09-03-2008, 09:59 AM

how about this:

Sub openas()
larry = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If larry <> False Then Workbooks.OpenText larry, DataType:=xlDelimited, comma:=True
End Sub

There are a lot of arguments I didn't specify that it would be wise to.

You can also set and unset the defaultfilepath (use XL help) around this - i.e. you might want to store the current defaultfilepath at the beginning of your macro, change it to where you know these csv files are, open the file and reset the defaultfilepath back to where it was when you started.

HTH

**miniweld** · 09-03-2008, 01:29 PM

Thanks for the replies.

Cheeky Charlie does your code work on the basis that this is a csv file that is being imported? The file isn't csv file and so I have this code to import the data in Excel:

Origin:=xlMSDOS _
        , StartRow:=2, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array( _
        1, 1), Array(2, 1), Array(5, 9), Array(73, 1), Array(93, 1), Array(97, 9), Array(480, 5), _
        Array(488, 5), Array(496, 1), Array(503, 9)), TrailingMinusNumbers:=True

So what I need to do is marry some code to give me a fileopen prompt to select the file I want to import and then tag the above code to it, to import it in the correct alignment.

Can anybody help??

Mucho Gracias!

**Cheeky Charlie** · 09-03-2008, 06:00 PM

Sorry, I didn't check back to your first post carefully enough.

You got it, and you have everything you need, line 1 of my code uses the getopenfilename command to get the filename. You already have the syntax for how to open it (my bad)

HTH

**miniweld** · 09-04-2008, 04:37 AM

Thanks for the reply. I have tried this but I get a "syntax error" message

 larry = Application.GetOpenFilename("Text Files (*.txt), *.txt"), Origin:=xlMSDOS _
        , StartRow:=2, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array( _
        1, 1), Array(2, 1), Array(5, 9), Array(73, 1), Array(93, 1), Array(97, 9), Array(480, 5), _
        Array(488, 5), Array(496, 1), Array(503, 9)), TrailingMinusNumbers:=True

Does anybody know what I need to change to get this to work?

Also, how do I wrap the text of the above code?

Many thanks