locked
Excel structure when open with Access VBA RRS feed

  • Question

  • Hi,

    I am currently working on a project of production management for a small company, and we'd like to use Access to better manage our product and after-sales services. However I'm facing some issues when I try to fetch data from an excel sheet. 

    First thing first, when testing the following function, I sometimes get a run-time Error 91; What I think that Excel didn't Select the row

        ' Select the entire Header row
        wks.Cells(HEADER_PROD_OPEN, 1).EntireRow.Select
        ' Find the appropriate row
        Set cell = Selection.Find(what:="Commande", after:=ActiveCell, lookIn:=xlFormulas, lookat:=xlWhole) ' Error line
        col_cmd = cell.Column

    When I don't get the RTE91, I get something more disturbing, the informations I'm suppose to get is shifted. Say I have the following structure

    Order # Customer Status Model Quantity Due Price
    501933793 Microsoft Production Open Heat shield 10 9/06/2015 1 440 USD

    When I run the code step by step and I try to read the Cells (2, 3), sometimes it reads correct sometimes it read a shifted cell either left or right, but never up or down.

    If you have any indication of where I should look to correct those weird bug, I would be extremely thankful.

    Alex.



    Tuesday, May 12, 2015 1:48 PM

Answers

  • Since you don't specify what Selection belongs to, it will probably create a new instance of Excel. You can avoid this by not selecting a range:

    Set cell = wks.Cells(HEADER_PROD_OPEN, 1).EntireRow.Find(What:="Commande", LookIn:=xlFormulas, LookAt:=xlWhole)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by eng.Alex.HL Tuesday, May 12, 2015 2:57 PM
    Tuesday, May 12, 2015 2:17 PM

All replies

  • Since you don't specify what Selection belongs to, it will probably create a new instance of Excel. You can avoid this by not selecting a range:

    Set cell = wks.Cells(HEADER_PROD_OPEN, 1).EntireRow.Find(What:="Commande", LookIn:=xlFormulas, LookAt:=xlWhole)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by eng.Alex.HL Tuesday, May 12, 2015 2:57 PM
    Tuesday, May 12, 2015 2:17 PM
  • Thank you,

    It's much neater this way, and after a few run of my test routine still no mistake. 

    I voluntarily create an new instance of Excel, because the code is actual run by Access. 

    I still have the shifted pattern when trying to read the cells though. So I'll keep digging.

    Thank you very much!

    Alex.

    Tuesday, May 12, 2015 2:39 PM
  • You should probably take a look at these links when you have time.

    http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

    http://www.erlandsendata.no/english/index.php?d=envbadacimportwbado

    You will find some great sample code there to integrate Excel and Access.



    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, May 12, 2015 10:54 PM