How to automatically map Excel columns to Access fields during import?

[Quintios]

I have an Access97 database into which I need to import an Excel spreadsheet. The columns in Excel do not match up with the columns in Access (not in the same order.) The enduser will cry and complain and screw up the process of moving a couple columns over so that they match the table in Access. They will also cry when I try to explain "Named Ranges" to them (I'll also have to repeat myself a lot) because I don't need/want *all* the data in Excel, just the first 10 or so columns.

So, how can I set it up such that the Excel spreadsheet is automatically imported into the correct table with the fields mapped correctly? The spreadsheet will be in the same format every time.

Thanks in advance. Onwards,

Q-

 

[daveg232]

Try importing the file yourself, but before closing the import window click the advance button. From here you can make whats called an Import Specification (I think thats what its called) this will allow you to create a specific format to import your excell sheets. To make the correct fields go with the correct fields in your table just rename the fields to match the table field names before you save the import spec.

And if you realy want to make it "user-proof" you can write a macro to take care of the whole process. Heres some of the code youll use.

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\Public Sub subImportTable()
Dim strFileName As String

On Error GoTo ErrorHandler

strFileName = "C:\WINDOWS\Desktop\Import.txt"

DoCmd.TransferText acImportDelim, "Import Spec", "Source Table", strFileName, False

Exit Sub

ErrorHandler:

If Err.Number = 3011 Then
MsgBox "The Import.txt file is missing from your desktop."
End
Else
MsgBox ("subImportTable " & Err.Number & " " & Err.Description)
End If

End Sub
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Hope that gets ya pointed in the right direction.

 

[Quintios]

I'm just not seeing what you're describing. There's an advanced button, but it's on the first window that opens up and only offers file searching capabilities. There's no advanced tab that I can see anywhere. This is Access 97. I'll look in the help for 'Import Specification'.

Where exactly is this Advanced button supposed to be?

Thanks for the code! I'll get to work on that and see if I can come up with something.

Thanks! Onwards,

Q-

 

[daveg232]

The advance button should be in the lower left hand corner of the import window. First goto New Table, then select the file to import, once the import window comes up go through the import process then before clicking the finish button click on the advance tab. Then goto the save as button to save the new spec.

Hope this helps.

 

[larryww]

Add another sheet to the spreadsheet which is properly ordered, and grab it instead. E.g.

Sheet3!A1 = +Sheet1!B1
Sheet3!B1 = +Sheet1!C1
Sheet3!C1 = +Sheet1!A1

etc. Get the idea?

 

[larryww]

When I answered I didn't see the 2 replies immediately preceding, but actually I think this simplistic approach works here. You can even hide the sheet

 

[Quintios]

Man, I hate it stuff isn't easy.

I still don't see any 'advanced' button in the lower left, or right, or any corner.

Here's what I do, maybe I'm screwing it up in the process:

Click File
Get External Data - Import
Select Excel type spreadsheet from the combo box
Select the excel spreadsheet in question
Click 'Import' (there's the 'advanced' tab, but it's for searching for files)
Select the worksheet, click next
Select 'First Row Contains Column Headings' - Click Next
Select 'In an Existing Table' and then pick the table - Click Next

Then the final page shows up that says 'Import to Table' and has the checkered flag on it. The only buttons I see are Cancel, Back, Forward (greyed out) and Finish. There are two other check boxes for analyzing data and displaying help.

I'm gettin nuttin over here. So far Access help hasn't been much help.

I appreciate you keeping trying here. What version of Access are you using?

Q- Onwards,

Q-