Read data from closed file (Excel)

[andyfas]

Hi,
I hope someone could help me with my problem.
I have an excel file "from_mydata.xls" closed. It has only one sheet ("data1"). I need to read the cell "a1" value and put it in a variable in order to confront this value with another, read from a second file "to_mydata.xls" already open.
I would like to read this value without open "from_mydata" file. Is it possible ?

thanks.

[RobDog888]

Welcome to the Forums.

You can use the Excel Object Model to open the file but hidden or you can use ADO to run a SELECT query against the file. Writting to the file while the user has it open can not be done. If a user has a file open the you can open it as read only and it wont conflict using eithe method.

option Explicit
'Add a reference to ms excel xx.0 object library
Private sub command1_click()
Dim oApp as Excel.Application
Dim oWBFrom As Excel.workbook
Dim oWBTo As Excel.Workbook
Dim strFromA1 As String
Dim strToA1 As String
Set oApp = New Excel.Application
oApp.Visible = False
Set oWBFrom = oApp.workbooks.open(FileName:="C:\from_mydata.xls", ReadOnly:=True)
Set oWBTo = oApp.Workbooks.open(FileName:="C:\to_mydata.xls", ReadOnly:=True)
strFromA1 = oWBFrom.Sheets("Sheet1").Cells(1, 1).Value
strToA1 = oWBTo.sheets("Sheet1").Cells(1, 1).Value
MsgBox strFrom & ", " & strTo, vbOkOnly
oWBFrom.Close SaveChanges:=False
oWBTo.Close SaveChanges:=False
Set oWBFrom = Nothing
Set oWBTo = Nothing
oApp.Quit
Set oApp = nothing
End sub

[andyfas]

thank you very much to welcome me and for your helpful advice :thumb:

[Boyko]

Two Excel 2007 files contain identical sheet structure (but different data) and identical macro. Each macro should be able to read (in the background if possible) whole sheet from the other file. Reading command is controlled with a button. So far I have decided to read/copy one sheet into a new sheet in the active file and then work with the data locally. I have come to two approaches:

1. I can open the file but this runs its macro and changes the focus. Can the file be opened with its macro disabled, while the macro that opens it remains active? Will "Application.Visible = False" apply to the workbook that is running the macro?

2. The "ExecuteExcel4Macro" function works fine but rather slow, as the sheets are about 1000 rows by 40 columns.

[westconn1]

Will "Application.Visible = False" apply to the workbook that is running the macro?
yes, unless you open the other workbook in a separate instance of excel, then you can have one visible and the other not

[Boyko]

I tried this

Application.DisplayAlerts = False
Application.Visible = True
Set wks_to = Worksheets.Add
Set app_from = New Excel.Application
app_from.DisplayAlerts = False
app_from.Visible = False
Set file_from = Workbooks.Open(file_from_name, 3, True)
file_from.Worksheets("Sheet1").Activate
file_from.Worksheets("Sheet1").UsedRange.Copy
wks_to.Activate
wks_to.Paste
file_from.Close SaveChanges:=False

but it looks like lines 5 and 6 have no effect, and both workbooks are affected by lines 1 and 2. If I remove line 1, I receive questions about updating links (line 7), about emptying the clipboard (line 12), plus both workbooks are visible. It works but it's not elegant so I'm still digging.

[westconn1]

while you have created a separate instance of excel, you have opened both workbooks in the same instance

line 7 should specify app_from.workbooks.open

app_from by default would not be visible anyway, but make sure not to allow alerts, as they also would not be visible

[Boyko]

That was helpful, thank you. But the dialog about the Clipboard continued to appear and I stopped it with

app_from.CutCopyMode = False

so I consider it resolved.