Reading data from a non-opened sheet

[Fredrik Glockner]

I have an application where I need to read data into a Visual Basic
program. The data is to be read from sheets with the same names, but
they reside in different directories according to parameters.


It would be best for me if I could read this data into Visual Basic
without opening the sheet where the data resides. This is because the
user might very well have a sheet open already with the same name.


I know that one can read data from a non-opened sheet with this kind of
method:

Sub ExcelMagic()
' Read from an Excel sheet without opening it first
sPath = "C:"
sBook = "Book1.xls"
sSheet = "Sheet1"
sCmd = "SUM('" & sPath & "\[" & sBook & "]" & sSheet & "'!R1C1:R10C10)"
nAns = Application.ExecuteExcel4Macro(sCmd)
MsgBox nAns
End Sub


The problem with this method is that it is only capable of reading one
single value off the sheet at a time. I would like to read a whole
matrix of values. Somewhat naïvely, I have tried to alter the code to
something like this:

sCmd = "'" & sPath & "\[" & sBook & "]" & sSheet & "'!R1C1:R10C10"

But it does not work.


Do anybody have an idea for how I could solve this problem?


Fredrik

 

[Ron de Bruin]

Hi Fredrik

This is working in Excel VBA

Maybe this will give you some ideas

This will get a1:c10 from the workbook 'c:/[testing.xls]Sheet1'
and copy it in the activesheet

Sub Getvalues()
ActiveWorkbook.Names.Add Name:="testing", _
RefersToR1C1:="='c:/[testing.xls]Sheet1'!R1C1:R10C3"
Application.ScreenUpdating = False
Range("a1:c10").Formula = "=testing"
Range("a1:c10").Value = Range("a1:c10").Value
ActiveWorkbook.Names("testing").Delete
Application.ScreenUpdating = True
End Sub

 

[=?iso-8859-1?q?Fredrik_Gl=F6ckner?=]

This will get a1:c10 from the workbook 'c:/[testing.xls]Sheet1'
and copy it in the activesheet

Sub Getvalues()
ActiveWorkbook.Names.Add Name:="testing", _
RefersToR1C1:="='c:/[testing.xls]Sheet1'!R1C1:R10C3"
Application.ScreenUpdating = False
Range("a1:c10").Formula = "=testing"
Range("a1:c10").Value = Range("a1:c10").Value
ActiveWorkbook.Names("testing").Delete
Application.ScreenUpdating = True
End Sub

Great! This example helps me.


Just one more question: I am primarily interested in fetching the
values into a two dimensional array. With this in mind, could the
procedure be made more efficient/quicker?


Thanks!


Fredrik

 

[Ron de Bruin]

Hi Fredrik

Check out this site fro ADO examples

--
Regards Ron de Bruin
http://www.rondebruin.nl

This will get a1:c10 from the workbook 'c:/[testing.xls]Sheet1'
and copy it in the activesheet

Sub Getvalues()
ActiveWorkbook.Names.Add Name:="testing", _
RefersToR1C1:="='c:/[testing.xls]Sheet1'!R1C1:R10C3"
Application.ScreenUpdating = False
Range("a1:c10").Formula = "=testing"
Range("a1:c10").Value = Range("a1:c10").Value
ActiveWorkbook.Names("testing").Delete
Application.ScreenUpdating = True
End Sub

Great! This example helps me.


Just one more question: I am primarily interested in fetching the
values into a two dimensional array. With this in mind, could the
procedure be made more efficient/quicker?


Thanks!


Fredrik

 

[=?iso-8859-1?q?Fredrik_Gl=F6ckner?=]

Hi Fredrik

Check out this site fro ADO examples

Thank you for your response. I'm a bit unsure about which site you
are refering to, though.

I have tried to look at your Excel-examples at
http://www.rondebruin.nl, but I could not seem to find something which
is relevant for my problem there.

Thanks again.

Fredrik

 

[Ron de Bruin]

Oops

I forgot to copy the link

Sorry
http://www.erlandsendata.no/english/index.php?t=envbadac

 

[=?iso-8859-1?q?Fredrik_Gl=F6ckner?=]

Check out this site fro ADO examples

Thank you for your response. I'm unsure which site you are refering to,
though.

I couldn't find anything relevant at your site:

http://www.rondebruin.nl/tips.htm


Thanks again!


Fredrik