none
Reference a Specific Excel Book RRS feed

  • Question

  • Experts -

    I have some code here that formats an Excel book after export.  Works great except if there are already other Excel files open, then it breaks.

    As such, how do I reference the specific Excel workbook I want to format?

    Thank you!


    Bonediggler

    Thursday, September 14, 2017 2:53 PM

Answers

  • I imagine the problem is with these lines here:

    Set xl = CreateObject("Excel.Application")

    Set xlBook = GetObject(sPath & sFilenm)

    You should open your workbook in the xl Application you've created; then there won't be any confusion with other instances of Excel.  Try this:

        Set xl = CreateObject("Excel.Application")
        Set xlBook = xl.Workbooks.Open(sPath & sFilenm) 

    I'm not sure why you have code (not quoted) to make the Excel instance visible, since you are only formatting it and then closing it.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Bonediggler Thursday, September 14, 2017 6:03 PM
    Thursday, September 14, 2017 5:26 PM

All replies

  • What is the error message? Can you post the code? Which line is highlighted?
    Thursday, September 14, 2017 3:18 PM
  • Can you show us your code?  How are you performing the export?

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Thursday, September 14, 2017 4:43 PM
  • Public Function f_Export_Report()
    
    On Error GoTo ErrorHandler
    
    Dim sPath As String
    Dim sDate As String
    Dim xl As Excel.Application
    Dim xlBook As Excel.Workbook
    
    sPath = “\\Folders\”
    sFilenm = "File_Name.xlsx"
    
    'Shape data, export report
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "q1"
    DoCmd.OpenQuery "q2"
    DoCmd.OpenQuery "q3"
    DoCmd.TransferSpreadsheet acExport, 10, "q4”, sPath & sFilenm, True
    
    'Format Excel output
    Set xl = CreateObject("Excel.Application")
    
    Set xlBook = GetObject(sPath & sFilenm)
    xl.Visible = True
    xlBook.Windows(1).Visible = True
    
    'Format sheet
    Set xlsheet1 = xlBook.Worksheets(1)
    With xlsheet1
        Cells.Select
        Cells.EntireColumn.AutoFit
        Range("A1:F1").Select
        Selection.Font.Bold = True
        Range("A2").Select
        With ActiveWindow
            .SplitColumn = 0
            .SplitRow = 1
        End With
        ActiveWindow.FreezePanes = True
        Range("A1:F1").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent3
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
        Range("A1").Select
        ActiveWorkbook.Worksheets("q4").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("q4").Sort.SortFields.Add _
            Key:=Range("A2:A46"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets("q4").Sort.SortFields.Add _
            Key:=Range("C2:C46"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortTextAsNumbers
        ActiveWorkbook.Worksheets("q4").Sort.SortFields.Add _
            Key:=Range("D2:D46"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortTextAsNumbers
        With ActiveWorkbook.Worksheets("q4").Sort
            .SetRange Range("A1:F46")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
    
    'Clean up
    xlBook.Close True
    Set xlBook = Nothing
    xl.Quit
    Set xl = Nothing
    
    'Send email
    Call SendNotes(sFilenm, sPath)
    
    MsgBox "Report Exported and Emailed!"
    
    Exit_Sub:
    Exit Function
    
    ErrorHandler:
    MsgBox Err.Description, vbCritical
    Resume Exit_Sub
    
    End Function
    


    Bonediggler

    Thursday, September 14, 2017 4:53 PM
  • Hi,

    Thanks for posting the code. When you said "it breaks," what is happening? Are you getting an error message? If so, what does it say? If you Debug, which line is highlighted as the cause of the error?

    Thursday, September 14, 2017 5:00 PM
  • I imagine the problem is with these lines here:

    Set xl = CreateObject("Excel.Application")

    Set xlBook = GetObject(sPath & sFilenm)

    You should open your workbook in the xl Application you've created; then there won't be any confusion with other instances of Excel.  Try this:

        Set xl = CreateObject("Excel.Application")
        Set xlBook = xl.Workbooks.Open(sPath & sFilenm) 

    I'm not sure why you have code (not quoted) to make the Excel instance visible, since you are only formatting it and then closing it.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Bonediggler Thursday, September 14, 2017 6:03 PM
    Thursday, September 14, 2017 5:26 PM