Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    TransferSpreadsheet Method (Office 2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: TransferSpreadsheet Method (Office 2000)

    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!"

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet Method (Office 2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: TransferSpreadsheet Method (Office 2000)

    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!"

  5. #5
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet Method (Office 2000)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: TransferSpreadsheet Method (Office 2000)

    I usually sleep about 6 hours per night <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  7. #7
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet Method (Office 2000)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: TransferSpreadsheet Method (Office 2000)

    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.

  9. #9
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet Method (Office 2000)

    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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: TransferSpreadsheet Method (Office 2000)

    You can use the Replace function in VBA.

  11. #11
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet Method (Office 2000)

    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

Posting Permissions

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