Opening CSV's as Text

[GaryB]

Is there a way of forcing Excel to use the text file opening wizard to open a CSV file? The file I have is full of extraneous crud and dates written as YYYYMMDD that I can ignore or reformat if only I can get the wizard to kick in and open it as Fixed Width. Unfortunately I can't just rename the file to .txt (it's networked and nearly always in use by someone else) and, as it's usually about 25MB , I want to try and avoid opening it and resaving it as .txt before reopening it.

Thanks

GaryB

 

[Andrew Poulsom]

You can do it in VBA using the OpenText method.

Copy the file and rename it as .txt. Switch on the macro recorder and open the file, making your selections in the Text Import Wizard. Turn off the recorder and look at the code. You will be able to use that code even if the file is a CSV file.

 

[GaryB]

Thanks Andrew,

What you say seems perfectly logical so I must be doing something wrong- but I don't know what!

Workbooks.OpenText FileName:="BigFile.txt", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(4,1) ....)

works perfectly well until I change the file name to .CSV when Excel decides to completely ignore it and open the file as a bog-standard CSV.
What am I missing??

Cheers

GaryB

 

[dk]

Hi,

How about using this:-


Application.Dialogs(xlDialogImportTextFile).Show

This will allow you to select a CSV file which will then be imported using the Text import wizard. I think it may only work on Excel 2000 onwards but I can't check for certain.

_________________<font face="Impact">Hope this helps,
Dan</font>
This message was edited by dk on 2002-10-10 07:56

 

[Jim North]

Gary,

Another option is to turn on the macro recorder and walk thru the steps of manually importing the data (including taking the junk in one block). Turn it off and then you've got a good start.

 

[Lubo]

Am interested in this question as have a frequent similar problem with opening CSV files through code. The files contain dates in the format dd/mm/yyyy. When opening the file through the file menu. no problem. When opening via a macro dates which are valid as mm/dd/yyyy (e.g. 09/10/2002) appear as such, those that aren't (e.g. 30/10/2002) remain as is. No amount of reformatting or type casting gets back to the original data. My workaround is to rename as a .txt and use OpenText specifying the date format but this isn't always practical/desirable e.g. when the files are a daily download from another system. Really don't wan't any dialog to appear as don't want any interaction from the users.

I can't find this behaviour documented anywhere as a MS bug. Any suggestions appreciated.

Lubo

 

[Andrew Poulsom]

Lubo,

See:

http://216.92.17.166/board/viewtopic.php?topic=23924&forum=2

The OP said he got OpenText to work, which is why I posted my reply above. But it seems it doesn't.

 

[Lubo]

Andrew,

Thanks for info. The related question made very interesting reading.

Still not clear if this is a Microsoft recognised problem or not. Or if the TextToColumns approach will work for me, but am looking forward to trying it!

Thanks
Lubo

 

[GaryB]

Dan,

Thanks for that suggestion. It seems to open the file ok. Can I amend the OpenText lines above to use this method? I thought I'd just re-record the macro using this method but I'm damned if I can see where to launch it from without using the macro and the help file doesn't!

Cheers

GaryB

 

[enoid]

I found that if you ChDir first, the code will open a CSV file. I don't know why it works, but it seems to.

ChDir "C:\myFileFolder"
Workbooks.Open Filename:= _
"C:\myFileFolder\data.csv"