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:

Please Login or Register  to view this content.

_ 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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Text to Columns

Various macro issues linked to DDE

Removal of special character from Excel sheet after data import from text file

Import Data Macro (not-overwriting certain columns)

import multiple text file to one excell workbook

Bookmarks

Bookmarks

Posting Permissions