Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    For Each WorkSheet in Workbook (Access/Excel)

    Hi!
    I am trying to write fuction in Access that will open existing Excel file.
    Save every worksheet in the same directory.
    So far I had written following:

    Option Compare Database
    Option Explicit

    Dim xl As Excel.Application
    __________________________________________
    Public Sub OpenDocument()

    Dim G As Long
    Dim strDocPath As String
    strDocPath = "C:TestTestReport.xls"

    G = Shell("RUNDLL32.EXE URL.DLL,FileProtocolHandler " & strDocPath, vbNormalFocus)

    Call SaveSingleSheet
    End Sub
    __________________________________________________ _____
    Sub SaveSingleSheet()
    'This will copy "Sheet1" to a new workbook, give it a name, save it and close it

    Sheets("Sheet1").Copy
    ActiveWorkbook.SaveAs Filename:="C:Sheet1.xls"
    ActiveWorkbook.Close
    xl.Quit

    End Sub
    __________________________________________________ _________

    Next step for me here is to have SaveSingleSheet() make into SaveEachSheet()
    using
    For Each WorkSheet in Workbook

    Can you please, push me in a right direction? Thanks

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

    Re: For Each WorkSheet in Workbook (Access/Excel)

    Try this. Note that I moved the instruction to quit Excel to OpenDocument - this procedure is responsible for starting Excel and hence also for closing it.
    I used CreateObject instead of Shell, and prefixed all Excel objects with xl where necessary.

    Dim xl As Excel.Application

    Public Sub OpenDocument()
    Dim strDocPath As String
    strDocPath = "C:TestTestReport.xls"
    Set xl = CreateObject("Excel.Application")
    xl.Workbooks.Open strDocPath
    Call SaveAllSheets
    xl.Quit
    Set xl = Nothing
    End Sub

    Sub SaveAllSheets()
    'This will copy each sheet to a new workbook, give it a name, save it and close it
    Dim wsh As Excel.Worksheet
    For Each wsh In xl.ActiveWorkbook.Worksheets
    wsh.Copy
    xl.ActiveWorkbook.SaveAs Filename:="C:" & wsh.Name & ".xls"
    xl.ActiveWorkbook.Close
    Next wsh
    Set wsh = Nothing
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For Each WorkSheet in Workbook (Access/Excel)

    Hans,
    you are so good to me as always. It worked perfectly fine!
    THANKS

    For some of the Excel files I need to make them .pdf
    I found this function and if you glance at it - will this work for me?
    I have Distiller installed so I think it should work.
    Thanks so much again,
    yeah, a function

    Sub Print2PDF()
    Dim oSheet As Worksheet
    Dim oPDF As PdfDistiller
    Dim TmpPSFile As String
    Dim PDFFile As String
    Set oSheet = ActiveSheet
    Set oPDF = New PdfDistiller
    TmpPSFile = "c:TmpPSFile.ps"
    PDFFile = "c:" & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "_" & ActiveSheet.Name & ".pdf"
    oSheet.PrintOut Copies:=1, preview:=False, _
    ActivePrinter:="Acrobat Distiller", printtofile:=True, _
    collate:=True, PrToFileName:=TmpPSFile
    oPDF.FileToPDF TmpPSFile, PDFFile, ""
    Kill TmpPSFile
    End Sub

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

    Re: For Each WorkSheet in Workbook (Access/Excel)

    I think that'll work, but can't you print directly to the "Acrobat PDF" printer?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For Each WorkSheet in Workbook (Access/Excel)

    Yeah...it almost worked before I was told we do not have Disteller installed and we do not need it anymore.
    So don't you just love working for people who do not really know what they want?
    Thanks so much for your help.

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

    Re: For Each WorkSheet in Workbook (Access/Excel)

    <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

Posting Permissions

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