+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Marcus Aurelius
    Guest

    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.


  2. #2
    Jim Thomlinson
    Guest

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


  3. #3
    Marcus Aurelius
    Guest

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1