none
Newbie Help Please! Access VBA Code for Importing access fields into Excel workbook. RRS feed

  • Question

  • Ok, so I built a DB to house customer proposals. I found a code to import Access fields into excel spreadsheet. I will post below. It works great if I only have one sheet. Though our proposal workbook has separate sheets for various data input. I am wanting to tell access to open the excel workbook and put certain fields in cells on the 'Material' page, and other fields in the Cells on the 'summary' page.

    It will only put them into the first page that opens on the excel workbook.  Now I know the code has 'active sheet' in the lines, and I am thinking this is where the issue is, though I cannot figure out how to write it to open the book and put the various information on the correct pages.

    Here is the code:

    Private Sub cmdfollowupestimate_Click()
        Dim objXLApp As Object
        Dim objXLBook As Object
        Set objXLApp = CreateObject("Excel.Application")
        Set objXLBook = objXLApp.workbooks.Open("\\PMC-SRV1\Folder Redirections\sgunvaldson\Desktop\Discounted bid form-r4.xlsm")
        objXLApp.Application.Visible = True
        objXLBook.activesheet.Range("A3") = Me.[Local Labor 1]
        objXLBook.activesheet.Range("A4") = Me.[Local Labor 2]
        objXLBook.activesheet.Range("A5") = Me.[Local Labor 3]
        objXLBook.activesheet.Range("A6") = Me.[Local Labor 4]
        objXLBook.activesheet.Range("A7") = Me.[Local Labor 5]
        objXLBook.activesheet.Range("A8") = Me.[Local Labor 6]
        objXLBook.activesheet.Range("A9") = Me.[Local Labor 7]
        objXLBook.activesheet.Range("A10") = Me.[Local Labor 8]
        objXLBook.activesheet.Range("A11") = Me.[Local Labor 9]
        objXLBook.activesheet.Range("A12") = Me.[Local Labor 10]
        objXLBook.activesheet.Range("A13") = Me.[Local Labor 11]
        objXLBook.activesheet.Range("A14") = Me.[Local Labor 12]
        objXLBook.activesheet.Range("A15") = Me.[Local Labor 13]
        objXLBook.activesheet.Range("A16") = Me.[Local Labor 14]
        objXLBook.activesheet.Range("A17") = Me.[Local Labor 15]
       
    End Sub


    • Edited by StevenGunner Sunday, January 29, 2017 3:34 AM typo
    Sunday, January 29, 2017 3:30 AM

Answers

  • Hi Steven,

    Rather than use ActiveSheet, try using the Sheets property. For example:

    objXLBook.Sheets("Sheet2").Range("B3")=Me.ControlName

    Hope it helps...

    Sunday, January 29, 2017 3:34 AM

All replies

  • Hi Steven,

    Rather than use ActiveSheet, try using the Sheets property. For example:

    objXLBook.Sheets("Sheet2").Range("B3")=Me.ControlName

    Hope it helps...

    Sunday, January 29, 2017 3:34 AM
  • Worked like a charm, thank you so much.  I spent 3 hours trying to figure that out.  I have no formal coding training or experience, and figure stuff out by a lot of trial and error mixed with googling.  Such an easy fix, thanks!
    Monday, January 30, 2017 4:38 PM
  • Hi,

    You're welcome! Glad to hear you got it to work. Good luck with your project.

    Tuesday, January 31, 2017 2:01 AM