Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Populating report from Excel (Access 2000/Excel 2000)

    I have an invoice which is received as an attachment in email. I would like to put certain data from the excel from into the access report and print it. I am not sure how to get values into text boxes on an access report from the excel form. I do not the user to see the report of anything, but just have the report to print with the proper information

    Any Ideas?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populating report from Excel (Access 2000/Excel 2000)

    If the Excel data are in tabular form, you can create a linked table in the Access database that links to the table in Excel.

    Otherwise, you will have to use Automation, i.e. use code in Access to start Excel, open the workbook and retrieve the data.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating report from Excel (Access 2000/Excel 2000)

    I am trying to do Automation, but doing it though Excel instead of Access. I am just sure how to get a textbox in the access report to equal a variable from excel. I can get the report to open and print, but I cannot get it to populate. For example, what I have is this.

    Dim objAcc7 As New Access.Application
    objAcc7.OpenCurrentDatabase "C:WindowsPOSFilesdbPOS.mdb"
    objAcc7.DoCmd.OpenReport "rpt_ExcelPickingSlipTest", acViewPreview
    objAcc7!Reports!rpt_ExcelPickingSlipTest!txt_Invoi ceID = InvoiceID *** Error on this line
    objAcc7.Quit
    Set objAcc7 = Nothing

    I get the error that the report rpt_ExcelPickingSlipTest does not exist on the line where I want to set the InvoiceID of a the txt_InvoiceID Textbox to a variable from the excel sheet

    There has got to be a way to set txtbox values in a report though automation from excel to access?

    Ideas?

    Thanks

  4. #4
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating report from Excel (Access 2000/Excel 2000)

    Hey Hans I got it [img]/forums/images/smilies/smile.gif[/img] I just could not set a value properly, but it works now [img]/forums/images/smilies/smile.gif[/img]

    Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populating report from Excel (Access 2000/Excel 2000)

    You can't do it this way reliably. The contents of a report are determined in the On Format and On Print events of the various sections of the report. If you set the value of a text box in code, it will only become effective the next time the section containing the text box is formatted. If your text box is in the report header, it will probably not get populated at all because the report header has already been formatted when the report was opened. If the text box is in the page header section, it will only become populated when the user moves to the next page, etc.

    Apart from that, you should use

    objAcc7.Reports!rpt_ExcelPickingSlipTest!txt_Invoi ceID = InvoiceID

    to indicate that Reports belongs to objAcc7. Moreover, you invoke Quit immediately after setting the text box, so any changes you make will vanish into thin air immediately.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populating report from Excel (Access 2000/Excel 2000)

    So, what did you do to make it work?

  7. #7
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating report from Excel (Access 2000/Excel 2000)

    To get the textboxes populated, I have:

    Dim objAcc7 As New Access.Application
    objAcc7.OpenCurrentDatabase "C:WindowsPOSFilesdbPOS.mdb"
    objAcc7.DoCmd.OpenReport "rpt_ExcelPickingSlipTest", acViewPreview
    objAcc7.Reports!rpt_ExcelPickingSlipTest!txt_Invoi ce = InvoiceID
    objAcc7.Reports!rpt_ExcelPickingSlipTest!txt_Consu ltantName = FirstName & " " & LastName
    objAcc7.DoCmd.OpenReport "rpt_ExcelPickingSlipTest", acViewNormal
    objAcc7.DoCmd.Close acReport, "rpt_ExcelPickingSlipTest", acSaveNo
    objAcc7.Quit
    Set objAcc7 = Nothing

    Basically I just open the report, give the direct path to the object, (since I have an instance of the access db object, I technically have access to all the content in the db), and just set the value of the specific text field that I wanted, such as:

    objAcc7.Reports!rpt_ExcelPickingSlipTest!txt_Consu ltantName = FirstName & " " & LastName

    I am still working on populating the subreports, but I have a few ideas on those

  8. #8
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating report from Excel (Access 2000/Excel 2000)

    Couple quickies

    1) Can you requery a subreport as you can to a form? If so, could you show me an example? I cannot seem to get a subreport to requery, and my syntax seems right(but it isn't, otherwise it would work)

    Currently I have: objAcc7.Reports!rpt_ExcelPickingSlipTest2!RPT_Exce lFindProductsInOrderSubreport.Report.Requery
    2) In a query, if a value is unavailable in a where clause, can you give it a default value?

    Thanks

  9. #9
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating report from Excel (Access 2000/Excel 2000)

    Cool, OK the subreports work now. My query to populate the subreports were wrong [img]/forums/images/smilies/sad.gif[/img]

Posting Permissions

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