Saving multi-tab excel file created from comma delimited text file
 

I am importing a csv and doing a bunch of data manipulation until I end up
with a worksheet with 6 tabs. My code had stopped there previously and I
would manually File=SaveAs and change the type to .xls. This worked fine.
Now, I need to copy the file from my local machine and send it to a few
network locations which I don't want to do manually. Well, when I tried to
have VBA save the worksheet and close it with something like this:
Loc1 = "MASTER" & " " & Today & ".XLS"
Set MasterBook = ActiveWorkbook
With MasterBook
.SaveAs Filename:=Loc1
End With
Workbooks(Loc1).Close SaveChanges:=True
The file gets saved as a text file and I lose all my tabs. How can I have
the file saved as an excel file and not a text file? I both need and
appreciate any help. Thanks.

Saving multi-tab excel file created from comma delimited text file
 

The SaveAs method takes multiple arguments. All but the first are optional.
The second argument is File Type. Here you can specify to save the file as an
excel file.

..SaveAs Filename:=Loc1, FileFormat:= xlExcel?

Not to sure what version of excel you are on so change the ? appropriately...
--
HTH...

Jim Thomlinson


"Marcus Aurelius" wrote:

I am importing a csv and doing a bunch of data manipulation until I end up
with a worksheet with 6 tabs. My code had stopped there previously and I
would manually File=SaveAs and change the type to .xls. This worked fine.
Now, I need to copy the file from my local machine and send it to a few
network locations which I don't want to do manually. Well, when I tried to
have VBA save the worksheet and close it with something like this:
Loc1 = "MASTER" & " " & Today & ".XLS"
Set MasterBook = ActiveWorkbook
With MasterBook
.SaveAs Filename:=Loc1
End With
Workbooks(Loc1).Close SaveChanges:=True
The file gets saved as a text file and I lose all my tabs. How can I have
the file saved as an excel file and not a text file? I both need and
appreciate any help. Thanks.

Saving multi-tab excel file created from comma delimited text
 

Thanks Jim. I am using Excel 2002 so I wasn't sure which one to use.
The help file had these:
xlCSV, xlCSVMSDOS, xlCurrentPlatformText, xlDBF3, xlDIF, xlExcel2FarEast,
xlExcel4, xlAddIn, xlCSVMac, xlCSVWindows, xlDBF2, xlDBF4, xlExcel2, xlExcel3
xlExcel4Workbook, xlExcel5, xlExcel7, xlExcel9795, xlHtml, xlIntlAddIn,
xlIntlMacro, xlSYLK, xlTemplate, xlTextMac, xlTextMSDOS, xlTextPrinter,
xlTextWindows, xlUnicodeText, xlWebArchive, xlWJ2WD1, xlWJ3, xlWJ3FJ3, xlWK1,
xlWK1ALL
xlWK1FMT, xlWK3, xlWK3FM3, xlWK4, xlWKS, xlWorkbookNormal, xlWorks2FarEast,
xlWQ1, xlXMLSpreadsheet
I ended up using xlWorkbookNormal and it worked. Thanks again for your help.


"Jim Thomlinson" wrote:

The SaveAs method takes multiple arguments. All but the first are optional.
The second argument is File Type. Here you can specify to save the file as an
excel file.

.SaveAs Filename:=Loc1, FileFormat:= xlExcel?

Not to sure what version of excel you are on so change the ? appropriately...
--
HTH...

Jim Thomlinson


"Marcus Aurelius" wrote:

I am importing a csv and doing a bunch of data manipulation until I end up
with a worksheet with 6 tabs. My code had stopped there previously and I
would manually File=SaveAs and change the type to .xls. This worked fine.
Now, I need to copy the file from my local machine and send it to a few
network locations which I don't want to do manually. Well, when I tried to
have VBA save the worksheet and close it with something like this:
Loc1 = "MASTER" & " " & Today & ".XLS"
Set MasterBook = ActiveWorkbook
With MasterBook
.SaveAs Filename:=Loc1
End With
Workbooks(Loc1).Close SaveChanges:=True
The file gets saved as a text file and I lose all my tabs. How can I have
the file saved as an excel file and not a text file? I both need and
appreciate any help. Thanks.