How to automate the data impor process from Excel to Access

[msand]

I have an Excel template for my clients to fill in some data and then email it back to me. For each client's info, I want to add it to the current database. The excel template looks like this:

Product Type Quantity Unit Price Discount% Final Price
xx 2 each 5 20 8
yy 4 hour 10 50 20

blalblabla

For each client, there is such a excel spreadsheet back and I have a database table to store the data. My problem is how to automate the data import process. Thanks for your help.

 

[LTunnicliffe]

Re: How to automate the data impor process from Excel to Acc

This transfers a named range from excel to access.

Sub TransferExcelDataToAccessTable() 

  Dim appAccess As Access.Application 
  Set appAccess = New Access.Application 
  
  appAccess.OpenCurrentDatabase "YOURAccessFileName.mdb" 
  
  appAccess.DoCmd.TransferSpreadsheet acImport, _ 
                                      acSpreadsheetTypeExcel97, _ 
                                      "TableNameInAcessWhereDataIsGoing", _ 
                                      "YOURExcelFileName.xls", _ 
                                      True, "ExcelRangeThatIsGoingToAcess" 
                                      
  appAccess.CloseCurrentDatabase 
  appAccess.Quit 
  
  MsgBox "Transfer Complete" 

End Sub

A couple of things:
1. Make sure you have the Access References turned on in the VBE.
2. Make sure that the Access table name is the EXACT same as the named range you are moving into it.
3. The database has to be in a shared directory.

I'm not sure if this meets your needs but I find it pretty useful.

Hope this works!

 

[msand]

Re: How to automate the data impor process from Excel to Acc

Thanks for your generous help. One problem is the rows for the clients to fill in vary. For example, one client might only have 10 rows to fill in and the other have like 200 rows. How to deal with this situation?

Also, i am new to Access VBA. Where can I put the code and execute it. I would really appreicate your detailed guidance.

 

[LTunnicliffe]

Re: How to automate the data impor process from Excel to Acc

Sub TransferExcelDataToAccessTable() 

  Dim appAccess As Access.Application 
  Set appAccess = New Access.Application 

  ActiveWorkbook.Names.Add Name:="TransferRange", RefersToLocal:=Range("A1:F" & Range("A65536").end(xlup).row)
    
  
  appAccess.OpenCurrentDatabase "YOURAccessFileName.mdb" 
  
  appAccess.DoCmd.TransferSpreadsheet acImport, _ 
                                      acSpreadsheetTypeExcel97, _ 
                                      "TableNameInAcessWhereDataIsGoing", _ 
                                      "YOURExcelFileName.xls", _ 
                                      True, "TransferRange" 
                                      
  appAccess.CloseCurrentDatabase 
  appAccess.Quit 
  
  MsgBox "Transfer Complete" 

End Sub

This is actually Excel code. You would need to put it in the workbooks that your clients are sending back to you (and which I assume you are sending to them). If you have any more questions feel free to post.

 

[msand]

Re: How to automate the data impor process from Excel to Acc

hi, Excel Master, you are fast. One related issue is: the worksheet my client fill in is not the one I am going to import because the data is not in the right format. So I actually link all these original data to another worksheet and arrange them as a table format, which is the one I want to import to Access. Since behind each number there is a link formula (or conversion formula), how can I just import the values for the range? Looking forward to hearing from you.

 

[LTunnicliffe]

Re: How to automate the data impor process from Excel to Acc

I would put this in your "clean" version of the data, the one with the links. I think, but am not positive, that the values will be imported and not the links.

LT

 

[msand]

Re: How to automate the data impor process from Excel to Acc

I put the code in the excel worksheet that has the clean data. When running it, message like this bounced up " Compile Error: User-Defined Type not defined" and when I checked the code, the first row "Dim appAccess As Access.Application" is highlighted. Any comments?

 

[LTunnicliffe]

Re: How to automate the data impor process from Excel to Acc

Whoops, you need to activate the Acess references. While in the VBE (Alt+F11 in Excel) goto Tools-References and check the box that something like Microsoft Access Oject Library. Then hopefully everything will work.

 

[msand]

Re: How to automate the data impor process from Excel to Acc

Turn on Reference box for Access Library. Another message coming back "Microsft Access can not open the database because it is missing or opened exclusively by another user". I didnt' use it so it must be the first type error. My question is: Do i need to specify the path for the name of the database and the name of the excel file? Also, i am using excel 2002 not 97, shall i just change to 02 or 2002?Thanks,

 

[LTunnicliffe]

Re: How to automate the data impor process from Excel to Acc

You need to change "YOURAccessFileName.mdb" to the path and file name of the database you want to transfer you stuff into. You also need to change "YOURExcelFileName.xls" to the path and file name of the Excel spreadsheet you are working with.

I'm not sure about the transfer type because I don't have the 2002 products. So what you are going to have to do is after the acImport type a "," and see what comes up in the little window if there is a Excel2002 type select that but if not see if Excel97 works.

LT