Importing CSV just like Excel opening CSV

[Bob]

Fellow peeps

I have some CSV files exported from Requisite Pro.
When I just open them from Windows, they come in fine.

When I try to import them through VBA, the formatting is different and
my columns are screwed up.

Any ideas on exactly what parameters Excel uses during the opening of
a CSV so that I can set them programmatically?

Right now, I am using the Sheet.QueryTable method. I have played with
a bunch of the parameters with no luck. Is there another way to
import that emulates the way Excel does it from Windows?

Thanks much
Bob

 

[Guest]

Hi Bob,

Have you tried just opening the CSV file with Excel? Should import perfectly
if you do.

Regards,

OssieMac

 

[Guest]

Re-reading you post I am now not certain that I properly understood what you
meant by opening from Windows. Did you mean that you were opening with Excel
or opening with another Windows program like the editor?

Anyway I normally import csv files by simply opening them with VBA and go
from there to import the data into other applications and they work fine.

Regards,

OssieMac

 

[papou]

Hi OssieMac
If I understand correctly, I think that Bob has a problem with the opening
of CSV files in Excel using VBA.
I too have encountered such problems with CSV file (semicolon delimiter) and
found non solution to date.
Although opening manually CSV files in Excel will work, it seems that both
methods Workbooks.Open and WorkBooks.OpenTextFile will fail through VBA ie:
it opens CSV file but it does NOT distribute data correctly into columns,
even when you set -in both VBA methods- the optionnal parameters correctly.

Cordially
Pascal

 

[Bob]

Hi OssieMac
If I understand correctly, I think that Bob has a problem with the opening
of CSV files in Excel using VBA.

The above is correct. Forgetting VBA for a moment, if I see the CSV
in Windows Explorer, and open it using Excel, all is fine.

On the other hand, if I am writing VBA inside of an Excel document,
and tell Excel to import the same CSV using the Sheet.QueryTable
method (and various combinations of delimiter parameters), it does not
come in cleanly. I am discouraged by the fact that you may have dealt
with this and found no solution.

I guess at this point, I may try to programmatically open the CSV into
an independent spreadsheet (as if I did it through Windows), then copy
the sheet into the workbook where the VBA is running. Sounds like you
may have tried this but I am going to give it a shot since it won't
take too long.

Thanks
Bob

 

[Guest]

Hi Bob,

Here is an example of how I programmatically open csv files. They are opened
as an Excel workbook and I then extract the data from them. I am not aware of
any other method but am always interested in alternative methods.

Workbooks.OpenText Filename:="MyTextFile.csv", _
DataType:=xlDelimited, comma:=True

There is a mountain of info in Help if you search under 'OpenText Filename'
..It might even contain the info you need for the method you are experimenting
with.

Regards,

OssieMac