Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have this macro that I used when I had Excel 2003 and now it is failing under 2007. I have a workbook that has multiple sheets. I need to save each sheet as a separate .txt.file.

    Can someone tell me why this is failing? The error is a runitome '1004' Method 'Copy' of object '_Worksheet' failed.

    [codebox]Sub createTxt()
    Dim oWS As Worksheet, oWB As Workbook
    Dim strPath As String, strFName As String
    strPath = "\\Bl-recs-goalie\professional_staff\Budgets\Administration\0 9-10\Budget\Budget_Upload\"
    For Each oWS In Worksheets
    oWS.Copy
    ActiveWorkbook.SaveAs Filename:=strPath & oWS.Name, FileFormat:=xlText
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    Next oWS
    End Sub[/codebox]

    Thanks in advance!
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Does it fail with the first sheet, or does it create one or more files, and fail then?

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='gvanhook' post='776165' date='20-May-2009 12:09']Can someone tell me why this is failing? The error is a runtime '1004' Method 'Copy' of object '_Worksheet' failed.[/quote]
    This more concise version works when run in Excel 2003 on Excel 2007 format files:

    Sub createTxt()
    Dim oWS As Worksheet
    Dim strPath As String, strFName As String

    strPath = "C:\" ' changed from your version
    Application.DisplayAlerts = False
    For Each oWS In ThisWorkbook.Worksheets
    ActiveWorkbook.SaveAs Filename:=strPath & oWS.Name, FileFormat:=xlText
    Next oWS
    Application.DisplayAlerts = True
    End Sub

    Why does your posted version need the Copy line?
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans,
    I tried copying the sheets over to a new workbook and it worked. Thanks anyway.
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  5. #5
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [attachment=83872:09_10_In...ansposed.xlsx]

    John,
    I tried using your suggested code, and while I do get a text file for each of my sheets, the txt files are being saved as sheet1.txt & sheet2.txt rather than the name of the sheet. This is an important part of the process. I also noticed that the name of one of the sheets remained as I named it, and the other became sheet2 and the name of the workbook became sheet2.txt.

    I have attached my test version of the file that has 2 sheets in it to see if you have any luck figuring out why.
    Thanks!
    Attached Files Attached Files
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='gvanhook' post='776188' date='20-May-2009 15:11']I tried using your suggested code, and while I do get a text file for each of my sheets, the txt files are being saved as sheet1.txt & sheet2.txt rather than the name of the sheet.[/quote]I get "23463112768.txt" and "2346311.txt"; so I'm not sure what's going on. Since I'm using Excel 2003 with the 2007 converter, perhaps someone with Excel 2007 can figure it out.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I tried John's code in 2007, and I get two txt files with the proper sheet tab numbers as file names, but both files have the info from sheet 2346311

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='stans' post='776263' date='21-May-2009 06:52']I tried John's code in 2007, and I get two txt files with the proper sheet tab numbers as file names, but both files have the info from sheet 2346311[/quote]Whoah, good catch, and bad diligence on my part. The active sheet gets printed every time, so we have to activate each sheet in the loop, so:
    Sub createTxt()
    Dim oWS As Worksheet
    Dim strPath As String, strFName As String

    strPath = "C:\" ' changed from your version
    Application.DisplayAlerts = False
    For Each oWS In ThisWorkbook.Worksheets
    oWS.Activate
    ActiveWorkbook.SaveAs Filename:=strPath & oWS.Name, FileFormat:=xlText
    Next oWS
    Application.DisplayAlerts = True
    ActiveWorkbook.Saved = True ' let's not save the whole thing as text
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about:

    For Each oWS In ThisWorkbook.Worksheets
    oWS.SaveAs Filename:=strPath & oWS.Name, FileFormat:=xlText
    Next oWS

    And not activating each sheet at all?

    Steve

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='sdckapr' post='776276' date='21-May-2009 10:03']How about:

    For Each oWS In ThisWorkbook.Worksheets
    oWS.SaveAs Filename:=strPath & oWS.Name, FileFormat:=xlText
    Next oWS

    And not activating each sheet at all?[/quote]
    I think you have the winner, except that that the FileFormat argument seems to require ... FileFormat:=xlTextMSDOS for worksheet saves.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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