I am trying to use the VB TransferSpreadsheet method with MS Access, and I can't seem to get it right. I'm following all of the instructions from the Help menu, in Access. I am writing the VB code to run off a cmd button on a form. Here is my code so far:
Private Sub cmdImportTestData_Click()
DoCmd.TransferSpreadsheet acImport, 5, "TestTable1", "C:\TestData\ImportTestData1.xls", False
Shouldn't this import data from the named spreadsheet to the TestTable1 table? I've double checked all of the syntax/names, the version of Excel, data type compatibilities, etc.
When I click on my cmdImportTestData button, nothing happens. I am new to VB, so I might be overlooking the obvious. If anyone has any insight, it would be greatly appreciated.
Let me start by saying thank you. This is the second time you have helped me, and I really appreciate it!
Actually, the VB runs with no errors at all. I put an error handler/MsgBox in it just to be sure. . . no errors. I am just writing this code to practice—as a self tutorial, so I can do this with real data some time. Since this is just practice, I made the table and the data in Excel very simple. The table has three columns: Counter, theDate, Words. The spreadsheet also has three columns (no header row). The first column is simply a number 0 – 50, the second is a series of dates, formatted like the table format, the third is a simple string, ‘This is test data.’ It’s pretty cut-and-dry.
After I posted my question, I did try to import the data through the Access File menu (Get External Data), and it DID give me an “errors.” However, it didn’t say what the problem was. For the life of me, I can’t figure out what would be causing this. I’ve used the Get External Data before without troubles—as you know, doing this isn’t really difficult at all. Hmmm?
I’ve tried this using various versions of the Excel file. As a trouble shooting technique, I saved the file in different versions, and updated the VB code each time to match. Still no luck.
The only thing I can think of is that there is some other code that needs to go along with the code I wrote. Should the code I wrote work as pretty much stand-alone code? There is other code behind the form, but it is unrelated (like “close form”).
(I almost forgot to mention. At one point, I cut and paste some data from Excel, to the table, just to make sure it would go in ok. It went in just fine using this method).
Again, thank you very much for sharing your expertise, “Pilgrim Pete.” I know this is an odd one—but those seem to always be the ones I find.
Last edited by carlstrauss; Mar 19th, 2002 at 10:37 AM.
In order to import a spreadsheet into an access table you need to have the first row of the spreadsheet match the column names in excel. For example, in Access Table 1 has 3 fields (Monday, Tuesday, Friday). The data you want to import from Excel must be under the same column headings (Monday, Tuesday, Friday). You will find that it will then allow you to import the spreadsheet. Also, when you throw in dates it gets even trickier but good luck.
Yes, I tried this after your last note (adding column headers). It worked with "Get External Data." So I was able to prove that the data is "importable." That's a huge help!
Even with that change, I can't get it to work right with TransferSpreadsheets yet (I did change the False to True, too). I will experiment some more, since I have this new information you provided, I think it's just a matter of time now.
I'll let you know, when I get it working, what the cause of the trouble was.
I still haven’t solved my problem, but I have some new information that may interest you.
I created a TranserSpreadsheet Macro, from the Access Macro tool, and it works splendidly. This has just added to my frustration. I created the macro exactly the way my VB code is written. The same method name, the same exact arguments---everything is the same! It worked perfectly the first time. I’m baffled as to why this same code doesn’t work in VB behind the form?
I just thought I’d let you know that I had a successful experiment, which has simply raised my curiosity even more.
Something that might interest you - I did the same thing, and used the wizard to convert my macro to VBA, and it put a "" at the end (where the Range parameter would be). Can't see why it should matter, but maybe it does..?
As usual, it was something really small that I overlooked! One of my co-workers noticed it. I don't know if this is true in other versions of Access or not, but I'm using Access 97 (right now), and the VB code behind the form, although correct, would not execute unless you manually and specifically set the On Click property to [Event Procedure], in the GUI.
It sounds simple enough, but I assumed (and we all know what happens when you assume), that the fact that the proper code was assigned to the button (with the ..._Click extension), that the button would default to execute the code when there was a click. Apparantly not.
At least in Access 97, you have to manually set the On Click property to [Event Procedure] IN THE GUI, or nothing happens.
Maybe this should have been obvious to me, but I'm new to Access, and I suppose I couldn't see the forest through the trees! Whoops!
Thanks a million for all of your help. I hope I didn't waste your time too much with this one. It's a bitter sweet victory.