VBA Convert .CSV to .Xlsx

[chingg1011]

hello expert,

I m writing a code to make use of *csv file to xls in using vba,

* the file name is changed every day
ie \\abc.20221221.csv

I got a path which is variable link like \\abc.20221221.csv , how can I write the path location to execute the text to data in vba if the path is changed to \\abc.20221219.csv ?

I set the parth at row A1 in sheet 1, (vba: range ("a1").text ) but it run failure

anyone can share what is the code to make it ?

 

[jdellasala]

I would STRONGLY recommend using Power Query rather than VBA. First you'd need a variable to hold the date.
First, bring the file into Power Query from the Excel Data -> From Text/CSV. When asked, select the Transform button. That will bring the file into Power Query with the following M Code:

let
    Source = Csv.Document(File.Contents("C:\Users\USERID\Desktop\abc20221221.csv"),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
    Source

Now, while still in the Power Query editor, in the Home tab, at the far right click New Source -> Other Sources -> Blank Query. Rename the Query1 to TodayFilename and enter this code into the address bar:

= Text.From( Date.Year(DateTime.LocalNow()) ) & Text.From( Date.Month(DateTime.LocalNow())) & Text.From(Date.Day(DateTime.LocalNow()))

Now go back to the query that pulled in the file, click on Source under Applied Steps, and change the code to:

= Csv.Document(File.Contents("C:\Users\USERID\Desktop\abc" & TodayFilename & ".csv"),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None])

Now do whatever changes are needed to be made to the file, and load it as a Table in a New Worksheet. Tomorrow, when the new file is available, simply go to Data -> Refresh All, and the table will load the new data.
Power Query is a VERY simple and easy to use interface, and doesn't take long to get the fundamentals. Search YouTube if you have questions on Power Query.

 

[chingg1011]

I would STRONGLY recommend using Power Query rather than VBA. First you'd need a variable to hold the date.
First, bring the file into Power Query from the Excel Data -> From Text/CSV. When asked, select the Transform button. That will bring the file into Power Query with the following M Code:

let
    Source = Csv.Document(File.Contents("C:\Users\USERID\Desktop\abc20221221.csv"),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
    Source

Now, while still in the Power Query editor, in the Home tab, at the far right click New Source -> Other Sources -> Blank Query. Rename the Query1 to TodayFilename and enter this code into the address bar:

= Text.From( Date.Year(DateTime.LocalNow()) ) & Text.From( Date.Month(DateTime.LocalNow())) & Text.From(Date.Day(DateTime.LocalNow()))

Now go back to the query that pulled in the file, click on Source under Applied Steps, and change the code to:

= Csv.Document(File.Contents("C:\Users\USERID\Desktop\abc" & TodayFilename & ".csv"),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None])

Now do whatever changes are needed to be made to the file, and load it as a Table in a New Worksheet. Tomorrow, when the new file is available, simply go to Data -> Refresh All, and the table will load the new data.
Power Query is a VERY simple and easy to use interface, and doesn't take long to get the fundamentals. Search YouTube if you have questions on Power Query.

Hi Jerry,

Thanks for your support, I am not sure if I have followed your guild line for power query, it seems that I need to set up power query in excel, right ? or the step as shown below as you mentioned ?

 

[jdellasala]

Your profile says you have 365 which wouldn't need the add-in. If you don't have that version, you should remove it from your profile to prevent getting advice you can't follow.
You have to download and install Microsoft Power Query for Excel.

 

[chingg1011]

Hi

seems that a restriction from my excel to download add-in for power query, can I still use code for running csv to xls by vba ?

below is current code that I need to go in to edit the day ie 20221221, 20221222....etc to extract the csv

How can I modify the code, say I can locate A9 in excel sheet (see second screen shot) for variable day changing to extract and execute text to xls via vba ?

 

[jdellasala]

Hi

seems that a restriction from my excel to download add-in for power query, can I still use code for running csv to xls by vba ?

below is current code that I need to go in to edit the day ie 20221221, 20221222....etc to extract the csv

How can I modify the code, say I can locate A9 in excel sheet (see second screen shot) for variable day changing to extract and execute text to xls via vba ?

View attachment 81375
View attachment 81376

Yes.

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[HongRu]

Try.
Replace this
"TEXT; \\ ...20221220.csv" _
with this
"TEXT;" & Range("A9").Value _


PS. I suggest you use Power Query. It will be much easier.

 

[chingg1011]

Try.
Replace this
"TEXT; \\ ...20221220.csv" _
with this
"TEXT;" & Range("A9").Value _


PS. I suggest you use Power Query. It will be much easier.

Try.
Replace this
"TEXT; \\ ...20221220.csv" _
with this
"TEXT;" & Range("A9").Value _


PS. I suggest you use Power Query. It will be much easier.

Hi HongRu,

I just tried it but seemed that was not successful, can you give me more support on this code ? thanks

 

[chingg1011]

Hi HongRu,

I just tried it but seemed that was not successful, can you give me more support on this code ? thanks


View attachment 81699

hello,

anyone can help on above problem?