Something Other than Transfer Spreadsheet! Desperate! 1

[SteveCarrier]

Hello,

I am trying to transfer the contents of certain cells in a spreadsheet to fields in a database. The TransferSpreadsheet method works okay if your spreadsheet has large ranges of relevant data, but I am going to have to sift through the spreadsheet with some code to locate certain records and transfer them to the relevant tables one at a time. If anyone know of a command or series of commands in VBA which will allow you to transfer cells one at a time to specific fields within a database table I would be forever in your debt.

Thanks in advance.

Steve Carrier

 

[griswom]

Steve,

Link the spreadsheet into your database. This will allow you to treat it like any other table. You can then search for the values that you need and transfer them to your Access tables.

HTH,

Griz

 

[buddyel]

I am trying to do the same thing. How do you go through linking a spreadsheet to a database.

 

[SteveCarrier]

I used the import feature in Access. Is that what you mean? The data in my speadsheet is fairly ugly so the table it creates is just warehousing unsorted data. Can I use the DLookup function to find certain records in the table? I need to find every field that has order code in it and transfer it to another table. Any ideas?

Thanks!

 

[griswom]

Hi SteveCarrier and buddyel,

Ok to link an Excel spreadsheet, go to the tables tab in your database, right click in a blank area, and select link tables. At the bottom under "Files of type" change to Excel Spreadsheet. Then locate your spreadsheet, highlight it, and select link. At this time it will take you to a wizard to describe the data you are importing. If your spreadsheet has a column w/ unique values in it, you should be able to set that as the Primary Key for the table. This has an advantage over importing the spreadsheet, because you will get live data updates over the link, whereas w/ importing you just get a static snapshot of the spreadsheet. You should now be able to write a query using your spreadsheet as the underlying table. You could bring back all records with an order code by specifying "not null" in the criteria for that field. You could then run a "make table" query w/ those records. Does that accomplish what you are looking for? HTH.

Griz