ThisWorkbook.Path

Question

Sub a()

    Dim copy_range As Variant
  
    Application.Workbooks.Open Filename:=Dir(ThisWorkbook.Path & "/* (2).csv")
    copy_range = Sheets(1).Range("A1:C288").Value
    ActiveWorkbook.Close
    
    Sheets(2).Range("A1:C288") = copy_range
    
   
      
End Sub

In this part Dir(ThisWorkbook.Path & "/* (2).csv")

It says the file doesn't exist.

The current folder contains (2) to (19).csv . The full name of the file name is 20222022(2).csv 20222022(3).csv ~ 20222022(19).csv no see.

The folder path is like this. C:\Users\fauus\Desktop\람세스 제어반

`Dir(ThisWorkbook.Path & "/* (2).csv")` is missing an ampersand, has a `*` instead of a `"`. And the path separator is wrong if on Windows. — Lundt, Sep 23 '22 at 01:04
I'm a Korean. Can you upload the finished version? The full name of the file name is 20222022(2).csv 20222022(3).csv ~ 20222022(19).csv no see. — 김동진, Sep 23 '22 at 01:15
`Dir(ThisWorkbook.Path & "\*" & "(2).csv")` for Windows. I don't use macs see help if you do https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dir-function — Lundt, Sep 23 '22 at 01:28
Dir() only returns the *filename*, so if the current directory isn't `ThisWorkbook.Path` it won't find the file. — Tim Williams, Sep 23 '22 at 03:07
Application.Workbooks.Open Filename:=ThisWorkbook.Path & "\" & Dir(ThisWorkbook.Path & "\* (2).csv") — Tim Williams, Sep 23 '22 at 03:08

score 0 · Answer 1 · answered Sep 23 '22 at 11:15

0

You don't need the DIR and you slash is the wrong way round, the following should work:

Application.Workbooks.Open Filename:=ActiveWorkbook.Path & "\*(2).csv"

answered Sep 23 '22 at 11:15

Stuart

144
5

1

You can't pass wildcards to `Open()` – Tim Williams Sep 23 '22 at 15:39
Yes you can, wildcard worked fine when I ran the code myself – Stuart Sep 24 '22 at 16:14
I do see some disagreement here: https://stackoverflow.com/questions/19527415/using-a-wildcard-to-open-an-excel-workbook Did not work for me in testing though. – Tim Williams Sep 24 '22 at 16:26

score 0 · Answer 2 · answered Sep 23 '22 at 15:43

0

Dir() only returns the file name, not the full path, so if the current directory isn't ThisWorkbook.Path it won't find the file.

Sub a()

    Dim copy_range As Variant, f, fldr As String, wb As Workbook

    fldr = ThisWorkbook.Path & "\"
    f = Dir(fldr & "* (2).csv", vbNormal)

    If Len(f) > 0 Then
        Set wb = Workbooks.Open(fldr & f)
        copy_range = wb.Sheets(1).Range("A1:C288").Value
        wb.Close
        ThisWorkbook.Sheets(2).Range("A1:C288") = copy_range
    Else
        MsgBox "No file found!"
    End If

End Sub

answered Sep 23 '22 at 15:43

Tim Williams

154,628
8
97
125

I used the code you provided. But nothing changes. – 김동진 Oct 11 '22 at 07:10
What does `Debug.Print ThisWorkbook.Path` give you? Is it a local folder, network folder, or some type of http:// path (SharePoint/OneDrive/etc) ? – Tim Williams Oct 11 '22 at 15:21
C:\Users\fauus\Desktop\람세스 제어반 This is the path to the folder you are actually using. – 김동진 Oct 12 '22 at 00:25

ThisWorkbook.Path

2 Answers2