TransferSpreadsheet Method (Office 2000)

Topic

New Reply

Please note: This is a followup question to my previous post (517,535) which was solved by Hans (my hero). I made it a different thread because this is a totally different issue.

I now have very workable code to import and convert an ever-changing Excel spreadsheet into the tables I need. Actually, it works fine if I do the initial import manually, answering YES when asked if the 1st row contains field names. When I try to automate this import using the following line:

DoCmd.TransferSpreadsheet acImport, , “Test Procedures”, “C:SeanSDRL 006 Matrix.xls”, True

all my work goes down the drain because Access decides to name the table fields as “F1”, “F2”, etc. My Excel file does NOT have a header row, per se, because it is set up similar to an Access crosstab query or even more like a pivot table without any summing or counting of the actual data. I DO, however want to use the 1st row as field names – there is intelligence built into them that I need for the rest of the program to work.

I guess I actually have 2 questions:
1. How can I change my DoCmd.TransferSpreadsheet to keep the 1st row of the Excel file as table field names, and
2. How can I change the same statement to import data from another worksheet in the same Excel file. I need to refer to whatever data is actually on each worksheet, not a named range (#’s of rows and columns are constantly changing)

Can anyone help? No offense taken if you tell me I’m not being clear, this is tough to actually explain — I’m happy to clarify
Kathi

Reply | Quote

Replies

1) You have already set the HasFieldNames argument of DoCmd.TransferSpreadsheet to True, so Access should use the first row as a header row. The only reason I can think of for replacing them with F1, F2 etc. is that the values in the first row would be invalid as field names in Access.

2) You can specify the worksheet to import from in the Range argument, in the form “SheetName!” (a quoted string containing the sheet name followed by an exclamation mark):

DoCmd.TransferSpreadsheet acImport, , “Test Procedures”, “C:SeanSDRL 006 Matrix.xls”, True, “MySheet!”

Reply | Quote

Yes, Hans, they actually are invalid names, I knew that but was happy with the forced results. In fact they are 1.1, 1.2, 1.3,…..4.23…..etc. I thought it was perfect when the “manual” import just changed the table field names to 11, 12, 13,…423, …. etc because with one little IIF statement I could extract the intelligence behind the value. Wonder why the 2 methods (manual vs. DoCmd) are different.

I’ll try the worksheetname! suggestion.

Kathi

Reply | Quote

The interactive method (the Import Wizard) gives you a chance to edit the field names. TransferSpreadsheet obviously can’t do this, but why it then uses the default field names F1 etc., I don’t know. Normally, you’d get around this by creating an import specification, but since the number and names of the fields will change, that is not feasible here. You could link the Excel table instead of importing it; TransferSpreadsheet will then change 1.1 to 1#1, so you should be able to adapt your code to interpret the field names.

DoCmd.TransferSpreadsheet acLink, , “Test Procedures”, “C:SeanSDRL 006 Matrix.xls”, True, “MySheet!”

Reply | Quote

Great idea. I’ve never tried the TransferSpreadsheet with linking instead of importing, but will now. I’ll let you know.

FYI, I never did change the names in the interactive mode, just loved the forced results. If this linking works smoothly, I’l love the “#” even more.

BTW, when do you sleep????

Kathi

Reply | Quote

I usually sleep about 6 hours per night

Reply | Quote

Darn, the link method produced the same results. Think I’m going to admit defeat and insist on changing the 1.1, 1.2, etc to usable field names.

Thanks for all your help.
Kathi

Reply | Quote

If you change 1.1 to 1,1 in the Excel worksheet, you should get 1#1 in the linked table (I think). If you change it to 1_1, it should be used without change in Access, irrespective of whether you import or link.

Reply | Quote

What timing, I was just in the spreadsheet trying to determine what I wanted to change to. Think I’ll go with the underscore. Wait, I can look for that character via InStr function, right?

“Regards” !!!
Kathi

Reply | Quote

You can use the Replace function in VBA.

Reply | Quote

Not to sound dumb, but “HUH?” Do I have to do that?

I’m sure I didn’t bother to explain that 1.1 refers to Subsystem1, Document1. I have 2 tables that have relationships to whatever is before or after the decimal point, if that’s what used.

I’ll test your memory here – The trick is that I need to create one big old Word file that you tried to help me with a while back. Your approach dealt with Masters and Subdocuments. I gave up on that approach based on the link you sent me detailing the problems with subdocuments. Instead I went to Helen’s site and got a workable solution that was really for Word Merge-ing.

I can’t just use 1 thru whatever the final column number is, or the Access provided F1 thru F-whatever, because the Subsystem part actually relates to the subfolder in which a document is stored AND in the final Word document I need to produce it is what triggers the next HeadingType. To make things more convoluted, well never mind, that would only make you more confused than my ineffective rambling has.

Suffice all that to say, thanks, and I’m sure after your 6 hours of sleep you’ll be bugged by me again!

Kathi

Reply | Quote