Does not recognise Excel files (1 Viewer)

[aziz rasul]

I am using a vbs file to open two MS Excel files (it's a long story as to why). The two Excel files open OK and I can see them. However when I enumerate through the workbooks, they don't exist. Is this because the Excel files were opened using a VB script?

If I open the two MS Excel files in MS Access, then I can enumerate them.

 

[Isaac]

Please share the VBScript code. This post should probably be in the Other Software category.

 

[aziz rasul]

Is there a way of transferring the thread over?

 

[Isaac]

A moderator may see it and act accordingly.

 

[aziz rasul]

Public Sub Close_Microsoft_Office_Activation_Wizard_And_Open_Excel_File(strExcelPath As String, strExcelFile As String, Optional strPassword As String)
'The purpose of this function is to close the Microsoft Office Activation Wizard dialog box if it exists and open strExcelFile.

    Const SW_SHOWNORMAL = 1
    Const SW_MAXIMIZE = 3
    Const WM_SYSKEYDOWN = &H104
    Const WM_SETTEXT As Long = &HC
    Const WM_KEYDOWN As Integer = &H100
    Const WM_KEYUP As Integer = &H101
    Const GW_CHILD = 5
    Const WM_SHIFT = &H10 'SHIFT
    Const WM_C = &H43 'Capital C
    Const WM_H = &H48 'Capital H
    Const WM_K = &H4B 'Capital K
    Const WM_R = &H52 'Capital R

    Dim strFile As String
    Dim strLine1 As String
    Dim strLine2 As String
    Dim strLine3 As String
    Dim strVBSFile As String
    Dim wsh As Object
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1
    Dim fso As Object
    Dim objTextFile As Object
    Dim WinWnd As Long
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    strFilePath = CurrentProject.Path & "\"
    strVBSFile = "Open MS Excel File.vbs"
    strFile = Chr(34) & strFilePath & strVBSFile & Chr(34)
    
    Call DeleteSpecificFile(strFilePath & strVBSFile)
    
    strLine1 = "Set objExcel = CreateObject(" & Chr(34) & "Excel.Application" & Chr(34) & ")"
    strLine2 = "objExcel.Visible = True"
    strLine3 = "objExcel.Workbooks.Open" & Chr(34) & strExcelPath & strExcelFile & Chr(34)
    
    If strPassword <> "" Then
        strLine3 = "objExcel.Workbooks.Open" & Chr(34) & strExcelPath & strExcelFile & Chr(34) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(34) & strPassword & Chr(34)
    End If
    
    Set objTextFile = fso.CreateTextFile(strFilePath & strVBSFile)

    With objTextFile
        .WriteLine strLine1
        .WriteLine strLine2
        .WriteLine strLine3
        .Close
    End With

    Set fso = Nothing
    Set objTextFile = Nothing

    Set wsh = VBA.CreateObject("WScript.Shell")
    wsh.Run strFile, windowStyle, waitOnReturn
    Set wsh = Nothing
    
    Sleep (2500)
    
    WinWnd = FindWindow(vbNullString, "Microsoft Office Activation Wizard")
    
    If WinWnd <> 0 Then
        Call SendMessage(WinWnd, WM_SHIFT, WM_C, 0)
    End If

    Call DeleteSpecificFile(strFilePath & strVBSFile)
    
End Sub

 

[Gasman]

I am using a vbs file to open two MS Excel files (it's a long story as to why). The two Excel files open OK and I can see them. However when I enumerate through the workbooks, they don't exist. Is this because the Excel files were opened using a VB script?

If I open the two MS Excel files in MS Access, then I can enumerate them.

I would have thought so.?
How is any program able to know if a file is open unless it opened it.? It *might* be able to know, if it cannot access a file because it is locked by another program?

 

[Isaac]

I am not sure why you are using two separate vbs files, one with a procedure and one to call the procedure, apparently.

To summarize, you can test this out in VBS yourself:

dim appExcel, lngCountOfWorkbooks, wb
set appExcel=createobject("excel.application")
set wb = appExcel.Workbooks.open("path to some valid excel file")
lngCountOfWorkbooks = appExcel.Workbooks.Count
msgbox lngCountOfWorkbooks

... VBScript has no problem "enumerating" through the excel application object's Workbooks collection, but it depends on how YOU are utilizing the application and workbook objects. You may need to do it all in one VBScript code instead of having one script call another script, or, you may need to adjust the "other script" to return a workbooks collection or something, I'm not sure I would do it that way in the first place.

Sorry, I would have used code tags but the code tag generator on AWF is not working

 

[aziz rasul]

I'm guesssing that I need to enumerate the workbooks within the VBS file and to use the additional code to do what I want all in the VBS file. Utimately I am trying to transfer a worksheet from 1 Excel file into a new Excel file. However the end user may not have a fully activated MS Office, so I am trying to get it to work on that basis, hence using the VBS file (otherwise the code hangs if I use MS Access VBA as the opened MS Activation wizard dialog needs to be closed manually). Hope that makes sense.

If WinWnd = 0 then no matter. I have attached the stuff to this thread, if anyone is interested or I can post a separate thread.