Set Up Custom Import of Excel Spreadsheet to Access

[Quintios]

I posted this before but received a response that told me to go to 'Advanced' during a manual import to set up a custom import process. There was no 'Advanced' button to be found during a manual import using Access 97.

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

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.

I have found through other posts that some of the methods I'll need to use are the TransferSpreadsheet, or TableDef and Connect... I'm kinda lost here.

Thanks in advance.


Onwards,

Q-

 

[cmmrfrds]

One way is to link the excel spreadsheet and then treat it like any other access table. You can link a spreadsheet just like you would link an mdb table or tables from an odbc connection.

 

[Quintios]

That is one idea. Another one is to create a table that exactly matches what's in Excel and just dump the thing into Access straight with no manipulations. However, that is not the best solution, in my opinion. But if all else fails I'll give it a try. My next attempt is to use A2K to do the import and see if I can find that missing 'Advanced' tab that was described to me. Once I get ahold of the VBA I'll copy it over to Access 97, if that's possible...

Regardless, has anyone set up a custom import script from Excel to Access 97??



Onwards,

Q-

 

[B827]

I think the Advanced button is only available when importing csv and txt files. If all else fails you could save the spreadsheet as csv and go from there Sandy

 

[mscraig]

I don't think what you are looking for is available in a one step method. I usually handle this by importing the spreadsheet in it's predefined layout, then I do a maketable query of the imported sheet to put it in the layout/mapping I need.

 

[HowieG]

Assuming you have Excel running on the machine that is doing the imports, you could use the Excel object to give your access code complete control over the excel file. You could:
- move/delete columns
- delete extraneous headings/footers
- change the formatting of dates
- have excel do a file save as into another format (dbf/access/csv...)
Or, anything else you could manually do from within Excel to get the data prepared.

And, you could not show the excel application, so the user wouldn't see that you're actually controlling excel to do this.

 

[Quintios]

HowieG, that sounds awesome! I would really appreciate it if you could point me in the right direction. You don't have to spell it out step by step (unless, of course, I try to figure it out and can't so I come back here whining REALLY LOUDLY), but if you could help me out with some keywords to go and research, that would help out a lot.

Thanks in advance.
Onwards,

Q-

 

[Quintios]

B827,

You're absolutely right. Once I set up a custom import spec, how do I use it?

Onwards,

Q-

 

[HowieG]

Start with this:

Private Sub Command12_Click()
    Dim objXLApp As Excel.Application
    Dim objXLBook As Excel.Workbook
    Dim objXLSheet As Excel.Worksheet
    Dim txtCellVal As String
    
    Set objXLBook = GetObject("h:\temp\book1.xls")
    Set objXLApp = objXLBook.Parent
    Set objXLSheet = objXLBook.Sheets("Sheet1")
    
    txtCellVal = objXLSheet.Range("b4").Value
    MsgBox "Cell B4 = " & txtCellVal
    
    objXLSheet.Range("B:B").Insert
    
    objXLApp.Windows("book1.xls").Visible = True
    'objXLApp.Visible = True
    'use SaveCopyAs to not not interfere with original file and any linking
    objXLBook.SaveCopyAs "h:\temp\book2.xls"
    
    Set objXLApp = Nothing
    Set objXLBook = Nothing
    Set objXLSheet = Nothing
End Sub

Then go to Excel and record a macro doing the various steps you need. It's a small modification to get the Excel VBA to work in Access. The VBA is the same, just the object references need to change.

For example, Excel would use "Range("B:B&quot.Insert", and Access requires "objXLSheet.Range("B:B&quot.Insert"

Once you dim the objects you can use the pop-down lists while coding, which makes it easier.

Let me know if you need more...