Import Excel Data to Access (1 Viewer)

[georg7]

Good morning guys,
I want to Import datasets from Excel into my Access database. The Image Shows how my Excel Sheet Looks like. Every month I want to Import the amounts per car-type in my Access table. The Excel sheet never changes. Is it possible to write a macro or something like that to Import B3:B6 ?
My Goal is that I've all the amounts listed per month in my Access db.

Have a nice one

 

[CJ_London]

you can use the transferspreadsheet method - see this link https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet

 

[vba_php]

in addition to CJ, note that importing the xl range will append the data to an access table if it already exists, and create a new table if it does not exist. the article doesn't seem to mention that. it does say too, that you *can* use ADO, but that's way more complicated than this. CJ's link is certainly simple. also look out for the acSpreadSheetType argument in that function. It's pretty convoluted in terms of what is compatible with what (e.g. - what argument item relates to which xl file extension and format type). And i don't think I've ever seen a link from microsoft that explains what to do about it. But it might exist somewhere....

 

[CJ_London]

look out for the acSpreadSheetType argument in that function. It's pretty convoluted in terms of what is compatible with what (e.g. - what argument item relates to which xl file extension and format type). And i don't think I've ever seen a link from microsoft that explains what to do about it. But it might exist somewhere....

just click on the acSpreadsheetType link and it tells you exactly what type to use for each format.

 

[gemma-the-husky]

Note that Access and Excel decide on the column type from the contents of the first few rows. If you want a different column type it can be tricky. One reason why I prefer to import csv's.

 

[CJ_London]

@vba_php the link you provided is to do with csv files imported into excel, not excel files imported into access. If you have evidence of the problem, please provide it otherwise all you are doing is confusing the OP.

 

[vba_php]

sorry. I was pointing out the similarities between the 2. for further reference. I'm sure georg can tell the difference pretty quick. shall I delete the links just in case?

 

[CJ_London]

entirely up to you

 

[vba_php]

done. we don't want to issue confusion around here.

 

[georg7]

Appreciate your help. The transferspreadsheet method worked pretty good.
Have a nice Weekend guys (Y)

 

[CJ_London]

there is another way using sql which you might want to investigate - main benefit is you can transform values if required (e.g. lookup a customer or product ID based on a name, ensure values are within a certain range, only import new records etc) as a single step. I often use it to inspect the data before importing and report on any errors found such as text in a numeric column, number of records to be imported, etc

The basic sql would look something like this which can then convert to an append query

SELECT *
FROM (SELECT * FROM [sheet1$A:D] AS xlData IN 'C:\path to file\myExcelFileName.xlsx'[Excel 12.0;HDR=yes;IMEX=1;ACCDB=Yes]) AS XL

Best to alias the query as here to avoid some issues you can get with data.

Note the range - this query will only return rows for columns A-D where there are values. If you didn't include the range, then the entire sheet would be imported.

you can also use a similar basis for importing text files - something like this

SELECT *
FROM (SELECT * FROM [TEXT;DATABASE=C:\path to file;HDR=No].myTextFileName.csv) TF

 

[vba_php]

SELECT *
FROM (SELECT * FROM [sheet1$A:D] AS xlData IN 'C:\path to file\myExcelFileName.xlsx'[Excel 12.0;HDR=yes;IMEX=1;ACCDB=Yes]) AS XL

you can also use a similar basis for importing text files - something like this

SELECT *
FROM (SELECT * FROM [TEXT;DATABASE=C:\path to file;HDR=No].myTextFileName.csv) TF

oh my! weren't you the one to tell *me* not to confuse the question asker, CJ? and now you're providing ODBC-type looking statements? hmmm....let us no go overboard here. I'm guessing he's already got the answer in a more simplistic way....I hope (per his ''thank you'' to us)!