0

Hi I have the below code, I would like to reference the workbook so I can copy and paste data from an already opened work book. However I am struggling to reference the workbook correctly. Can anyone help? :)

File name is "Pics & Benefits upload file.xlsm" 

    Workbooks.Open Filename:= _
    "U:\FBS\PROJECTS_TEAM\MI\Sean's Projects\PICS and Benefits\Pics & Benefits upload file.xlsm"

Dim PicsFile As String
PicsFile = "Pics & Benefits upload file.xlsm"

I would also like an error message to occur if someone else is currently in the file name "Pics & Benefits upload file.xlsm" I.e. if another user is in the file it will exit the macro. (I'm not too sure how to do this)

Thanks

Sean Bailey
  • 375
  • 3
  • 15
  • 33
  • 1
    to check if file is already open see: http://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open-using-vba – Fabrizio Jul 20 '15 at 10:57

2 Answers2

1

In addition to Mielks answer which will allow you to reference the correct workbook - in the same line you can also have the file open as read-only.

This code will tell you if a file is already open - returning TRUE or FALSE (it will also return TRUE if the file doesn't exist - either way it will say if you can do anything with that file name and location).

Public Function FileIsOpen(FullFilePath As String) As Boolean

    Dim ff As Long

    On Error Resume Next

    ff = FreeFile()
    Open FullFilePath For Input Lock Read As #ff
    Close ff
    FileIsOpen = (Err.Number <> 0)

    On Error GoTo 0

End Function

Public Sub test()

    If FileIsOpen("S:\Bartrup-CookD\MyTestFile.xls") Then
        MsgBox "That file's open, or doesn't exist - do something else."
    End If

End Sub
Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
0

You can do it like that:

Dim wkb As Excel.Workbook
Set wkb = Workbooks.Open(Filename:= _
    "U:\FBS\PROJECTS_TEAM\MI\Sean's Projects\PICS and Benefits\Pics & Benefits upload file.xlsm")
mielk
  • 3,890
  • 12
  • 19