Manipulating an external .xls via VBA from a driving .xls.

[VisualGuy]

I'm trying to read an excel file from another excel file using VBA. I'm having a lot of trouble interacting between two sheets that exist on the external .xls. Here's what I'm basically trying to do. The external .xls has two worksheets. On worksheet 'sheet2', I have two columns:

ID - Numeric
Total Amount

On worksheet 'sheet1', I also have two columns:

ID - Numeric
Total Amount (If Blank, it states unknown).

I need to launch a VBA script from the first excel file that will read this entirely seperate excel file, starting with the ID from the 'sheet2'. I need to grab the values, one at a time, then search for that ID value on sheet1. If I find it and the total amount on 'sheet1' says unknown, then I need to replace the unknown with the total amount from sheet2 that corresponds to that ID.

Make sense? Sounds simple to me, yet I'm struggling like you wouldn't believe. Can anyone give me some advice?

 

[SoCalAccessPro]

You're gonna have to learn the excel objects, properties and methods, as the code required to do this would require a little time.

You'll be using the workbook, worksheet, range and cell objects.

For the lookup, look into Hlookup and Vlookup.Or, you could just import the spreadsheets into Access and use an update query.....

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com