Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

vba - Finding a workbook in one of multiple Excel instances

I have a macro in Outlook VBA to grab data from an open Excel workbook ("Workbook1").

I reference the workbook as follows:

Dim objApp As Excel.Application
Set objApp = GetObject(, "Excel.Application")
Set wb = objApp.Workbooks("Workbook1.xlsx")

I often get runtime error 9, that VBA cannot find the workbook.

I think since I have more than one Excel instance open, VBA is looking for my workbook in the wrong instance.

How do I reference my workbook when running more than one Excel instance?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Try This


Option Explicit
Public Sub Example()
    Dim xlApp As Excel.Application
    Dim Book As Workbook

    Set xlApp = New Excel.Application
    Set Book = xlApp.Workbooks.Open(Environ( _
                        "USERPROFILE") & "DocumentsTempTemp.xlsm")

    ' Do something

    Set xlApp = Nothing
    Set Book = Nothing
End Sub

Or This which works for me.


Option Explicit
Public Sub Example()
    Dim xlApp As Excel.Application
    Dim Book As Excel.Workbook
    Dim Sht As Excel.Worksheet
    Dim xlStarted As Boolean
    Dim FilePath As String
    Dim Cell As Range
    Dim Rng As Range

'   // File Path
    FilePath = "C:TempTemp.xlsx"
    Debug.Print FilePath

'   // If Error get Excel Application
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")

    If Err <> 0 Then
        Application.StatusBar = "Please wait while Excel source is opened ... "
        Set xlApp = CreateObject("Excel.Application")
        xlStarted = True
    End If
    On Error GoTo 0

'   // Open Workbook, Sheet1 to get data
    Set Book = xlApp.Workbooks.Open(FilePath)
    Set Sht = Book.Sheets("Sheet1")

'   // Set range variable
    Set Rng = Sht.Range("A1")

    For Each Cell In Rng
        Debug.Print Cell.Value
    Next


    '// Close & SaveChanges
    Book.Close SaveChanges:=True
    If xlStarted Then
        xlApp.Quit
    End If

    Set xlApp = Nothing
    Set Book = Nothing
    Set Sht = Nothing
End Sub

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...