HELP I need to import a spreadsheet into Access

[Hawksey]

Hi,

Hope somebody can help me out on this one?!

I've written a bit of VB code to automatically import a csv file into a table when a user opens up the database.

What I now need to do is import a spreadsheet (.xls) into a table...but specify which cells of data I need. This spreadsheet is a pain in the nuts because when I download it from the server has the Name, Date and other gumph at the top which I don't want in my database written in pretty colours!

So say I only want the data from A5 across to M5 downwards!

Any ideas please please please?!?!

 

[KeithG]

You could create a VBA procedure to open the xl sheet and write the cell values to a field in a table. To open the xl sheet in VBA use

dim xlApp as Excel.Application
dim xlWrkBk as Excel.Workbook
dim xlSht as Excel.WorkSheet


set xlApp=createobject("Excel.Application")
set xlWrdBk= getobject(PathToFile)
set xlSht=xlWrkBk.worksheets(1)

 

[Bodisathva]

or:

DoCmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

 

[Hawksey]

No no no guys! I need to be able to do the following, all that does above is opens up the spreadsheet as an object:

What I now need to do is import a spreadsheet (.xls) into a table...but specify which cells of data I need. This spreadsheet is a pain in the nuts because when I download it from the server has the Name, Date and other gumph at the top which I don't want in my database written in pretty colours!

So say I only want the data from A5 across to M5 downwards!

Any help would be greatly appreciated!

 

[allan57]

DoCmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

range A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet.
When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

 

[KeithG]

Once you open the spreadsheet you can write the data to a table

 

[mschwent]

If you want to see what value of range you have:

open the spreadsheet in excel
select record macro
select all the cells you want to put into your database at the same time
stop macro
edit macro in visual basic and see the code