# Solved: Save CSV files in XLS format



## gmoukled (Oct 17, 2011)

I have a folder which contains 3 csv files, call them X, Y, Z.
I need to import the 3 files to an excel spread sheet, in which i already have 3 headers on sheet 1, I want to add header X for file X, header Y for file Y and header Z for file Z, and then after I import the 3 files i need to save everything in XLS format.

In the code I pasted: X is "HA Andon Totalized Report", and Range("A4:G4").Select is where the header X located on sheet one. 


I ran a macro to import only one file and it did work.
But I need to modify the code to import all the 3 files and add the specific header to each file at the same time.

How can i do that?

This is the code for importing one file:

Sub Macro1()
'
' Macro1 Macro
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\TZFTQF\Fall 2011 Coop term\Excel Files 2\HA Andon Totalized Report.csv" _
, Destination:=Range("$A$1"))
.Name = "HA Andon Totalized Report"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
Range("A4:G4").Select
Selection.Copy
Sheets("Sheet3").Select
Cells.Select
ActiveSheet.Paste
Selection.ColumnWidth = 10
Range("A1:G14").Select
Selection.Columns.AutoFit
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ChDir "C:\Users\TZFTQF\Fall 2011 Coop term\Excel Files 2"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\TZFTQF\Fall 2011 Coop term\Excel Files 2\SpreadSheet.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub


----------



## Keebellah (Mar 27, 2008)

Hi, You should know that code is better placed in the right frame, okay.


```
[COLOR="Red"]Sub RunAll()
' This is just to demonstrate how you can use your macro to import a file and save it.
' What you need to do is make it possible to select a file and run the same macro

Dim ImportFilePath As String, ExportFilePath As String
Dim File2Import As String, File2Export As String
ImportFilePath = "C:\Users\TZFTQF\Fall 2011 Coop term\Excel Files 2"
ExportFilePath = "C:\Users\TZFTQF\Fall 2011 Coop term\Excel Files 2"
File2Import = "HA Andon Totalized Report.csv"
File2Export = "SpreadSheet"
Call Macro1(ImportFilePath, File2Import, ExportFilePath, File2Export)


End Sub[/COLOR]


Sub Macro1([COLOR="red"]ImportFilePath As String, File2Import As String, ExportFilePath As String, File2Export As String[/COLOR])
'
' Macro1 Macro
' You msut pass the 4 (four) parameters
' ImportFilePath = that is the fullpath where the file can be found (no blackslash at the end!!!)
' File2Import    = the name of the file to import
' ExportFilePath = the Fullpath of where the file has to be saved to (no blackslash at the end!!!)
' File2Export    = The filename to be saved, the extension is added in the code

'
With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & ImportFilePath & "\" & File2Import, Destination:=Range("$A$1"))
    .Name = "HA Andon Totalized Report"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

Sheets("Sheet1").Select
Range("A4:G4").Select
Selection.Copy
Sheets("Sheet3").Select
Cells.Select
ActiveSheet.Paste
Selection.ColumnWidth = 10
Range("A1:G14").Select
Selection.Columns.AutoFit
Application.CutCopyMode = False
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
' ChDir "C:\Users\TZFTQF\Fall 2011 Coop term\Excel Files 2"  '  <<< Unnecessary line of code
ActiveWorkbook.SaveAs Filename:=ExportFilePath & "\" & File2Export & ".xlsm", _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub
```
I don't know how good you are with VBA but this is the general idea, The macro RunAll just runs one file, the one in your posting, but you could add aa loop or something to select a new file and run it again.

See if what I have posted helps you, trial and error that's the way to do it and you will remember it better.

Just holler if you need assistance.


----------



## gmoukled (Oct 17, 2011)

Thank you.

But now I have a problem with this line: With ActiveSheet.QueryTables.Add(Connection:= _...

the imported csv file gets copied on the active sheet not on the sheet i want it to be saved on.

for example : in the code above, if I am not on sheet 3 , the code runs but the csv files gets copied on the active sheet not on sheet 3 
I tried to replace that line of code i mentioned above but it did not work

any idea?

thanks


----------



## Keebellah (Mar 27, 2008)

Then you must make sure the activesheet is sheet 3, seems logic, doen't it

First line in my part of the code:
Sheets("Sheet3").Activate


----------



## Keebellah (Mar 27, 2008)

Rule number 1 in programming and coding

Make sure that the source *and* target are clearly defined.

A macroi is just a 'dumb' compilation of lines of code that do exactly what you tell it to do.
So you need to include your own checks and verifications.
Your code did not mention anything about which shet it starts in so it's just plain code.


----------



## gmoukled (Oct 17, 2011)

I used that command within the "with" statement that is y i was getting an error!

Thank you


----------



## gmoukled (Oct 17, 2011)

The Code is work perfectly now.
I made it to work for 12 different csv files.
And this would be my last Question.

I am going to run this macro for 12 different csv files every day. So I would like to save every day's work on a separate spreadsheet.


```
Sub RunAll()
' This is just to demonstrate how you can use your macro to import a file and save it.
' What you need to do is make it possible to select a file and run the same macro

Dim ImportFilePath As String, ExportFilePath As String
Dim File2Import As String, File2Export As String
ImportFilePath = "C:\Users\TZFTQF\Desktop\Raw Files"
ExportFilePath = "C:\Users\TZFTQF\Desktop\XLS Files"
File2Import = "HA Andon Totalized Report.csv"
File2Export = "SpreadSheet"
Call Macro1(ImportFilePath, File2Import, ExportFilePath, File2Export)
Call Macro2(ImportFilePath, File2Import, ExportFilePath, File2Export)
Call Macro3(ImportFilePath, File2Import, ExportFilePath, File2Export)
Call Macro4(ImportFilePath, File2Import, ExportFilePath, File2Export)
Call Macro5(ImportFilePath, File2Import, ExportFilePath, File2Export)
Call Macro6(ImportFilePath, File2Import, ExportFilePath, File2Export)
Call Macro7(ImportFilePath, File2Import, ExportFilePath, File2Export)
Call Macro8(ImportFilePath, File2Import, ExportFilePath, File2Export)
Call Macro9(ImportFilePath, File2Import, ExportFilePath, File2Export)
Call Macro10(ImportFilePath, File2Import, ExportFilePath, File2Export)
Call Macro11(ImportFilePath, File2Import, ExportFilePath, File2Export)
Call Macro12(ImportFilePath, File2Import, ExportFilePath, File2Export)


 ChDir "C:\Users\TZFTQF\Desktop\XLS Files"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\TZFTQF\Desktop\XLS Files\SpreadSheet.xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

Sub Macro1(ImportFilePath As String, File2Import As String, ExportFilePath As String, File2Export As String)
'
' Macro1 Macro
'

    Sheets("Sheet2").Activate
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\TZFTQF\Desktop\Raw Files\HA Andon Totalized Report.csv" _
        , Destination:=Range("$A$1"))
        .Name = "HA Andon Totalized Report"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Sheet1").Select
    Range("A4:G4").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Cells.Select
    Selection.Insert Shift:=xlDown
    Selection.ColumnWidth = 10
    Range("A1:G150").Select
    Selection.Columns.AutoFit
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
End Sub
```
I tried to replace this : 

```
ChDir "C:\Users\TZFTQF\Desktop\XLS Files"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\TZFTQF\Desktop\XLS Files\SpreadSheet.xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
```
with this:

```
ChDir "C:\Users\TZFTQF\Desktop"
application.GetSaveAsFilename
```
And in the pop up window I chose my desktop to save the file on but this did not work!

Any idea?


----------



## gmoukled (Oct 17, 2011)

I only copied up until macro 1 because the rest 11 macros are almost similar.


----------



## Keebellah (Mar 27, 2008)

The syntax is no good. Application.GetSaveFileAsFilename

You *don't need the chdir* I mentioned that in the macro

If you want to save the ActiveWorkbook to your Desktop as Spreadsheet then just tell it that.


```
ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\TZFTQF\Desktop\[B][COLOR="Red"]SpreadSheet[/COLOR][/B].xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
```


----------



## Keebellah (Mar 27, 2008)

Why don't you just number the Files so you don't need a dialog, just change the filename to save for something you need like jjmmdd-01.xlsm through jjmmdd-12.xlsm
Saving files to my Desktop is not one of my favourites, especially if you are on a network, that will unnecessarilly increase the size of your profile file.

Choose a common folder if more people need to see them.


----------



## Keebellah (Mar 27, 2008)

How do you select the csv file?

I only see one csv file in the macro you posted.


----------



## gmoukled (Oct 17, 2011)

Thanks for your help.

I am going to be saving my work somewhere else then my desktop, I am just using my desktop temporarily...

And as I mentioned in an earlier post, the code i posted is not the full code, I just posted up until macro 1 because all the other macros are pretty much the same
...


----------



## Keebellah (Mar 27, 2008)

Yes I understood that, but You only need One Macro and a loop to select the different csv files, but that is something you can go into once you get more experience in VBA.
Like I mentionde, trial and error is the way I learned and expecially not be afraid to try new things.


----------



## gmoukled (Oct 17, 2011)

I just tried to replace the 12 files i was working on , with 12 new files with the same names , I used the same paths , i did not change anything and I am getting

Error 1004: Excel can not find the text file to refresh this external data range, check to make sure the text file has not been removed or renamed, then try to refresh again.

If I click Debug, this line of the code gets highlighted:

```
.Refresh BackgroundQuery:=False
```
How can i fix this?


----------



## Keebellah (Mar 27, 2008)

The problem is that your link is a MsQuery link and not just read a file.
Could you post one csv file and also what you process before you save it as Excel so that I can take a look.
You should not use the External data query (MsQuery) but just open the file , process it and then same it as an Excel file


----------



## gmoukled (Oct 17, 2011)

I can't post the file I am sorry.

What do you mean by : "You should not use the External data query (MsQuery) but just open the file , process it and then save it as an Excel file"

Is that when I use the Get External Data feature ( From Text) to import the csv file?


----------



## Keebellah (Mar 27, 2008)

Yes correct, can you at least post abogus csv file with the right headers and so?


----------



## Keebellah (Mar 27, 2008)

Are there macro's in the resulting xls spreadsheet?
I mean you could create a 'Master Werkbook' that you use to trigger the different imports.and also which contains the information you craete the headers with.
As far as I understand the file does not have to be an xlsm file if there are no macro's.


----------



## gmoukled (Oct 17, 2011)

OHH man I actually modified the code to save the spreadsheet in XLS not XLSM almost an hour ago and it did work

But I did not try to replace the 12 files with 12 new files ...

Now after I read your last reply I tried that and it is FINALLY working

Thank you so much.


----------



## Keebellah (Mar 27, 2008)

Glad to have been of some help.
Don't forget to press the "Mark Solved' button if that is the case


----------



## gmoukled (Oct 17, 2011)

Is it possible to name the spreadsheet I am generating by the current day date using a VBA command?


----------



## Keebellah (Mar 27, 2008)

sure just add the date in the format you want.

For example: "Spreadsheet-"& Cstr(year(date) & "-" & cstr(month(date)) & "-" & cstr(day(date)) & ".xls"


----------



## gmoukled (Oct 17, 2011)

Thanks


----------

