Get data without opening Excel?

[arrun]

Dear all, is there any way to copy the elements of a sheet without opening the Excel file?

Let say I have a Excel file saved in "C:/". And that Excel file has 2 Sheets. Now I want to copy the elements of the first sheet without opening it.


Generally I first open the Excel file using VBA and then copy the elements. However if Excel file is of large size, it takes lot of time to opening it. Therefore I thought it may be a better approach to get the data without opening it. Just like we can get data from DB by establishing a Connection.

Thanks for your help.

[Kenneth Hobs]

There are three ways to get values from a closed Excel. If you mean copy, then you will have to open the file.

One method is a sheet reference using R1C1. The second method is to use ADO to query. Here is the third method.

[VBA]
Sub t()
MsgBox GetValue("x:\test", "test.xlsx", "Sheet1", "A1")
End Sub

'=GetValue("c:\files", "budget.xls", "Sheet1", "A1")
Private Function GetValue(path, file, sheet, ref)
' path = "d:\files"
' file = "budget.xls"
' sheet = "Sheet1"
' ref = "A1:R30"

Dim arg As String

If Right(path, 1) <> "\" Then path = path & "\"

If Dir(path & file) = "" Then
GetValue = "file not found"
Exit Function
End If

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("a1").Address(, , xlR1C1)

GetValue = ExecuteExcel4Macro(arg)
End Function
[/VBA]

[arrun]

Thanks Kenneth for your reply. However I could not find any way to use the collected data. Here, I made a small modification of your code:

[VBA]Sub t()
Dim MyValue(5, 5) As Variant
For i = 1 To 5
For j = i To 5
Worksheets("Book1").Cells(i, j) = GetValue("x:\test", "test.xlsx", "Sheet1", "A1")(i, j)
Next j
Next i

MsgBox GetValue("x:\test", "test.xlsx", "Sheet1", "A1")
End Sub

'=GetValue("c:\files", "budget.xls", "Sheet1", "A1")
Private Function GetValue(path, file, sheet, ref)
path = "F:\Rough"
file = "Book1.xlsx"
sheet = "Sheet1"
ref = "A1:R30"

Dim arg As String

If Right(path, 1) <> "\" Then path = path & "\"

If Dir(path & file) = "" Then
GetValue = "file not found"
Exit Function
End If

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("a1").Address(, , xlR1C1)

GetValue = ExecuteExcel4Macro(arg)
End Function[/VBA]

However I am getting following error:

Run-time error '13':
Type mismatch

and this error is happening in the line of 'Worksheets("Book1").Cells(i, j) = GetValue("x:\test", "test.xlsx", "Sheet1", "A1")(i, j)'

Is there any way to resolve this?

Thanks and regards,

[snb]

To summarise several methods.

If you want to retrieve the value of cell D9 in sheet1 in file G:\OF\adressen.xls:

[vba]
Sub M_snb()
Cells(1, 5).Formula = "='G:\OF\[adressen.xls]Sheet1'!D9"

Cells(3, 5) = ExecuteExcel4Macro("'G:\OF\[adressen.xls]Sheet1'!" & Range("D9").Address(, , xlR1C1))

' add the reference to Microsoft ActiveX Data Objects 2.0 Library

With New Recordset
.Open "SELECT * FROM `Sheet1$`;", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\OF\adressen.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Cells(5, 5) = Application.Index(.GetRows(, , 3), 1, 8)
End With

With New Recordset
.Open "SELECT * FROM `Sheet1$`;", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\OF\adressen.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
.Move 7
Cells(9, 5) = .Fields(3)
End With

End Sub[/vba]

NB.
- check/adapt the path G:\OF\
- check/adapt the filename adressen.xls
- check/adapt the sheetname Sheet1
- check/adapt the range D9

- in the recordsetmethod column A stands for fields(0), so column D stands for fields(3)
- in the recordsetmethod the first row is to be considered to contain the filenames. So row 7 corresponds with the 8th row in the Excel sheet.