Forum Discussion
VBA: How to read very very big csv file into excel quickly without excel's auto formatting?
When I use `Workbooks.open` method to open the csv file I want to use, it takes me 30 seconds. However, Excel foramtted the data automatically during file opening. It formats some values like date, ...
Hi cindy_lu
my proposal would be to use Power Query for any sort if import, so also for CSV files. I can't tell if it is faster than your solution (maybe not), but there you have highest flexibility to adjust the import and transform it to a structure that fits your needs, without coding.
If VBA is needed anyway, you could just trigger the Power Query from there.
Thanks for the suggestion.
However, this function is only one small part of the automation process I wrote in VBA.
Also, I don't need any other flexibility this time 😄
Or do you mean that I can start the Power Query then load all the data by VBA automatically?
However, this function is only one small part of the automation process I wrote in VBA.
Also, I don't need any other flexibility this time 😄
Or do you mean that I can start the Power Query then load all the data by VBA automatically?
Hi cindy_lu
yes, that's what I meant. Create a new query/connection with Power Query and load the data once. And after that, you can trigger the next import from VBA:
Either with
ActiveWorkbook.Connections("Name of your query/connection").Refresh
or
ActiveWorkbook.RefreshAll