excel to access

[boumbo]

i have data in excel which i need to put into access. The data needs to be "massaged" before i upload it into access. I currently have a macro in excel to do it.

this macro opens the table in access through DAO. then it loops into the excel sheet row by row while taking bits and pieces of some strings in certain columns from each row. then i upload the "massaged" data in access row by row. I do it through a command button in excel to get the filename of access database and the excel.

Now instead of having to do it in excel. I want to do it in access. I did not manage to do it.


How do i loop through the excel row by row, modify it and then insert it into access VBA?
How do i upload it to one of the table in this access?

I dont want to use the import funtion as the columns order can be different for different user. So it has to read the column header and based on this header it has to add the data from this column in a specific column in access.

any help or link i can study to do that? I did not find anything on this forum.

 

[pbaldy]

The technique is called automation. Here's a primer:

http://www.mvps.org/access/modules/mdl0006.htm

 

[boumbo]

Thanks.

I did it in access. but i find it very slow compared if i do it in excel.

I have an excel sheet of 2000 rows. so i need to upload this into access. but in access the loop of row is very slow. when i run my macro in excel to upload in access, the upload is fast compared if i loop in access.

I do insert and update SQL. why is it slow if ran macro from access button?

 

[pbaldy]

This is speculation, but I suspect it's because when you work from Excel, most of the work is being done within the same application (Excel working with Excel). When you automate Excel from Access, it is working outside itself (Access working with Excel). It seems logical to me that the first would be faster. In either case the insert is being handled by Jet, so probably no appreciable speed difference there.

 

[boumbo]

i think it is doing this 2000 times thats why it is slow:-

for loop

insert table values 1, value 2 , value3, value4 into (_
.cells(rownumber, col 1),
.LEFT(cells(rownumber, col 1),2),
.MID(cells(rownumbercol2) ,1,2))
.MID(cells(rownumbercol3) ,1,2))
.MID(cells(rownumbercol4) ,1,2))
etc...
)

next loop


I have about 20 values to insert and each time access macro needs to get the value from excel for each column then in each row...

any solution to speed up the process... or i better run this macro from excel for mass upload?

 

[pbaldy]

A couple of thoughts would be to either link to the Excel file so Access could work with it like a table or perhaps even better would be to import the file into an Access table (not the actual destination table). Either would likely be faster than automating Excel.

 

[boumbo]

I tried the transfer command for import.

but it has its limitation... for example there are some excel headers it does not like. like a two word headers(there is one space in between)

any way to get around it.

if i change header, yes it is super fast but the headers are the ones i get when i export from SAP. so i cant ask users to change it and type specific headers. the less they have to think the less error there will be in my database.

any solution to get the transfer command for headers like "Doc. Date" :-
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"exceldata", ExcelText, True

 

[pbaldy]

What version? I just tested with A2k and this imported a spreadsheet with spaces in the header (row 1). The resulting Access table had the same names.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Excel401kTest", strPath, True

 

[boumbo]

Try these headers in two columns.

Doc.no.

Inv Rec'd Date