Results 1 to 11 of 11
  1. #1
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328

    Compile error_vba

    I am using access 2007 that exports and imports data from excel. When I use an 2003 excel format with fileName.xls all works fine. (The excel file includes code that prevents "do you want to save" message when opening and closing). When I use the same code with an xlsm file I get an error message. My partner, with Office 2016 has to use xlsm because of the vba in the excel file.



    The code that generates the message is for opening and closing the excel file. (need to open/close or don't get calc results)
    Code:
    Dim xl As Excel.Application, WB As Excel.Workbook
    Set xl = CreateObject("Excel.Application")
    Set WB = xl.Workbooks.Open(CurrentProject.Path & "" & .ExcelFile)

    With an xlsm file, the "Dim xl as excel.application" portion of the code generates this error message:

    "Compile Error
    Can't find project or library"

    Even if I switch back to the filename.xls excel file, get the same message. If I go back to the original Access program
    and the xls file, it works fine.

    My partner, using access 2016 gets other problems-they have to manually open and close the xlsm file to
    get results. Are there some changes in vba necessary when running this code in an Access 2003 or 2007 file in Access 2016?

    Very frustrating. Any help much appreciated.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    more than likely a references issue between Office versions. Your method is called Early Binding. You'll need to code for Late Binding and make the office versions figure it out.
    You will have to create the memory space for the objects
    Dim xl As Object, xlw As Object etc.
    and use SET statements to create the objects.
    Check out those terms
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you are missing the excel library, or have the wrong one. If you have different versions of Office, it is better to late bind. You lose intellisense but everything still works. To late bind is relatively simple. Change your code to

    Dim xl As Object
    WB As Object

    Set xl = CreateObject("Excel.Application")
    Set WB = xl.Workbooks.Open(CurrentProject.Path & "" & .ExcelFile)

  4. #4
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328
    Thanks so much-this got me past the problem that I was having. Got a new one now, not sure if related. The excel file, in xlsm format doesn't come up with the correct output even though it gets all the correct input data. Something weird about xlsm. I will work on it.

    Minor error in your reply-Dim in front of WB and need \ between "".
    Other problem, can't figure out how to click solved for your reply.

    Thanks again, much appreciated.



    Quote Originally Posted by Ajax View Post
    you are missing the excel library, or have the wrong one. If you have different versions of Office, it is better to late bind. You lose intellisense but everything still works. To late bind is relatively simple. Change your code to

    Dim xl As Object
    WB As Object

    Set xl = CreateObject("Excel.Application")
    Set WB = xl.Workbooks.Open(CurrentProject.Path & "" & .ExcelFile)

  5. #5
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328
    Not quite as good as I thought. The first exported record came back with wrong results as described in my response.. But the next one, a different record, won't import results. I get and error message saying that record was deleted to key violations. I am beginning to think that xlsm files just don't work with access. True?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Code:
    and need \ between "".
    I just copied your code

    my bad on missing the dim - just added a return to put it on a new line

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    can't figure out how to click solved for your reply
    scroll to top of thread - select Thread tools dropdown

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I am beginning to think that xlsm files just don't work with access. True?
    nope - I've recently taken over a snakepit design when excel (xlsm) opens access and runs access functions and access opens excel and runs excel functions and moving data both ways. It works but a nightmare to manage.

    with regards your error - it is telling you that you have a unique index in your destination table and trying to add a record which has the same key for an existing record. You need to inspect your source data and compare with the destination data

  9. #9
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328
    Could you expand that 2nd sentence. I don't know what Unique Index means.

    The data that goes to the xlsm file is exactly the same as the data that goes to the xlsm file. The only difference between the excel files is the xlsm and xls after the file name. Does xlsm treat the data differently?

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    in access, fields can be indexed - go into a table design and look at the field properties. A unique index just means there cannot be duplicate values - typically this is the primary key which is typically an autonumber type

    .xlsm simply means the file has macros (pre 2003 they were .xlm) and there is no difference in the treatment of data.

    when you say the data is exactly the same what does that mean? literally they contain exactly the same column headings in exactly the same named worksheet with the rows below exactly the same and in exactly the same order?, or there is a sheet with the same column headings but different values in the rows below?

    The reason this matters is excel will make assumptions about data types (number/text/date etc) based on the first few rows. So it may be that based on the rows in the .xls one datatype is assumed, and in the .xlsm a different datatype is assumed. One of the biggest issues with this is very long numbers. Excel will often truncate these to scientific notation - which means these long numbers can look the same - and are therefore not unique. Similarly some text codes which look like scientific notation will be treated as a number. Common issue with things like stock codes

  11. #11
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328
    I finally found out what is going on, though can't figure our why. The excel file has several sheets. One of the sheets has an early version of the need calculations. For some reason, the up-to-date calc sheet is using name definitions and their values from the old sheet to make the its calcs. Seems strange that it happens in xlsm, but not in xls. Must be some reason.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Why won't this program compile?
    By Lou_Reed in forum Access
    Replies: 21
    Last Post: 09-28-2017, 03:18 PM
  2. Compile from Database
    By Stefan Moser in forum Access
    Replies: 2
    Last Post: 03-05-2015, 04:23 AM
  3. Compile Error Can someone HELP
    By Z1nkstar in forum Access
    Replies: 13
    Last Post: 04-09-2014, 11:37 AM
  4. compile error ????
    By chuman vishal in forum Programming
    Replies: 1
    Last Post: 03-06-2013, 10:21 AM
  5. Why do I get a compile error?
    By shabbaranks in forum Programming
    Replies: 24
    Last Post: 10-18-2011, 07:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums