Importing table data from Excel

[Ramu]

Hi

Can any one help to find a way to import/link excel sheet with access? I am OK if the excel sheet field is " 12345" but if the filed is S12345 or 123/s/345- I am getting a error in the linked table how to solve this probelm (even I have changed the format of the excel sheet's particular filed as Text - still not working)- Please

And is there is any way we can create a command button to import excel table by clicking- please note I am Zero in VB- any one please help me with simple access commands

Thanks
Ramu

 

[Smee]

I also would be interested in a solution to this one

In the near future I will also need to import information from a spreadsheet into Access.

It will be timesheet information that is input by the corresponding member of staff, and it needs to be collated in the database so that various forms are updated.

I have more comfort with VBA than Ramu.

Any and all advice welcomed.

Thanks

 

[neileg]

The kind of error you are having is common with imports to Access or Excel where you rely on the wizard to interpret the data type. As you have found, if the data type is wrong, you get errors.

Changing the format in Excel doesn't help since this only affects the display. Excel is quite happy to have text and numbers in the same column, Access isn't. You would have to change your numeric values to text in Excel by prefixing them with an apostrophe '

If you create a table in Access with the datatype set correctly, you will be able to import OK. Access will import an Excel number into a text field without giving an error.

You can't run an import from a button by using the button wizard or a macro. I'm sure you can do it in code, but I don't know how.

 

[Smee]

Thanks for your advice.

When I get to that part of my task your advice will be useful. Plus, by then my coding abilities should be up to automating the task.

Hopefully your advice will help Ramu as well.

Cheers

 

[The_Doc_Man]

Even for folks who are zero in VB, there is a quick and dirty way to do this.

First, the suggestion to create the table before you do the import is spot-on. The problem you were having is that the Excel Import Wizard only reads the first few rows of the sheet in order to guess the proper format. So if the first few rows are only 12345, it says, "Aha, numbers! I can do that..." Then, a few rows later, S12345 shows up and the wizard goes BARF! So the solution is, don't trust the wizard to get it right the first time. Or, if you like Murphy's laws, don't assume the wizard will get it right. Take steps to assure that it cannot get it wrong. Pre-define the table structure.

By the way, there is another reason to do a pre-define rather than to trust the wizard. You see, the default size of a text field imported this way is 255 characters. So if you have text but it is a lot smaller than 255 characters, pre-defining the table potentially saves you some space when you later compact your database.

Second, build a form. Put a button on it using the button wizard. Perhaps tell it to open a report when you use that button. Doesn't matter what you tell it, really, but opening a report is a simple case. Now look at the code behind that button, because the way Access implements button-driven operations is to automagically generate some code for you.

Read the help files on the TransferSpreadSheet method or action. Try to figure out the parameters you want for it, which will include giving it a file name and a table name. Also, it will require you to specify whether you are doing an import or an export. If I recall correctly, most of the other parameters could be defaulted, but it is a good idea to look at them anyway.

Now edit the button's code to remove the line of code that opens the report and replace it with the line of code that does a transfer of spreadsheet data.

You can edit the text shown on the form's action button to make it say anything you want within sizing limits. So customize it to suit yourself.