i need tips on how to retrieve some excel cells values from one sheet and put them in another sheet using vb?

Question

thx for your help.

Answer 1

you can use ADO to connect to Excel files like "Access" files (mdb files)

but you should use this format for ConnectionString in ADO Connection:

ConnectionString =
"Provider = Microsoft.Jet.OLEDB.4.0;Data
Source=MyFile.xls;Extended Properties=Excel 8.0;"

Answer 2

When Excel File Name=>(Book1) Sheet Name=>(Sheet1) Cell Name=> A1 contains the value you want to retrieve in say Book2 at any sheet, at any cell say A1, Pls enter the formula in the target cell as follows
=[Book1.xls]Sheet1!$A$1
Instead of typing out the whole thing u could keep both files open,
put an = symbol in the target cell, select the other file from the task bar and click on the source cell. Excel will do the work of adding the address of the source cell.
Hope this solves ur query.

Answer 3

easiest way to do complex stuff in excel with VB is to get excel to write the code for you then hack on it as necessary. Create a spreadsheet that's a representative example of what you'll be wanting to manipulate, then sit down and write out step by step exactly what you're wanting to do. Go to tools > macros and turn on macro recording, walk through the steps you want your code to perform in excel, then turn off macro recording. Pop open the macro and there's the VBA code ready for whatever. Good luck and I hope this helps.

Answer 4

copy the information in the cell "ctrl c" then when you paste it into the next sheet, paste special, that feature is under the edit menu, it will paste the value and not the formula