Solved Access to Excel column formula

[Snowflake68]

I have modified some code that I have found on the web so that I can export a query from Access into an Excel template. In the template I have a formula that calculates two columns but I want to generate this in the code so that the formula is only there when there is a row in the Excel file.

I can do this for the first record but how do I change the code below so that it increments the rows for the recordset.

Many thanks in advance.

Public Sub createXLorder()

'Create Excel Invoice (Formatted file)

On Error GoTo ErrorHandling

    Dim xlApp As New Excel.Application
    Dim xlWrkBk As Excel.Workbook
    Dim xlSht As Excel.Worksheet
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim strFilelocation As String
    Dim strFilepath As String

        
    Dim CheckFileExists As Boolean
    Dim myValue As Object
    Dim sSql As String
    
      strFilepath = [Application].[CurrentProject].[Path] & "\Outputs\Invoice_" & Format(Now(), "yyyy_mm_dd") & ".xlsx"
        
    ' Check to see if file already exists and then delete it
        CheckFileExists = Dir(strFilepath) <> vbNulString
        If CheckFileExists Then
             MsgBox "File exists and will be deleted" ' message used for testing purposes only
            Kill strFilepath
        Else
           '''MsgBox "file does not exists" ' message used for testing purposes only
        End If
    
    'open and reference an instance of the Excel app
    
    Set xlApp = CreateObject("Excel.Application")
    'open and reference the template file
    Set xlWrkBk = xlApp.Workbooks.Open("C:\Mr H IT\MTAG\Template\InvoicePivotTemplate.xlsx")

    'reference the first sheet in the file
    Set xlSht = xlWrkBk.Sheets(1)
    
    'open the recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM qry_Export_Invoice")

    If Not (rs.BOF And rs.EOF) Then

        rs.MoveFirst

        xlRow = 1          'set the Xl row to the first row where we want to insert detail

        'loop through the recordset to get all the details
        Do While Not rs.EOF

''            'write the detail
        xlRow = xlRow + 1

        xlSht.Cells(xlRow, 1) = rs.Fields("ACCOUNT_REF")
        xlSht.Cells(xlRow, 2) = rs.Fields("CUST_ORDER_NUMBER")
        xlSht.Cells(xlRow, 3) = rs.Fields("INVOICE_DATE")
        xlSht.Cells(xlRow, 4) = rs.Fields("INVOICE_TYPE")
        xlSht.Cells(xlRow, 5) = rs.Fields("PO")
        xlSht.Cells(xlRow, 6) = rs.Fields("PO L")
        xlSht.Cells(xlRow, 7) = rs.Fields("STOCK_CODE")
        xlSht.Cells(xlRow, 8) = rs.Fields("STOCK_DESC")
        xlSht.Cells(xlRow, 9) = rs.Fields("UNIT_PRICE")
        xlSht.Cells(xlRow, 10) = rs.Fields("NCR")
        xlSht.Cells(xlRow, 11) = rs.Fields("Mtag Lot")
        xlSht.Cells(xlRow, 12) = rs.Fields("Customer_Lot")
        xlSht.Cells(xlRow, 13) = rs.Fields("PROJECT")
        xlSht.Cells(xlRow, 14) = rs.Fields("QTY_ORDER")
        xlSht.Cells(xlRow, 15) = rs.Fields("NOTES_1")
        xlSht.Cells(xlRow, 16) = rs.Fields("NOTES_2")
        xlSht.Cells(xlRow, 17) = rs.Fields("NOTES_3")

        xlSht.Cells(xlRow, 18) = "=I2*N2" ' this is the part that i need to know how to increment the row number for each record

            'move to the next record in the recordset
            rs.MoveNext
        Loop

    End If

    Set rs = Nothing
    Set db = Nothing

    xlWrkBk.SaveAs strFilepath

    xlWrkBk.Close

    Set xlWrkBk = Nothing
    Set xlApp = Nothing

        DoCmd.Close acForm, "frmMsgFormattingFile"

Exit Sub

ErrorHandling:
    MsgBox "Something went wrong, please contact the system administrator", vbCritical, "Create XL Invoice"
       Exit Sub
      
End Sub

 

[Gasman]

Just copy and paste into the other rows, just as you would do manually.?

 

[Snowflake68]

Just copy and paste into the other rows, just as you would do manually.?

thanks the it needs to be automated for the end user.

 

[theDBguy]

Which columns are you multiplying? Price and Qty?

 

[Gasman]

thanks the it needs to be automated for the end user.

Yes, and you can automate that in code?

Alternatively set a count intRow and increment it by 1 on each record and then concatenate it to the cell address ?

xlSht.Cells(xlRow, 18) = "=I" & intRow & "*N" & intRow

I wrote this once, but forgot to post it

I would go with the Copy and Paste method though.

 

[Snowflake68]

Yes, and you can automate that in code?

Alternatively set a count intRow and increment it by 1 on each record and then concatenate it to the cell address ?

xlSht.Cells(xlRow, 18) = "=I" & intRow & "*N" & intRow

I wrote this once, but forgot to post it

I would go with the Copy and Paste method though.

many thanks for this, it worked although I just had to change the line to this

xlSht.Cells(xlRow, 18) = "=I" & xlRow & "*N" & xlRow

 

[Gasman]

Well, you were already incrementing xlrow?, but it makes more sense to use the same variable