Using VB to Import data from Excel to Access

[levanduyet]

I have one Excel file that I export from the SAP. I want to import to Access Table. I use VB6. The requirements :
_ Does not delete any rows, columns.
_ Does not open the excel file.

The data in Excel workbook as following:

http://i2.photobucket.com/albums/y4/levanduyet/ImportFromExcel.gif

Anyone can help me that how to write the sub procedure in VB6 to import these data base.

Thanks,

LVD

[CreganTur]

I develop in VBA and Visual Basic 2005, not VB6, so my solution may not work perfectly for you; you might have to make a few adjustments.

If you can work within Access, then the easiest way for you to do this would be using the DoCmd.TransferSpreadsheet method- use acImport for the first parameter to import your designated Excel file. See Access Help for more specific information on this method.

If you cannot do this through Access, then your best bet would be to create a recordset of your Excel file via an ADO connection:

Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strFilepath & ";" _
& "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

rst = New ADODB.Recordset
rst.Open "SELECT * FROM [" & szSheetName & "];", conn, adOpenStatic, adLockOptimistic

In the above code example, strFilepath is the actual filepath to your Excel file, and szSheetName is the name of the actual worksheet within the workbook that holds the data you want to look at.

If your Excel spreadsheet is going to have a different filepath every time, example: you create and save a new one every day, then let me know- I have a more specific example that shows how to use a file dialog window to grab the filepath of the chosen workbook and how to use another ADO method to grab the worksheet name off of the workbook.

HTH:thumb

[levanduyet]

rst.Open "SELECT * FROM [" & szSheetName & "];", conn, adOpenStatic, adLockOptimistic


The above code is not flexible. It require the data that should arrange properly.
As I have informed:


_ Does not delete any rows, columns.
_ Does not open the excel file.


Regards,

LVD

[CreganTur]

The above code is not flexible. It require the data that should arrange properly.


The recordset shouldn't change the arrangement of your data. Use the .Fields method to pull the records out of the recordset. It references fields using an index number. Look at Access help for more specific information.

I would expect that .Fields(0) would be your Materials field- I'm not certain though, because you have 2 blank columns within your spreadsheet... so if the recordset reads those columns then Materials could be .Fields(2)... you'll have to do a little testing to see.