VBA to open file path given in specific cell.

[Jefe187]

All,
First time poster and VBA novice. I am trying to have VBA open a file on a shared drive where the file path can change. Therefore, I would like VBA to use the file path specified in cell A1 on "Report" tab. I can get the results by embedding the file path in the code, but this is not efficient as there are numerous files that will need to be opened and the change monthly. I am working in Excel 2013. Any help would be great! Here is what I have thus far:

Option Explicit


Sub PullFromFile()


Dim wkb As Workbook, wkbFrom As Workbook


Set wkb = ThisWorkbook
Set wkbFrom = Workbooks.Open("H:\027-12 2015 Checkbook.xls")


Dim wks As Worksheet
Set wks = wkbFrom.Sheets("RBM")


Dim rng As Range
Set rng = wks.Rows("1:10000")


rng.Copy wkb.Sheets("Sheet1").Range("A1") '


wkbFrom.Close False


End Sub

 

[Joe4]

Welcome to the Board!

You can do it like this:

Sub PullFromFile()

Dim wkb As Workbook, wkbFrom As Workbook
Dim fromPath As String

'   Get path from cell A1 on Report tab
fromPath = Sheets("Report").Range("A1")

'   Make sure there is a backslash at the end of the from path
If Right(fromPath, 1) <> "\" Then fromPath = fromPath & "\"

Set wkb = ThisWorkbook
Set wkbFrom = Workbooks.Open(fromPath & "027-12 2015 Checkbook.xls")
...

 

[Jefe187]

Thank you! Worked perfect! I also just noticed I need this to paste special with formatting and values as well as ungrouped. Being my first day on a site like this please advise if I should create a new post for this. Thanks again for the quick help!

 

[Joe4]

General rule of thumb:

If your question is a directly related follow-up, where it is helpful to anyone answering the new question to be aware of the old one, post it to that thread.

If it is a new question where having no knowledge of the previous answer doesn't matter (even if it is a question on the same project), post it to a new thread.

I think this new question falls in the later category.