CODEWITHSUNDEEP
excelvbams-access
lcuieji
lcuieji

Reputation: 13

Output data from query to Excel : unable to work on the data in Excel

I'm having an issue with working on an Excel file from Access. I have created a query in an Access database, that I successfully exported into an specific Excel file (called "Template.xlsx"). This file contains 2 sheets : - Sheet 1 called "v_def" containing a template - Sheet 2 where the query has been exported (name is the same as the query).

What I am trying to do, from a macro in Access, is to put the data from my query into the template. Please see the code below. 

Public Sub COTATION_FORMAT()


    'Declare variables
    Dim WKB As Excel.Workbook
    Dim WKS As Excel.Worksheet
    Dim WksQuery As Excel.Worksheet
    Dim TimeStamp As String

    'Initialize variables
    Set WKB = Workbooks.Open(CurrentProject.Path & "\#Export\Template.xlsx")
    Set WKS = WKB.Sheets("V_DEF")
    Set WksQuery = WKB.Sheets("Q_EXPORT_COTATION")

    TimeStamp = FORMAT(CStr(Now), "dd.mm.yyyy_hh.mm.ss")


    On Error GoTo ErrHandle

    'Data for template left block
    With WKS
        .Range("C5").Value = WksQuery.Range("B2").Value
        .Range("C6").Value = WksQuery.Range("C2").Value
        .Range("B7").Value = WksQuery.Range("D2").Value
        .Range("B8").Value = WksQuery.Range("E2").Value
        .Range("A11").Value = WksQuery.Range("F2").Value
    End With

    'Data for template columns
    With WKS
        .Range("A17") = WKB.Sheets(2).Range("D2").Value
        .Range("B17") = WKB.Sheets(2).Range("G2").Value & " " & WKB.Sheets(1).Range("H2").Value & " " & WKB.Sheets(1).Range("I2").Value & " " & WKB.Sheets(1).Range("J2").Value & " " & WKB.Sheets(1).Range("K2").Value
        .Range("C17") = WKB.Sheets(2).Range("L2").Value & " " & WKB.Sheets(1).Range("M2").Value & " " & WKB.Sheets(1).Range("N2").Value & " " & WKB.Sheets(1).Range("O2").Value & " " & WKB.Sheets(1).Range("P2").Value
        '.Range("D17") = Wks.Sheets(2).Range("").Value
        .Range("E17") = WKB.Sheets(2).Range("Q2").Value
        .Range("F17") = WKB.Sheets(2).Range("R2").Value
        .Range("G17") = WKB.Sheets(2).Range("S2").Value
        .Range("H17") = WKB.Sheets(2).Range("T2").Value
        .Range("I17") = WKB.Sheets(2).Range("U2").Value
        .Range("J17") = WKB.Sheets(2).Range("V2").Value
        .Range("K17") = WKB.Sheets(2).Range("AC2").Value & " x " & WKB.Sheets(1).Range("AD2").Value & " x " & WKB.Sheets(1).Range("AE2").Value
        .Range("L17") = WKB.Sheets(2).Range("X2").Value
        .Range("M17") = WKB.Sheets(2).Range("W2").Value
        .Range("N17") = WKB.Sheets(2).Range("X2").Value
        .Range("O17") = Range("N17").Value * Range("J17").Value
        .Range("P17") = WKB.Sheets(2).Range("E2").Value
    End With

    'delete export data without impacting template
    'save as another file to preserve template
    WKB.Sheets(2).Delete
    WKB.SaveAs "Cotation_" & TimeStamp, xlWorkbookDefault
    'ending message
    MsgBox "Template done ! ", vbOKOnly + vbInformation, "ACCESS"



ExitHandle:
    WKB.Close
    Set WKS = Nothing
    Set WKB = Nothing
    Exit Sub

ErrHandle:
    MsgBox Err.Number & " - " & Err.Description, vbCritical, "RUNTIME ERROR"
    Resume ExitHandle

End Sub

The thing is, I have no errors while running this code but nothing happens in the Excel file. The data is not copied from one sheet to another. I must be doing something wrong while declaring my Workbook variable, but I can't see what.

If someone has any clue what I am doing wrong, please do not hesitate to share :)

I am using Office 365 on a windows 10 computer if that's helping. In the reference tools, I've got the following checked :

Thank you for your time & have a great day !

Upvotes: 1

Views: 89

Answers (1)

Krish
Krish

Reputation: 5917

like @Kostas K said, you need an Excel application object to work with Excel from Access.

It would look like this.

Dim XLA AS New Excel.Application
Dim WKB As Excel.Workbook

'Open workbook using the excel application object
Set WKB = XLA.Workbooks.Open(CurrentProject.Path & "\#Export\Template.xlsx")

Some also prefers late binding and late binding means you can remove hard references from your Access app and work with whatever excel version is available. In that case above code becomes like this

Dim XLA AS object
Dim WKB As object
SET XLA = CreateObject("Excel.Application")

'Open workbook using the excel application object
Set WKB = XLA.Workbooks.Open(CurrentProject.Path & "\#Export\Template.xlsx")

Upvotes: 1

Related Questions