Using VBA to read/write data from Excel files

[Robert Crandal]

How would I go about using Visual Basic to extract
data from cells of any given Excel file and store that
data in variables of my running VBA program??
Similary, how would I write back a value into
that same file into a specified cell??

I just need some basic examples regarding which
functions to use for file operations to get me going.

Thank you all!

 

[Robert Crandal]

I have been trying the following code in my VBA module(s):

Dim x As Integer

x = Workbooks("My File").Worksheets("Sheet1").Range("D8").Value

However, I get the following error message:

"Run time error 9 - Subscript out of range"

What am I doing wrong??

 

[Robert Crandal]

So, just to re-phrase my original question, I want to be able to read
the cell values of a closed "xls" file using Visual Basic AND without
opening the source workbook on my screen.

After searching the Internet, I discovered that I have to use an
automation object to open the workbook and retrieve the value of
cell. Here is some sample code that I found:

----------------------------------------------------
Dim oXL As Excel.Application, oBook As Excel.Workbook, oSheet As
Excel.Worksheet, vValue As Variant

Set oXL = New Excel.Application
Set oBook = oXL.Workbooks.Open("D:\Test.xls")
Set oSheet = oBook.Worksheets("Sheet1")

vValue = oSheet.Cells(1, 1).Value 'Get the value from cell A1
Debug.Print vValue

Set oSheet = Nothing
oBook.Close
Set oBook = Nothing
oXL.Quit
Set oXL = Nothing
----------------------------------------------------

This code actually works, the problem is that it is sooooo extremely slow
(in my opinion) just to read the value of Cells(1,1).

Does anybody know of any quicker ways to use VBA to read data
from closed Excel files??

Thank you everyone!

 

[Dave Peterson]

You could add formulas to empty cells to retrieve values from your sending
workbook.

=======

John Walkenbach has a routine that can get values from a closed workbook:
http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

=======

As for updating those cells, I'd open the workbooks, update the cells and close
the workbook. The biggest delay I've seen is when my receiving file is on a
network.

I'll sometimes copy those receiving files to my harddrive, run my macro and then
copy those updated files back to the network.

 

[Robert Crandal]

Can you give an example of adding "formulas to empty cells to retrieve
values from
your sending workbook"???

Additionally, I discovered that the GetValue() function mentioned below DOES
work very well for reading individual cells. However, if I want to read a
range
of cells (say "A1:L25") then my code starts to look ugly. It is also a very
slow process of reading a range of cells one cell at a time by calling
GetValue() for each individual cell. Can GetValue somehow be used to
read a range of cells in a faster manner???

As for the GetDataFromClosedFile() function, it seems to be missing function
parameters, so I have no clue how to use that one.

Hope to hear from you soon! Thank you!

 

[Dave Peterson]

Create a new worksheet in the receiving workbook.
Open your sending file.
Select the cell in the sending worksheet in the sending workbook.
Edit|Copy (or ctrl-c)

Back to A1 of that new worksheet in the receiving workbook.
Edit|paste special|paste link

Close the sending workbook.

Now back to the receiving workbook.

Start recording a macro.
Select A1 of that new sheet
Hit F2 (to edit the cell)
Hit Enter (don't make any changes)
Stop recording the macro.

Your recorded macro will have the syntax building that formula. Depending on
the names of the paths/folders/workbooks/worksheets and addresses, you'll want
to match that syntax when you build the formula using variables.

If you have trouble, share the exact recorded formula and your variable names
and what they represent.