DoCmd.TransferSpreadsheet

Hello,

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
End Sub

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.

Thanks!

-- Carl

Your code looks fine to me. You're importing from Excel v5, right?

Do you get an error, or does it just run happily but do nothing?

Have you tried importing data from the same spreadsheet using the Import/Export wizard to see if there are any issues that it can find?

I'll keep thinking, and post any more ideas I have...

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.

First - no probs. I'm only up here to learn, keep my mind sharp, and in the hope that someday (when/if I get some interesting work to do) I might find the answer to a problemn I encounter...

Back to the problem. Since it is only practice data, any chance you could post the spreadsheet & I'll take a look?

BTW when the wizard 'errored' did it come up with that 0 records for Primary key violations etc. or another error?

PP

Actually, yesterday during one of many experimentations, I changed the number sequence to start at 1, instead of 0.


Currently, I don't have an official Primary Key on TestTable1. However, I'll expriment with that too.

I've attached the Excel file as a txt file, because I couldn't attach it as an xls file. I think you can resave it as an xls file on your machine?

Let me know what you think. Thanks!

No time right now - need to be somewhere else - but it is the (lack of) field headers I think. Don't think you can import to an existing table without them...

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.

Thanks a million for your time and help!

-- Carl

Hello,

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.

Thanks!

--- Carl

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..?

Keep on experimenting! :)

Okay, I got it!

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.

Thanks again!

--- Carl

Glad you got it nailed in the end - I must confess that the scenario you describe didn't really occur to me. I assumed (yep, me too) that the line of code was executing, but not succeeding.

I don't know how familiar you are with the Access & VB IDE, but in future, if you set a Breakpoint on the relevant line of code you're interested in, you can 'see' it execute.

Finally, I think newer versions of Access (2k and XP) are smart enough to work out what event handlers are attached to which controls when you compile the project.