Open the Current Directory (on SharePoint)

[ermccarthy]

Good Day!

I have a workbook that I have put in the below VBA. In theory, it is a simple and straight forward module..open workbook, copy from it, paste into another workbook, close workbook... I am trying to make this as end-user friendly as possible, as not all of my users are not technically inclined.

I can not get the ChDir to work properly. I would like the OpenFilename box to open to the same directory that the current open file sits in. By way of example, if the current file is in C:/Documents, then I would like the OpenFilename box to open to that same directory.

Now just because I need to make this more tricky.....the directory that both files will be sitting in is a SharePoint directory.

Any Ideas??

<code>
Sub GrabData()
'
' GrabData Macro
'
Dim wbThis As Workbook
Dim wbOpen As Workbook
Dim strFileName
myDir = CurDir()
Set wbThis = ThisWorkbook
ChDir myDir
strFileName = Application.GetOpenFilename
If TypeName(strFileName) <> "Boolean" Then
Workbooks.OpenText strFileName
Set wbOpen = ActiveWorkbook
End If
wbOpen.Sheets("Flow").Range("A1:G41").Copy

wbThis.Sheets("Flow").Range("A1:G41").PasteSpecial xlPasteValues
wbOpen.Activate
Application.CutCopyMode = False
ActiveWindow.Close
Sheets("Flow").Select

End Sub</code>