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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Exporting multiple tabs as .CSV files with the tab name being the folder and filename 0
Saving as Sheet as a Tab Delimited Text file 2
Import a comma delimited file & match records 3
Copy two sheets from workbook & create new workbook 8
Save As xls to text (tab delimited) in encoding utf-8 0
Save ActiveWorkbook dilemma 3
Export multiple Excel sheets to a single text file 3
save file using information from a cell 1

Back
Top