Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    11

    Unanswered: Need help tweaking code that gets Excel files from Access Form

    Hi Folks!

    My customer service team uses 2 excel templates to prepare customer quotes at the moment. They are named after job numbers....one is the calculation sheet, and one is the "quote" sheet for the customer - indicated by a Q suffix. Example


    12345.xls
    12345q.xls

    Right now, I have code that looks at the work order number they are displaying, and goes and prints those 2 documents.

    EDIT - here's the code:

    Dim wo As String
    wo = Me!workorder
    Dim strDocPath As String
    Dim shtName As String
    strDocPath = "F:\Marketing and Customer Services\Shared\MASTER QUOTATIONS\saved quotations\" & wo & ".xls"
    shtName = "quotation"
    Dim oExcel As Excel.Application
    Dim WB As Excel.Workbook
    Dim WS As Excel.Worksheet
    Set oExcel = New Excel.Application
    Set WB = oExcel.Workbooks.Open(strDocPath)
    Set WS = WB.Worksheets(shtName)
    Call WS.PrintOut
    oExcel.Quit

    strDocPath = "F:\Marketing and Customer Services\Shared\MASTER QUOTATIONS\saved quotations\" & wo & "q.xls"
    shtName = "failure analysis"
    Set oExcel = New Excel.Application
    Set WB = oExcel.Workbooks.Open(strDocPath)
    Set WS = WB.Worksheets(shtName)
    Call WS.PrintOut
    oExcel.Quit

    I would like to modify this code to do 2 things


    a) open the Excel file on screen instead of just directly printing
    b) if the file is NOT there, go to a generic template Excel workbook - I don't know the code to check if the file exists or not.


    Any help? Much thanks in advance!

    Best Regards

    Jarrett Lee
    Last edited by OEMReman; 03-29-04 at 11:36.

  2. #2
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    For the first question, you need a command to make it visible.
    Maybe this will solve the second problem also.

    oExcel.visible = true (this may be the syntax, though I'm not sure)
    try it after the line
    Set WB = oExcel.Workbooks.Open(strDocPath)

    hopefully this will get you down the right path...
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  3. #3
    Join Date
    Mar 2004
    Posts
    11
    Appreciate the help!

Posting Permissions

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