Excel vba Open a .csv file with workbooks.opentext method not working

[kashif.special2005]

Hi,

I have a csv file with approx 800000 data with semicolumn delimineted, and I am using below method to open this file with delimineted, It opens the csv file but not in seperate column.

Sub opencsv()


    Workbooks.OpenText Filename:=Range("B1"), DataType:=xlDelimited, Semicolon:=True


End Sub

After opening the file I can use texttocolumn method, but if we have the method that we can open the file with delimited, and why it is not working.

Thanks
Kashif

 

[rpaulson]

your code works for me with a ; delimited text file.

 

[kashif.special2005]

Hi rpaulson,

Thanks for reply, it is .csv file and I don't know why it is not working here, please find below some sample data in the .csv file.

Sample Data:

PERIOD_START_TIME;PLMN name;RNC name;WBTS name;WBTS ID;WCEL name;WCEL ID;AVG_NON_HSDPA_PWR (M1000C138);AVE_HSPA_DL_POWER (M1000C238)

08.07.2017 00:00:00;PLMN;MSARNC2;P_BASE_TITANIUM;1232;P_BASE_TITANIUM_1;52041;18.54;26.86

08.07.2017 00:00:00;PLMN;MSARNC2;P_BASE_TITANIUM;59987;P_BASE_TITANIUM_3;52043;18.49;27.48 Thanks Kashif

<colgroup><col width="1037" style="width:778pt"> </colgroup><tbody>
</tbody>

 

[DavidAckroyd]

This is a bug (feature?) in Excel, if you open a "*.csv" file using .OPENTEXT then the other parameters are ignored, so Excel overrides your settings and opens the file as a standard Excel CSV. You can prove this by renaming your file to "*.txt" then your code will work.

This is also why when you attempt to open an actual comma delimited file using WORKBOOKS.OPENTEXT the FieldInfo param does nothing.

I suspect it worked for rpaulson because he didn't have a file named "*.csv".