excel not finding file with *

[jordanburch]

Hey everyone

I have the following path.

Workbooks.Open ("K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Source Files\CRIS\" & ThisWorkbook.Worksheets("Variables").Range("A4").Value & "\" & Left(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 6) & "\" & "*.xlsx")

The path looks just fine and works on a different month, however this month it just says it cant find that file and shows it with the above path and the *.xlsx like the file should be named *.xlsx vs the acutal file name. Its the only file inside that path and I want it to open that file and just put the * it seemed to work before but now it doesnt that I tried it in a new month. Any ideas?

 

[Fluff]

You cannot use a wildcard like that, try

Dim Fname As String

Fname = Dir("K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Source Files\CRIS\" & ThisWorkbook.Worksheets("Variables").Range("A4").Value & "\" & Left(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 6) & "\" & "*.xlsx")
If Fname <> "" Then Workbooks.Open Fname

 

[jordanburch]

You cannot use a wildcard like that, try

Dim Fname As String

Fname = Dir("K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Source Files\CRIS\" & ThisWorkbook.Worksheets("Variables").Range("A4").Value & "\" & Left(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 6) & "\" & "*.xlsx")
If Fname <> "" Then Workbooks.Open Fname

thanks fluff! I tried that but it doesnt seem to open anything up? it skips to the next part but it doesnt download any file nor open any file. any ideas? the next part is trying to select a worksheet in that book and it doesnt find it because its not opening the file.

 

[Fluff]

If you add a message box showing the value of Fname what does it say?

 

[jordanburch]

You cannot use a wildcard like that, try

Dim Fname As String

Fname = Dir("K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Source Files\CRIS\" & ThisWorkbook.Worksheets("Variables").Range("A4").Value & "\" & Left(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 6) & "\" & "*.xlsx")
If Fname <> "" Then Workbooks.Open Fname

Sub CRISimport()
Dim wb As Workbook

'ThisWorkbook.Worksheets("Variables").Range("A1").Value = InputBox("Enter month I.E. 08-MAY20", Default:="08-MAY20")
'ThisWorkbook.Worksheets("Variables").Range("A3").Value = Right(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 5)
'
'ThisWorkbook.Worksheets("Variables").Range("A3").Value = "FY" & Right(ThisWorkbook.Worksheets("Variables").Range("A3").Value, 2)
'ThisWorkbook.Worksheets("Variables").Range("A4").Value = ThisWorkbook.Worksheets("Variables").Range("A3").Value
Dim Fname As String

Fname = Dir("K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Source Files\CRIS\" & ThisWorkbook.Worksheets("Variables").Range("A4").Value & "\" & Left(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 6) & "\" & "*.xlsx")
If Fname <> "" Then Workbooks.Open Fname

Application.ScreenUpdating = True


     Sheets("3875 Indy").Select
    Sheets("3875 Indy").Copy After:=Workbooks("Suspense automation.xlsm").Sheets(2)

End Sub

thats the whole code

 

[jordanburch]

If you add a message box showing the value of Fname what does it say?

it shows a blank box

 

[Fluff]

In that case what this return

MsgBox "K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Source Files\CRIS\" & ThisWorkbook.Worksheets("Variables").Range("A4").Value & "\" & Left(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 6) & "\"

 

[jordanburch]

In that case what this return

MsgBox "K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Source Files\CRIS\" & ThisWorkbook.Worksheets("Variables").Range("A4").Value & "\" & Left(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 6) & "\"

so weird thing is they change the file path structure going forward. I think I figured it out that I need a totally different variable. This got me on the right track. Thanks fluff! Will update in the morning! Have a great evening sir!

Jordan

 

[Fluff]

You're welcome & thanks for the feedback.

 

[jordanburch]

You're welcome & thanks for the feedback.

Morning Fluff! So I got the path corrected. It not is saying run time error 1004 cannot find file CRIS TI97 3875 JAN 2020.xlsx

I believe that is the correct filename it looks right. Not sure why its erroring out there.

Jordan