Results 1 to 4 of 4

Thread: [RESOLVED] Code works but not when run by clicking a button

  1. #1

    Thread Starter
    Lively Member oswaler's Avatar
    Join Date
    Jul 2006
    Location
    U.S. of A.
    Posts
    87

    Resolved [RESOLVED] Code works but not when run by clicking a button

    I'm using Access 2003 and Excel 2003. I need to automate the importing of data from a bunch of Excel files into a single table in Access. I am very familiar with Excel VBA but have never used VBA in Access before.

    The following code (that I got from a website) works fine when I create it as a separate module and run it from the VB debugging window. It goes to the 2 files in the folder I specified and imports the data from the files. Ultimately, though, I want to create a button on a form to run this code. I made an unbound form with one button and I tried attaching the same code to the On Click event for the button as an Event Procedure. When I click the button I get an error saying:

    The expression On Click you entered as the event property setting produced the following error: Object or class does not support the set of events.
    *The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]
    *There may have been an error evaluating the function, event, or macro.

    The error is displayed in a dialog box but the debugger is not run so it doesn't show me a problem with a particular line of code (and as I said it runs fine in the debugger anyway). Is there something special I have to do to this code to get it to run from a button? I thought maybe there was a problem with my installation of Access, so I tried it on a different computer and got the same result.

    A related question: When I create the code below as a module in Access, I see the name of the module in the module list, but the Run button is greyed out. For some reason the only way I can run it is by opening it in the VB window and running it through the debugger. Why is this?

    Thanks for any help. Here is the code:

    Dim strPathFile As String, strFile As String, strPath As String
    Dim strTable As String
    Dim blnHasFieldNames As Boolean

    ' Change this next line to True if the first row in EXCEL worksheet
    ' has field names
    blnHasFieldNames = True

    ' Replace C:\Documents\ with the real path to the folder that
    ' contains the EXCEL files
    strPath = "C:\testdb\"

    ' Replace tablename with the real name of the table into which
    ' the data are to be imported
    strTable = "tblnames"

    strFile = Dir(strPath & "*.xls")
    Do While Len(strFile) > 0
    strPathFile = strPath & strFile
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    strTable, strPathFile, blnHasFieldNames

    ' Uncomment out the next code step if you want to delete the
    ' EXCEL file after it's been imported
    ' Kill strPathFile

    strFile = Dir()
    Loop
    Last edited by oswaler; Jul 30th, 2009 at 01:28 PM.
    (d/(du)[∫(fu)du]
    Occupation: A respiring organism
    Hobbies: Respiration

  2. #2
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Code works but not when run by clicking a button

    I just tried it and was able to run it from the module and the click evnt of the button as well with no errors...

    Are you placing the code in the below sub assuming that Command0 is the commandbutton?

    vb Code:
    1. Private Sub Command0_Click()
    2.    
    3. End Sub
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  3. #3

    Thread Starter
    Lively Member oswaler's Avatar
    Join Date
    Jul 2006
    Location
    U.S. of A.
    Posts
    87

    Re: Code works but not when run by clicking a button

    OK, I think I found the problem. I have both Access 2003 and 2007 installed on the same computer. I'm trying to do this work in 2003. While I was troubleshooting I found that even if I commented out all of the code, I still got the same error. So I tried making a button with the wizard that would open Excel and I still got the same error. I tried redoing all of this in Access 2007 and it all worked fine. So it seems that 2007 was interfering with the functionality of 2003. I'm just doing it all in 2007 and saving in the 2003 format.

    Thanks for the help guys!

    -Eric
    (d/(du)[∫(fu)du]
    Occupation: A respiring organism
    Hobbies: Respiration

  4. #4
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: [RESOLVED] Code works but not when run by clicking a button

    Ah the ongoing war between 2003 and 2007!!! Glad your problem is solved
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

Posting Permissions

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



Click Here to Expand Forum to Full Width