import from excel to access

[davikokar]

Hallo, I'm trying to import an excelsheet into access through vba. I can use the runCommand acCmdImport, but I would like to set all the options programmatically (like delimited or fixed, delimiter, text qualifier, first row contains headers,...). Do you know how to do this? thanks

 

[Remou]

Hi
In my version of Access, I do not need delimiters or text qualifiers to import Excel sheets. These properties are set on the spreadsheet. Headers and the range to import can be set using Tranferspreadsheet. Text imports, which need delimiters etc, can be controlled by import / export specifications.

 

[gavjb]

Are you trying to import Excel or CSV, as you dont have options like delimiters in Excel but you do in csv, if you are importing CSV you need to use the docmd.transfertest where you can select delimiter etc...

If you are importing CSv do a manual import and create a specification first before you code the import in VBA, where you can specify the specification.

If you are importing excel you use docmd.transferspreadsheet where you can select the Spreadsheet version you are importing and you can specify where the sheet has fieldnames etc...

Hope this helps


Gavin,

 

[DrSimon]

I use Docmd.TransferSpreadsheet. As you're importing an excel spreadsheet delimited/fixed & delimiter aren't relevant. For a flat file DoCmd.TransferText is available.
Simon Rouse

 

[davikokar]

thanks for your advice, actually I am importing a csv file, not a excelsheet. With transfertext I can set all the options. I was wondering how can I set programmatically also the name of the file that should be imported. Something like a "browse files" buttons that would return the path of the file. Any idea?

 

[Remou]

Hi
This may help:
IMPORT Excel Sheet into Access allowing user to select file
thread705-760625

 

[davikokar]

thanks remou for the link. The code there is there would give me this error:
user defined type not defined. The debugger highlights this line:
Dim fDialog As Office.FileDialog

Do you know why? do I have to import libraries? thanks

 

[PHV]

You have to reference the microsoft office x.y object library.
menu tools -> References ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[powerlock2005]

I would like to do something like this as well from a .csv file.

Here is what I do. I export from a MySQL database every week and I would like to only import the newer records into my access database, not the entire .csv each time.

How can I do this?

 

[PHV]

I export from a MySQL database every week and I would like to only import the newer records
Why not simply export only the newer records ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DrSimon]

Yes, if you can do what PHV says that's best. But if not, import the entire csv into a temporary table then extract those that are new and append them to your holding table. Finally delete or empty the temp table.
If you don't know how to find the new records, try using the 'Find Unmatched Query Wizard'. Even if it doesn't do the job for you, it will show you the syntax to use.
Simon Rouse