Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    WEB SAVE (Excel 2000)

    Hi all,

    I'm trying to save the third worksheet from each workbook in a folder as a separate webpage, what I have created is an infinite
    loop, that doesn't stop, and doesn't save anything...I'm sure that it is possible, does anyone see what I am doing wrong here?

    Thanks,
    Darryl.

    Sub AllFolderFiles()
    Dim wb As Workbook
    Dim TheFile As String
    Dim MyPath As String
    MyPath = "Cocuments and Settingsdfadden.TORSTARDesktopTEST"
    TheFile = Dir("*.xls")
    Do While TheFile <> ""
    Set wb = Workbooks.Open(TheFile)
    ActiveWorkbook.PublishObjects.Add( _
    xlSourceSheet, _
    Filename:=MyPath, _
    Sheet:="Edition_Attributes", _
    Source:="A141", _
    HtmlType:=xlHtmlStatic, _
    Title:="PLATE ROOM PRODUCTION REPORT").Publish
    Loop
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: WEB SAVE (Excel 2000)

    You forgot to go to the next file in the loop. Immediately above the line with Loop, insert this line:

    TheFile = Dir

    (no arguments after Dir within the loop!)

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WEB SAVE (Excel 2000)

    Try

    <code>
    Sub AllFolderFiles()
    Dim wb As Workbook
    Dim TheFile As String
    Dim MyPath As String
    MyPath = "Cocuments and Settingsdfadden.TORSTARDesktopTEST"
    TheFile = Dir("*.xls")
    Do While TheFile <> ""
    Set wb = Workbooks.Open(TheFile)
    ActiveWorkbook.PublishObjects.Add( _
    xlSourceSheet, _
    Filename:=MyPath, _
    Sheet:="Edition_Attributes", _
    Source:="A141", _
    HtmlType:=xlHtmlStatic, _
    Title:="PLATE ROOM PRODUCTION REPORT").Publish
    TheFile = Dir
    Loop
    End Sub
    </code>
    Legare Coleman

  4. #4
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: WEB SAVE (Excel 2000)

    Thanks, all the files are open, but none of them saved as webpages.

    Darryl.

  5. #5
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: WEB SAVE (Excel 2000)

    Thanks guys,
    I didn't have the correct file name...it works using this, but it only creates one page with all the sheets on one page....If I were to have a seperate web page for each indiviual sheet do I need to create another loop?

    Thanks Darryl.

    Sub AllFolderFiles1()
    Dim wb As Workbook
    Dim TheFile As String
    Dim MyPath As String
    Dim x As String
    Dim y As String
    x = Sheets("Edition_Main").Range("A1").Value
    y = Mid(x, 9, 12)
    MyPath = "Cocuments and Settingsdfadden.TORSTARDesktopTEST"
    TheFile = Dir("*.xls")
    Do While TheFile <> ""
    Set wb = Workbooks.Open(TheFile)
    ActiveWorkbook.PublishObjects.Add( _
    xlSourceSheet, _
    Filename:=y, _
    Sheet:="Edition_Attributes", _
    Source:="A141", _
    HtmlType:=xlHtmlStatic, _
    Title:="PLATE ROOM PRODUCTION REPORT").Publish
    TheFile = Dir
    Loop

    End Sub

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

    Re: WEB SAVE (Excel 2000)

    You need to change the filename within each loop. As it is you are putting them all in the same filename (variable = Y)

    Steve

  7. #7
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: WEB SAVE (Excel 2000)

    Can someone tell me why I can't access the TEST folder on my desktop, it keeps going into the "My Documents" Folder.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WEB SAVE (Excel 2000)

    Not without seeing the code. Neither of the procedures try to access the TEST folder. The first accesses a file named test in the DESKTOP folder, and the second accesses a file of unknown name (we can't see what is in cell A1 on sheet Edition_Main) in the current folder.
    Legare Coleman

Posting Permissions

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