Get data from unopened file

The code would be like that

Sub CopyRangeFromClosedWB()
    Dim WB As Workbook, AB As Workbook

    Set AB = ActiveWorkbook
    Set WB = Workbooks.Open(ThisWorkbook.Path & "\Yasser.xlsm")
    
    Application.ScreenUpdating = False
        Sheets("Sheet1").Range("A1:C10").Copy AB.Sheets("Sheet1").Range("G1")
        WB.Close
    Application.ScreenUpdating = True
End Sub

This would copy range from closed workbook to the active workbook
Hope it helps you

Hi zico8

Originally Posted by zico8

.....
Is it possible to get data without opening that file?....

Yes and no. You can get the value out of any cell in a closed Workbook. ( without opening it) It is called “performing a reference”
See code here:
http://www.excelforum.com/developmen...ml#post4213824
and here:
http://spreadsheetpage.com/index.php...a_closed_file/
Have a go, and let me know if you need more specific help
Alan

Thanks for it,

but some times ago I was told to apply GetObject:

Set WB = GetObject(ThisWorkbook.Path & "\myfile.xlsm")

or just:

Set WB = Workbook(ThisWorkbook.Path & "\myfile.xlsm")

In fact - its works, but can you tell me what is the difference to between GetObject, Workbooks.Open and just Workbook?

Hi zico8,

Originally Posted by zico8

any comments?

I have tried to experiment a little and have looked a lot in Internet for info.

I do not have a complete answer for you. But I can tell you the following
This statement
Dim WB As Workbook
Assigns a variable for holding a Workbook object.

Now to your specific questions
_1) Set WB = Workbooks("myfile.xlsm")
This Sets the WB Object to a specific File, here the opened file "myfile.xlsm". SO THE FILE "myfile.xlsm" MUST ALREADY BE OPEN FOR THIS TO WORK.

_2) Set WB = Workbooks.Open(ThisWorkbook.path & "\myfile.xlsm")
This opens the Workbook "\myfile.xlsm" and sets the WB Object to this file "\myfile.xlsm". So the File must exist but does not have to be open. If it is open this code line will not error

_3) Set WB = GetObject(ThisWorkbook.path & "\myfile.xlsm")
This appears to do the same as _2) , Except the file is not visible. I have not been able to find a way to make this visible. So to manipulate it you must use VBA code. It is good practice to add a line to release the reference to this Object.

Hope that helps a bit.

And here is a code i used to test the above:

'
Sub SetWBTestCodes() '  http://www.excelforum.com/excel-programming-vba-macros/1110924-get-data-from-unopened-file.html
Rem 1 'Creat a new workbook for this Demo Code
Workbooks.Add 'Creat a new workbook for this Demo Code
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.path & "\myfile.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False  ' Save the newly created workbook with name "\myfile.xlsm" in the same folder as that in which this code is in    'For .xlsm Files 'FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False  'For .xls Files default will usually do

Rem 2 'Explain difference between 3 similar code lines
Dim WB As Workbook 'Variables used for holding of Workbook. Define WB as a Workbook Object
1 Set WB = Workbooks("myfile.xlsm")  'Set WB object to specific file. File must be open
WB.Close 'Close the file to demonstrate that the next two lines will work with the File closed ( as long as the file exists )
2 Set WB = Workbooks.Open(ThisWorkbook.path & "\myfile.xlsm") 'Open this file and set WB object to it. Will not error if file is already open.
3 Set WB = GetObject(ThisWorkbook.path & "\myfile.xlsm") ' This appears to do the same as Code line 2 , Except the file is not visible. I have not been able to find a way to make this visible. So to manipulate it you must use VBA code. It also will not error if file is already open.
    '    Application.Visible = True' These two lines should make the File visible but they do not appear to work.
    '    WB.Parent.Windows(1).Visible = True
	
Rem 3) 'Delete Demo File
WB.Close 'Close the File again or else the following line will not work
Kill ThisWorkbook.path & "\myfile.xlsm" 'Delete File. USE WISELY!!
Set WB = Nothing ' Release reference. May not be necerssary but is good practice. ( BTW Removes it from VB Editor )
End Sub

Alan

P.s. The further code below demonstrate how to do the “Performing a reference”, that is to say it gets data from a closed File without opening that File, as per your original request.

Sub SetWBTestCodesAndPerformReferrence() '  http://www.excelforum.com/excel-programming-vba-macros/1110924-get-data-from-unopened-file.html
Rem 1 'Creat a new workbook for this Demo Code
Workbooks.Add 'Creat a new workbook for this Demo Code
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.path & "\myfile.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False  ' Save the newly created workbook with name "\myfile.xlsm" in the same folder as that in which this code is in    'For .xlsm Files 'FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False  'For .xls Files default will usually do

Rem 2 'Explain difference between 3 similar code lines
Dim WB As Workbook 'Variables used for holding of Workbook. Define WB as a Workbook Object
1 Set WB = Workbooks("myfile.xlsm")  'Set WB object to specific file. File must be open
WB.Close 'Close the file to demonstrate that the next two lines will work with the File closed ( as long as the file exists )
2 Set WB = Workbooks.Open(ThisWorkbook.path & "\myfile.xlsm") 'Open this file and set WB object to it. Will not error if file is already open.
3 Set WB = GetObject(ThisWorkbook.path & "\myfile.xlsm") ' This appears to do the same as Code line 2 , Except the file is not visible. I have not been able to find a way to make this visible. So to manipulate it you must use VBA code. It also will not error if file is already open.
    '    Application.Visible = True' These two lines should make the File visible but they do not appear to work.
    '    WB.Parent.Windows(1).Visible = True

Rem 3) ' Demonstrate getting value from cell in a closed File. "Performing a referrence"
WB.Worksheets.Item(1).Name = "Sheet1" ' Just in case the first sheet in the closed workbook is not named sheet 1 as it needs to be ****
WB.Worksheets("Sheet1").Cells(1, 1).Value = "Hallo"
WB.Close 'Close file as it was opened by code lines 2 or 3
Dim GetReferrence As String ' This variable will contain the value from a cell in the closed workbook.
Dim FullReferrencecell1 As String 'This is a string to be used to "Perform the referrence".   Only the filename and sheet name will trypically appear / be needed for open files, but if you closed the source file need the full reference
Dim strws As String: Let strws = "Sheet1" '****  For "performing a referrence" you have to know this
Let FullReferrencecell1 = "'" & ThisWorkbook.path & "\" & "[myfile.xlsm]" & strws & "'!" & Range("A1").Range("A1").Address(, , xlR1C1)
    '   Debug.Print FullReferrencecell1 '  Typical returned form   'F:\ExcelForum\wbSheetMaker\[myfile.xlsm]Sheet1'!R1C1   for fist cell in Closed Workbook
Let GetReferrence = ExecuteExcel4Macro(FullReferrencecell1) 'This should return us our test cell value. It "Performs the referrence"
MsgBox prompt:="Value obtained from closed Workbook, cell (1, 1) is """ & GetReferrence & """"

Rem 4) 'Delete Demo File
Kill ThisWorkbook.path & "\myfile.xlsm" 'Delete File. USE WISELY!!
Set WB = Nothing ' Release reference. May not be necerssary but is good practice. ( BTW Removes it from VB Editor )
End Sub

EDIT:
To get code line 3) to work as code line 2) , an extra line is required to give in total these two code lines
Set WB = GetObject(ThisWorkbook.path & "\myfile.xlsm")
WB.Windows(1).Visible = True

Thanks to snb
http://www.excelforum.com/the-water-...ml#post4227653
for that one
Alan

and again
http://www.excelforum.com/the-water-...ml#post4228123