Import from csv file or Excel 1

[bebbo]

I know you can import into Access from a excel sheet. However how can I automate this procedure. I need to have a button on a form which imports without having to go into Access.

I.E Microsoft Access db Table = Staff
Excell Sheet = Staff

I want the excel staff info automatically go into Access.

Thanks

 

[dyarwood]

If you are importing into Access you could use coding and

DoCmd.TransferSpreadsheet

then on your form you could set a button to run this code. Is that the type of thing you were thinking?

dyarwood

 

[bebbo]

Yep, but how would I do that?

 

[dyarwood]

From access in a VBA code

Function Import_my_spreadsheet()

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

DoCmd.TransferSpreadsheet acImport, _ acSpreadsheetTypeExcel5, _
"Employees","C:\Spreadsheet.xls", True, "A1:G12"

End Function


This imports an excel 5 file called Spreadsheet from the C drive using the first row as field heading and using the range A1 to G12. The data is imported into a table called Employees

This code must then be run using a macro and then that macro can be assigned to a button. Do you need any more or is that ok?

dyarwood

 

[bebbo]

Silly question, does that mean I require Visual Basic or is VBA part of Access. To be hoenst I have little experience of Access. I know forms, query etc but haven't use VB with.

Perhaps you could point me to an example????

 

[dyarwood]

VBA is part of Access. It can be found under the modules option of the main database window. When there click new and then copy and paste the code I gave you above. What version of excel are you using?

I will have a think about another way round it. Have you recorded any macros within excel before?

dyarwood

 

[bebbo]

I haven't done macros with excel, however I'm sure i could. My experience is mainly Visual Foxpro. However the past six months I've been looking at VB and Access.

I'm using excel 2000. Thanks for your help!!!!!!!!

 

[dyarwood]

Let me know if the coding works. I would suggest if you want to learn VBA start off in Excel. Using the macro recorder in Excel to produce some macros. Then look at them by using the edit tool. In Excel you can record macros with keyboard and mouse clicks within spreadsheets. This will give you an idea of what VBA does and how it builds up. There are things like if, while, do loops which are very useful but not necessary to do if your just starting it. Access unfortunately does not have a recorder like this, you have to write it yourself. There are slight differences between Excel VBA and Access VBA but knowing the background of VBA will help in Access.

Also thought I would recommend a book which I use. Excel 2002 VBA Programmer's Reference, by Wrox. There is an Access book by the same people. I found this book very useful when creating my stuff.

If you need any other help just get back to me.

dyarwood

 

[bebbo]

Hi,

Thanks for you help. I've created the module and it works !!!!!!! however how do I get this on the form. Also can I check for duplicate entries. I.e if the employee has an unique number can I stop these from being imported??

Brilliant, THANKS

 

[dyarwood]

You want a button on your form to do this so you need to create a macro within Access to run the module code. (Don't worry it gets easier the more you do it) Under the macros tab click new. Then under Action click the drop down arrow and select RunCode.

Then enter the function name Import_my_spreadsheet() into the function name section.

Have you created buttons on a form before and assigned them to macros?

I'm not sure in the importing if you can check for duplicates. I usually do this with code but that may be even more complicated. Is the unique number in the left column of your table? Also is it ordered so that the repeated enteries will be one after the other?

dyarwood

 

[bebbo]

Thanks,

I'm at work at the moment and best get on with some other things. However thanks for your help and I will continue with this later when I'm back at home. I'm sure I'll have further questions then.

Once again thanks, I'm sure I'll be able to manage this.

 

[bebbo]

Hi

I can't appear to get the macro inserted.
I can't appear to see "Action click the drop down arrow and RunCode".

 

[bebbo]

It says record a new macro is this the correct area??

 

[dyarwood]

This is within Access. On the main database screen there is a macros tab and this will open up a macro box. The RunCode will be under the drop down list under the actions section

dyarwood

 

[janetlyn]

Dyarwood, in your code below, what is the significance of the 5 behind Excel. Is that the version? Do I put this in the Control Source for the field I want populated?

Function Import_my_spreadsheet()

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

DoCmd.TransferSpreadsheet acImport, _ acSpreadsheetTypeExcel5, _
"Employees","C:\Spreadsheet.xls", True, "A1:G12"

End Function

If I put this in the control source, would the Excel spreadsheet need to be opened in order for it to pick up the value?

Thanks for any help you can give. Janet Lyn

 

[dyarwood]

The 5 is the Excel version. Think that version was around 1995. There are other versions you can select though.