# VBA Convert .CSV to .Xlsx



## chingg1011 (Dec 21, 2022)

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 (Dec 21, 2022)

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 (Dec 21, 2022)

jdellasala said:


> 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:
> 
> 
> ...



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 (Dec 21, 2022)

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 (Dec 21, 2022)

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 (Dec 21, 2022)

chingg1011 said:


> 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 ?
> 
> ...


Yes.


----------



## alansidman (Dec 21, 2022)

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 (Dec 22, 2022)

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 (Dec 29, 2022)

HongRu said:


> Try.
> Replace this
> "TEXT; \\ ...20221220.csv" _
> with this
> ...





HongRu said:


> Try.
> Replace this
> "TEXT; \\ ...20221220.csv" _
> with this
> ...



Hi HongRu, 

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


----------



## chingg1011 (Jan 2, 2023)

chingg1011 said:


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


hello, 

anyone can help on above problem?


----------

