Altering code to treat CSV file as TXT file

[eliW]

Hi,

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

    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

 

[Richard Schollar]

Eli

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

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]

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]

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

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

Richard

 

[eliW]

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]

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]

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

 

[eliW]

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

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]

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!