Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
values from workbook without opening it
hello
I would like to use the values of a range (same range in same worksheet name), but from different workbooks (could be up to 40). I am currently doing it but my code involves opening each workbook which is cumbersome. Once the workbook are opened the following code is applied ' Test to see if the file is open. if not opened then message If Not IsFileOpen(Range("WorkbookNameCell1").Offset(i, 0).Value) Then MsgBox "" & Range("WorkbookNameCell1").Offset(i, 0).Value & " is not opened or the workbook name is misspell." & vbCr & vbCr & _ "Open it or check the spelling on your list (do not forget the extension .xls)" Range("WorkbookNameCell1").Offset(i, 0).Select Exit Sub End If 'take appropriate values Dim i As Long For i = 0 To 29 Dim Expt(1 To 8) As Range Set Expt(1) = Workbooks(Range("WorkbookNameCell1").Offset(i, 0).Value).Worksheets("Forecast").Range("H8:I39") etc In order not to open the workbooks, and because I do not know in advance which path the user will be using, I would like to write the path in a cell MyPath = Range("PathName").Value and add the path in the code, so I do not have to open each workbook. Any help greatly appreciated. Thanks -- caroline |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
values from workbook without opening it
You can have your code do exactly what a human would do:
1. select a cell 2. insert the referencing formula\ 3. capture the value something like: ='C:\[data.xls]Sheet1'!$B$9 Your code would splice together the formula string and then Range("Z100").Formula = the_string do an Application.Calculate and then get the value. -- Gary''s Student - gsnu200903 "caroline" wrote: hello I would like to use the values of a range (same range in same worksheet name), but from different workbooks (could be up to 40). I am currently doing it but my code involves opening each workbook which is cumbersome. Once the workbook are opened the following code is applied ' Test to see if the file is open. if not opened then message If Not IsFileOpen(Range("WorkbookNameCell1").Offset(i, 0).Value) Then MsgBox "" & Range("WorkbookNameCell1").Offset(i, 0).Value & " is not opened or the workbook name is misspell." & vbCr & vbCr & _ "Open it or check the spelling on your list (do not forget the extension .xls)" Range("WorkbookNameCell1").Offset(i, 0).Select Exit Sub End If 'take appropriate values Dim i As Long For i = 0 To 29 Dim Expt(1 To 8) As Range Set Expt(1) = Workbooks(Range("WorkbookNameCell1").Offset(i, 0).Value).Worksheets("Forecast").Range("H8:I39") etc In order not to open the workbooks, and because I do not know in advance which path the user will be using, I would like to write the path in a cell MyPath = Range("PathName").Value and add the path in the code, so I do not have to open each workbook. Any help greatly appreciated. Thanks -- caroline |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
values from workbook without opening it
you can get data from a closed workbook using formula.
try following as an idea: Sub GetData() Dim mydata As String 'data location & range to copy mydata = "='C:\[NewBook.xls]Sheet1'!$B$2:F12" '<< change as required 'link to worksheet With Worksheets(1).Range("B2:F12") '<< change as required .Formula = mydata 'convert formula to text .Copy .PasteSpecial Paste:=xlPasteValues End With Application.CutCopyMode = False End Sub -- jb "caroline" wrote: hello I would like to use the values of a range (same range in same worksheet name), but from different workbooks (could be up to 40). I am currently doing it but my code involves opening each workbook which is cumbersome. Once the workbook are opened the following code is applied ' Test to see if the file is open. if not opened then message If Not IsFileOpen(Range("WorkbookNameCell1").Offset(i, 0).Value) Then MsgBox "" & Range("WorkbookNameCell1").Offset(i, 0).Value & " is not opened or the workbook name is misspell." & vbCr & vbCr & _ "Open it or check the spelling on your list (do not forget the extension .xls)" Range("WorkbookNameCell1").Offset(i, 0).Select Exit Sub End If 'take appropriate values Dim i As Long For i = 0 To 29 Dim Expt(1 To 8) As Range Set Expt(1) = Workbooks(Range("WorkbookNameCell1").Offset(i, 0).Value).Worksheets("Forecast").Range("H8:I39") etc In order not to open the workbooks, and because I do not know in advance which path the user will be using, I would like to write the path in a cell MyPath = Range("PathName").Value and add the path in the code, so I do not have to open each workbook. Any help greatly appreciated. Thanks -- caroline |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
values from workbook without opening it
sorry to be thick but I do not understand what I ahve to do
I wrote the path including the file, sheet and range name in A1 =C:\Documents and Settings\Administrator.CHANGEME\My Documents\file4\[template.xls]sheet1!H8:I39 then If A1 is named PathName I wrote in VBA Dim MyPath As String MyPath = Range("PathName").Value Range("H8:I39).value=Mypath.value And it did not work. I thank you in advance for your help -- caroline "Gary''s Student" wrote: You can have your code do exactly what a human would do: 1. select a cell 2. insert the referencing formula\ 3. capture the value something like: ='C:\[data.xls]Sheet1'!$B$9 Your code would splice together the formula string and then Range("Z100").Formula = the_string do an Application.Calculate and then get the value. -- Gary''s Student - gsnu200903 "caroline" wrote: hello I would like to use the values of a range (same range in same worksheet name), but from different workbooks (could be up to 40). I am currently doing it but my code involves opening each workbook which is cumbersome. Once the workbook are opened the following code is applied ' Test to see if the file is open. if not opened then message If Not IsFileOpen(Range("WorkbookNameCell1").Offset(i, 0).Value) Then MsgBox "" & Range("WorkbookNameCell1").Offset(i, 0).Value & " is not opened or the workbook name is misspell." & vbCr & vbCr & _ "Open it or check the spelling on your list (do not forget the extension .xls)" Range("WorkbookNameCell1").Offset(i, 0).Select Exit Sub End If 'take appropriate values Dim i As Long For i = 0 To 29 Dim Expt(1 To 8) As Range Set Expt(1) = Workbooks(Range("WorkbookNameCell1").Offset(i, 0).Value).Worksheets("Forecast").Range("H8:I39") etc In order not to open the workbooks, and because I do not know in advance which path the user will be using, I would like to write the path in a cell MyPath = Range("PathName").Value and add the path in the code, so I do not have to open each workbook. Any help greatly appreciated. Thanks -- caroline |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
values from workbook without opening it
Sorry I am still struggling :
I do not want to write the path, name of workbook, C:\[NewBook.xls]Sheet1'!$B$2:F12 in the code (as I want the user to enter this in a cell and this use it in the code. thanks -- caroline "john" wrote: you can get data from a closed workbook using formula. try following as an idea: Sub GetData() Dim mydata As String 'data location & range to copy mydata = "='C:\[NewBook.xls]Sheet1'!$B$2:F12" '<< change as required 'link to worksheet With Worksheets(1).Range("B2:F12") '<< change as required .Formula = mydata 'convert formula to text .Copy .PasteSpecial Paste:=xlPasteValues End With Application.CutCopyMode = False End Sub -- jb "caroline" wrote: hello I would like to use the values of a range (same range in same worksheet name), but from different workbooks (could be up to 40). I am currently doing it but my code involves opening each workbook which is cumbersome. Once the workbook are opened the following code is applied ' Test to see if the file is open. if not opened then message If Not IsFileOpen(Range("WorkbookNameCell1").Offset(i, 0).Value) Then MsgBox "" & Range("WorkbookNameCell1").Offset(i, 0).Value & " is not opened or the workbook name is misspell." & vbCr & vbCr & _ "Open it or check the spelling on your list (do not forget the extension .xls)" Range("WorkbookNameCell1").Offset(i, 0).Select Exit Sub End If 'take appropriate values Dim i As Long For i = 0 To 29 Dim Expt(1 To 8) As Range Set Expt(1) = Workbooks(Range("WorkbookNameCell1").Offset(i, 0).Value).Worksheets("Forecast").Range("H8:I39") etc In order not to open the workbooks, and because I do not know in advance which path the user will be using, I would like to write the path in a cell MyPath = Range("PathName").Value and add the path in the code, so I do not have to open each workbook. Any help greatly appreciated. Thanks -- caroline |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
values from workbook without opening it
Got it!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I needed to write in the cell '='C:\[NewBook.xls]Sheet1'!$B$2:F12 then it all makes sense Thanks to John and gsnu200903 -- caroline "caroline" wrote: Sorry I am still struggling : I do not want to write the path, name of workbook, C:\[NewBook.xls]Sheet1'!$B$2:F12 in the code (as I want the user to enter this in a cell and this use it in the code. thanks -- caroline "john" wrote: you can get data from a closed workbook using formula. try following as an idea: Sub GetData() Dim mydata As String 'data location & range to copy mydata = "='C:\[NewBook.xls]Sheet1'!$B$2:F12" '<< change as required 'link to worksheet With Worksheets(1).Range("B2:F12") '<< change as required .Formula = mydata 'convert formula to text .Copy .PasteSpecial Paste:=xlPasteValues End With Application.CutCopyMode = False End Sub -- jb "caroline" wrote: hello I would like to use the values of a range (same range in same worksheet name), but from different workbooks (could be up to 40). I am currently doing it but my code involves opening each workbook which is cumbersome. Once the workbook are opened the following code is applied ' Test to see if the file is open. if not opened then message If Not IsFileOpen(Range("WorkbookNameCell1").Offset(i, 0).Value) Then MsgBox "" & Range("WorkbookNameCell1").Offset(i, 0).Value & " is not opened or the workbook name is misspell." & vbCr & vbCr & _ "Open it or check the spelling on your list (do not forget the extension .xls)" Range("WorkbookNameCell1").Offset(i, 0).Select Exit Sub End If 'take appropriate values Dim i As Long For i = 0 To 29 Dim Expt(1 To 8) As Range Set Expt(1) = Workbooks(Range("WorkbookNameCell1").Offset(i, 0).Value).Worksheets("Forecast").Range("H8:I39") etc In order not to open the workbooks, and because I do not know in advance which path the user will be using, I would like to write the path in a cell MyPath = Range("PathName").Value and add the path in the code, so I do not have to open each workbook. Any help greatly appreciated. Thanks -- caroline |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I stop blank workbook from opening when opening an existing | Excel Discussion (Misc queries) | |||
excel VBA problem - setting workbook as variable & opening/re-opening | Excel Programming | |||
How to make the opening of a workbook conditional upon the opening of another workbook | Excel Programming | |||
How to make opening of workbook conditional of opening of another workbook | Excel Programming | |||
Question for Experts: Opening workbook with workbook references | Excel Programming |