Altering code to treat CSV file as TXT file

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,931
Hi,

I am using this partial code to import and process a CSV file generated in other application.

Code:
    myfile = Application.GetOpenFilename("CSV Files,*.csv")
    If myfile = False Then End
    Workbooks.OpenText Filename:=myfile, _....

Now, it works fine with users using Excel 2000, but with users using Excel 2003 -the Date format is reversed and all calculations goes wrong.

I went through some answers in this forum concerning this problem and found that Excel 2003 interprets CSV files directly without giving a chance to assign desired format to DATES. There was an idea to convert the csv file to txt file -and this was useful also in my case.

My question is: how can I alter this code to open a CSV file but to treat it as a TXT without forcing the user to copy or rename the original CSV file manually. Also maintaining same code for Excel 2000 and 2003 users.

I'll appreciate accepting some ideas on this.

Eli
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Eli

Maybe not a particularly 'clever' answer, but you could always rename the file immediately before you open it:

Code:
myfile = Application.GetOpenFilename("CSV Files,*.csv") 
    If myfile = False Then End
    myfileNew = Left$(myfile,Len(myfile)-3) & "txt"
    Name myfile As myfileNew
    Workbooks.OpenText Filename:=myfileNew, _....

Other options would probably include reading in the file as a textstream rather than specifically opening it, but the above will work (assuming you don't already have the same file ending .txt of course!).

Hope this helps!

Richard
 

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,931
Thank you Richard for your prompt answer!

If your procedure is renaming the CSV file to TXT file- this is not what I wanted, as it is important to me to maintain the original CSV file for other applications.

If the answer is yes - can you rename it back before leaving the code?

Eli
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Eli

Yes, if you wanted to go that route you could certainly simply rename the file at the end of your code (after you've saved it down):

Code:
...rest of code above...
Name myfileNew As myfile
End Sub

Richard
 

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,931

ADVERTISEMENT

Works fine!

I would prefer some direct approach to this problem - but so far your solution is effective and of much help.

Thank you again,

Eli
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Eli

I wasn't completely happy with my suggestion (I guess we both thought it less than perfect - altho functional). According to VBA Help for opentext, one of the arguments (FieldInfo) allows you to specify an array giving information on how each column is to be interpreted. Check out the VBA Help, and if you need any help with it, do post back.

This would seem to be a lot more elegant (and clever!) than Richard's suggestion Number 1!
 

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,931

ADVERTISEMENT

Thank you Richard, I'll try that after weekend- have a nice weekend too
Eli
 

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,931
Hi Richard,

Now I see it. I was alresdy using FieldInfo in my original macro to specify how fields should be interpreted (as u can see in Array 5 & 6) - but it seems to work only on .txt files but not .csv files (in Excel 2003) -so that's exactly the problem.

Thank u again for your concern, I am quite satisfied with your first solution!

Eli

Code:
Workbooks.OpenText Filename:=myfileNew, _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=True, OtherChar:=",", FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 4), Array(6, 4), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1))
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hey Eli :)

I am disappointed that Microsoft decided to code the OpenText method that way (to only work on .txt and not .csv) - I have checked a couple of books I have and I can't find anything that would provide an adaptation to be able to use csv files instead. It just seems so stupid that you can go to the trouble of specifying how to import your text file (ie how the columns should be interpreted) and Excel will then just go and do its own thing regardless (I know OpenText is specifically targeted at .txt files - at least I do now!).

I'm glad that my previous suggestion successfully works! It doesn't seem so unclever to me now!

Another thing about csv files that has caused me some irritation (usually because I have a few monster files open at once, all set to manual calculation) is that opening up a csv will force Excel to perform a recalculation of all open workbooks. Very frustrating if your computer locks up for a few seconds whilst it does this!

Maybe Excel2007 will improve these instances? I can only hope :)

Anyway, good luck with your Excelling!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,384
Messages
5,547,621
Members
410,804
Latest member
bluepinky
Top