Passing full directory path plus filename into String variable using VBA in Excel?

Question

Borrowed the code and tweaked. Everything worked fine until strPath2 only receives filename instead of the entire file path. After searching there is surprisingly no answer.

I basically want to let Dir find any file that contain the word "Hello" in its filename. The start and the end of the filename varies. Is there any function/method that returns the full directory path with filename as String?

Sub TransferData()


    Dim strPath2 As String
    Dim wbkWorkbook1 As Workbook
    Dim wbkWorkbook2 As Workbook

    strPath2 = Dir("C:\*Hello*.xlsx")

    Set wbkWorkbook1 = ThisWorkbook 
    Set wbkWorkbook2 = Workbooks.Open(strPath2)

    wbkWorkbook2.Worksheets("Sheet1").Range("A1:J20").Copy
    wbkWorkbook1.Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteValues


End Sub

purpleblau · Accepted Answer · 2019-12-21 18:38:39Z

4

It's very simple. I figured it out myself.

strPath2 = "C:\" & Dir("C:\*Hello*.xlsx")

answered Dec 21, 2019 at 18:38

purpleblau

5892 gold badges8 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Passing full directory path plus filename into String variable using VBA in Excel?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related