VB to reference open workbooks not appearing in the workbooks collection

[P0lar]

Hi, Hope someone can help, I have a program which can output daily reports to excel format, and I'm trying to automatically collate a weekly view from the 7 open files. I was hoping to use:


For Each wb in Workbooks

...do the collating stuff...

Next wb


But the excel workbooks opened by the program don't appear in the workbooks collection of the workbook I have the VB code in. I suspect they are each in a seperate instance of excel as if I copy and paste formulas between them, I get just hte formual results as values. Is there any way I can get at them to process?


Thanks for the help!

 

[vijay.vizzu]

Can you please share your complete code or file for better understanding

 

[P0lar]

Hi,


Code is as follows, I've not tested if the middle bit works as it's not picking up any workbooks to test with and my VB syntax isn't that great without debugging! My workbook with the vbcode has 7 worksheets, one for each day I want to import data to A1:I100.


dim wb as workbook


For Each wb in Workbooks


if left wb.name,3) = "_tt" then

SDate = wb.sheets("Intraday").range("B2")

SDay = worksheetfunction.weekday(SDate,15)

thisworkbook.sheets(SDay).range("A1:I100")=wb.sheets("Intraday").range("A1:I100")

End if


Next wb


end sub

 

[Colin Legg]

Hi,


The 7 open files must be open in the same Excel instance as the workbook which contains the code. If they're open in a different instance of Excel then the code won't see them.

 

[P0lar]

Thanks Colin,


I had a sinking feeling that may be the case, is there a way to get at them in VB at all or would I need to re-open them from the file directory into the right excel instance?

 

[Colin Legg]

Hi,


Possible, yes, but it wouldn't be easy. Why don't you open each workbook using your code - that way you wouldn't have to open them manually? The code would look something like this:

[pre]

Sub foo()

'list all of the 7 file names here, separated by commas
Const strFILE_NAMES As String = "File1.xlsx,File2.xlsx,File3.xlsx"

'this is the delimiter we use to separate the file names in the
'strFILE_NAMES constant
Const strDELIMITER As String = ","

'this is the folder the 7 files live in
Const strFOLDER As String = "F:MyDocuments"

Dim strarrFiles() As String
Dim strFile As String
Dim l As Long
Dim wkbTarget As Workbook

'this puts the 7 file names in a 1 dimensional array
strarrFiles = Split(strFILE_NAMES, strDELIMITER)

'this loops through the array so we can look at each filename
For l = LBound(strarrFiles, 1) To UBound(strarrFiles, 1)

strFile = strarrFiles(l)

'is a file with that name already open?
If WorkbookExists(strFile) Then
'yes, let's get a reference to it
Set wkbTarget = Workbooks(strFile)
Else
'if not then open it
Set wkbTarget = Workbooks.Open(strFOLDER & strFile)
End If

'now do whatever you need to do with wkbTarget and close it if necessary

Next l

End Sub

'this function checks if a file is open in the current instance of Excel
Function WorkbookExists(ByRef strWorkbookName As String) As Boolean
On Error Resume Next
WorkbookExists = Not Workbooks(strWorkbookName) Is Nothing
On Error GoTo 0
End Function

[/pre]

 

[P0lar]

Great, thanks Colin, I'll give that a go!


The software opens the excel report when I export it, so it's sat there looking ready to use but sadly more inaccessible than I was expecting it to be...