0

I have an automatically excel (excel A) that copy and paste information from another excel (excel B). I already control if the excelB exist or not.

I want to do this:

If "workbookExcelBisopen" Then 'what can i put inside the if
   'What can i put here for make the code work
Else 
    Set ExcelB = Application.Workbooks.Open(".xlsx", False, True)
End if

Sheets("Sheet1excelB").Select

The main problem I get is if the workbook is already open, it display a message saying "ExcelB is already open. Do you want to save the changes?". I want to avoid this kind of message.

Thank you into advance.

David_helo
  • 187
  • 2
  • 2
  • 12
  • 1
    `Set ExcelB = Application.Workbooks("nameofexcelBworkbook.xlsx")` – Vincent G Jul 23 '18 at 12:43
  • It dosn't work. – David_helo Jul 23 '18 at 13:34
  • 1
    It should, but you didn't previously mentioned that you also needed the code for "workbookExcelBisopen". – Vincent G Jul 23 '18 at 14:08
  • I mean, without the If, if i put the code you mention instead of "Set ExcelB = Application.Workbooks.Open(".xlsx", False, True)" it doesn't work. – David_helo Jul 23 '18 at 14:15
  • @David_helo: here is another reference I found for you (using [Google](https://www.google.com/search?q=excel+vba+check+if+a+workbook+is+open&ie=utf-8&oe=utf-8&client=firefox-b-ab)): [How to quickly check if a workbook is open](https://www.extendoffice.com/documents/excel/3600-excel-check-if-a-file-is-open.html) – Our Man in Bananas Jul 23 '18 at 14:19
  • Thanks @OurManinBananas i'll try tomorrow and tell you what i get. And sorry if it's duplicate. Sometimes for people that are not used to program every day, sometimes it's hard to ask in the correct way (at least from my point of view). – David_helo Jul 23 '18 at 14:31
  • @David_helo: it's not a problem - most of the people on SO want to help, but expect to see effort (both in trial and error, plus research) by the questioner ... – Our Man in Bananas Jul 23 '18 at 14:34
  • I solve it! But i don't know how to give u points @OurManinBananas – David_helo Jul 24 '18 at 06:58

1 Answers1

0

I get the solution thanks to the @Our Man in Bananas solution in SO.

 Sub test()

        Dim wb As Workbook

        Set wb = GetWorkbook("C:\Users\dick\Dropbox\Excel\Hoops.xls")

        If Not wb Is Nothing Then
            Debug.Print wb.Name
        End If

End Sub


Public Function GetWorkbook(ByVal sFullName As String) As Workbook

    Dim sFile As String
    Dim wbReturn As Workbook

    sFile = Dir(sFullName)

    On Error Resume Next
        Set wbReturn = Workbooks(sFile)

        If wbReturn Is Nothing Then
            Set wbReturn = Workbooks.Open(sFullName)
        End If
    On Error GoTo 0

    Set GetWorkbook = wbReturn

End Function

Than you for the answer.

David_helo
  • 187
  • 2
  • 2
  • 12