Column separator change in VBA

[essebteyahmed]

Gentlemen,

I have a monthly report that I need to update with fresh data from a CSV file, where the column separator is ";", I changed the parameter in windows (Control Panel/ Region/ Advanced settings/ column seperator), and when I open the CSV file manually, the columns are correctly separated, but when I open the same file using the VBA statement Workbooks.open(), the file opens as if I didn't change the column separator parameter.

Any Ideas?

Thanks

 

[Joe4]

Welcome to the Board!

Open it using a Text option that allows you to set the delimiter upon Import.
One way is to go to the Data menu, and on the Get External Data ribbon, use From Text. That will invoke the Import Wizard and allow you to use any delimiter you want.
If you want the VBA code for this, simply use the Macro Recorder, and record yourself doing it manually once.

 

[wideboydixon]

If the file extension is .CSV then I think Excel mandates that the text is delimited by commas. A workaround is to rename (or copy) the file to use a different extension and then use the Workbooks.Open() method making sure you supply the Delimiter parameter appropriately.

WBD

 

[Joe4]

If the file extension is .CSV then I think Excel mandates that the text is delimited by commas.

That is one way, but it is not the only way.
It all depends on how you open the file. If you just try to use the Open command, you will experience that behavior. But if you open it using the method I described, you can import it without having to rename it.