Use of wildcard to find and open .csv based on cell value path

[xlr8urknowledge]

Yes I am a novice and cut and paste vba code mostly.
I have a code that will go to a cell on my main page for the normal file location path [c:\temp] then the file name is the same always except the generated file tag at the end. [Item28_to_57 Images_06022015_101719.csv"] "101719" is the tag. The macro below works however only because I know the tag for this file. I need to be able to get the one and only file in this location that matches first portion of the criteria [Item28_to_57 Images_06022015_"] but then can get the file regardless of the last portion [101719.csv]. It is a .csv file only. Once the file is found and opened, the copy and paste to destination needs to be to the sheet as below to my original WB and destination WS. I then need to know the name of the opened file so I can close it and return to my original WB and main WS.

Any help would probable retain years and minimize grey hair! I have been surfing the forum for solutions for a while but have found tidbits but can't put them together!

Thanks for any help!!
Running Windows 7 with MSOffice 2013

Sub Macro1()

'
Dim TimeDataWB As Excel.Workbook
Dim DatasheetWS As Excel.Worksheet
Dim TimedataWS As Excel.Worksheet
Dim destWB As Workbook

' Set :

Set TimeDataWB = Workbooks.Open(Filename:=(ActiveSheet.Range("H1").Value) & "\Item28_to_57 Images_06022015_101719.csv")
Set DatasheetWS = TimeDataWB.Sheets(1)


Range("A1:B31").Select
Selection.Copy
Windows("Time data.xlsm").Activate
Sheets("Time data").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Data sheet").Select
Windows("Item28_to_57 Images_06022015_101719.csv").Activate
ActiveWindow.Close
Range("A1").Select
End Sub

 

[Rijnsent]

Hi xlr8urknowledge,

I didn't test this code, but this should get you heading in the right direction,

Cheers,

Koen

Sub Macro1()

Set ResultWb = ActiveWorkbook
ImportDir = Worksheets("Import").Range("H1").Value  'e.g. C:\TEMP\
ImportFile = "Item28_to_57 Images_06022015_*.csv"

FoundFile = Dir(ImportDir & ImportFile)
If Len(FoundFile) = 0 Then
   MsgBox "The file does NOT exist."
   Exit Sub
Else
End If

Set TimeDataWB = Workbooks.Open(Filename:=(ImportDir & FoundFile))
Set DatasheetWS = TimeDataWB.Sheets(1)

DatasheetWS.Range("A1:B31").Copy
ResultWb.Worksheets("Time data").Range("A1").Paste
TimeDataWB.Close

End Sub

 

[xlr8urknowledge]

Thanks so much Rijnsent! With a few minor tweaks this worked great.
here is the final:

Sub Macro1()

Set ResultWb = ActiveWorkbook
ImportDir = Worksheets("Data sheet").Range("H1").Value 'e.g. C:\TEMP\
ImportFile = "Item28_to_57 Images_06022015_*.csv"

FoundFile = Dir(ImportDir & ImportFile)
If Len(FoundFile) = 0 Then
MsgBox "The file does NOT exist."
Exit Sub
Else
End If

Set TimeDataWB = Workbooks.Open(Filename:=(ImportDir & FoundFile))
Set DatasheetWS = TimeDataWB.Sheets(1)

DatasheetWS.Range("A1:B31").Copy
TimeDataWB.Close
Sheets("Time Data").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Data sheet").Select

End Sub

Again thank you for keeping me from going too grey!

 

[xlr8urknowledge]

Hi Rjinsent,
another issue I've come across was the code I "tweaked" works great but now I have a program that saves the folder with the same issue. The last part of the folder that is made is unique. Is there a way to modify the importDir line to also incorporate a wild card so no matter what the last part is it will open the folder?

for example:
ImportDir = Worksheets("Import").Range("H1"*).Value 'e.g. C:\TEMP\

I'm still looking around and trying different things but thought I would ask you.

Thank you for any advice as always!

 

[Rijnsent]

Hi xlr8urknowledge,

cool that it works! As a general tip: try to use as few .activate and .select statements as possible. I know that is what excel shows you when you record steps, but 9 out of 10 times your macro is 1) faster, 2) more readable, 3) less error-sensitive without them.
In order to work with directories, you will first need to find that directory. So say that your directory is named "C:\temp\1234_BLA\" where the BLA bit changes. The Dir function can be used in such a way that it only looks at directories:

FoundDirectory = Dir("C:\TEMP\1234_*", vbDirectory)

See this help page with a bit more detail: MS Excel: DIR Function (VBA)

In more practical code:

'All code cut up in small steps
SearchDir = Worksheets("Import").Range("H1").Value
'-> SearchDir now holds e.g. "C:\TEMP\1234_*", which is the value of cell H1.
'As an alternative: If you fill in H1 only the "Starts with" bit (C:\TEMP\1234_ in this example), then you would need an extra line of code like the next to add the asterisk, "glueing" strings together works with an & in VBA:
' SearchDir = Worksheets("Import").Range("H1").Value & "*" 
FoundDirectory = Dir(SearchDir, vbDirectory)
'-> If a directory with this name can be found, FoundDirectory should hold a (text) value, e.g. "C:\TEMP\1234_HURRAY"
If Len(FoundDirectory) = 0 Then
   Msgbox "No directory found!"
End If

Hope that solves your problem,

Koen

 

[xlr8urknowledge]

Thank you Koen,

here is a copy and paste with explanation of what is happening:

Sub Macro1()

Set ResultWb = ActiveWorkbook

SearchDir = Worksheets("Data sheet").Range("H1").Value & "*" 'H1 contains "C:\Temp\" the full path is "C:temp\Test\"
FoundDirectory = Dir(SearchDir, vbDirectory) ' returns [SearchDir = "C:\Temp\*"],[vbDirectory = 16]
If Len(FoundDirectory) = 0 Then 'Returns "FoundDirectory = ".""
MsgBox "The Directory does NOT exist."
Exit Sub
Else
End If

ImportFile = "Item28_to_57 Images_06022015_*.csv"

FoundFile = Dir(FoundDirectory & ImportFile) ' Returns [FoundDirectory = "."] , [ImportFile "Item28_to_57 Images_06022015_*.csv"]
If Len(FoundFile) = 0 Then 'Returns [FoundFile = ""]
MsgBox "The file does NOT exist." 'ERROR
Exit Sub
Else
End If

Set TimeDataWB = Workbooks.Open(Filename:=(FoundDirectory & FoundFile))
Set DatasheetWS = TimeDataWB.Sheets(1)

DatasheetWS.Range("A1:B31").Copy
TimeDataWB.Close
Sheets("Time Data").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Data sheet").Select

End Sub


It looks as though it cannot find the path C:\Temp\Test but finds "." instead. Should I still be using the importfile line? it finds all but not sure if it can find the file if the path is just "."???

Thanks again.
Ken

 

[Rijnsent]

Hi Ken,

that's nostaglia to me ... The first directories in a directory are . and .. , after that the real ones start. Luckily there are plenty of examples on how to find the next folder, I took some code from this one:
http://www.mrexcel.com/forum/excel-...et-full-folder-name-folder-path-wildcard.html

SearchDir = Worksheets("Data sheet").Range("H1").Value 'H1 contains "C:\Temp\" the full path is "C:temp\Test\"
DirPattern = "T*"  'Look only for directories/files starting with a T
FoundItem = Dir(SearchDir & DirPattern, vbDirectory)

Do While Len(FoundItem) > 0
    If Left(FoundItem, 1) <> "." Then
        If (GetAttr(SearchDir & FoundItem) And vbDirectory) = vbDirectory Then
            FirstMatch = SearchDir & FoundItem & "\"
            Exit Do
        End If
    End If
    FoundItem = Dir
Loop

If Len(FirstMatch) = 0 Then
    MsgBox "The Directory does NOT exist."
    Exit Sub
Else
End If

Cheers,

Koen

 

[xlr8urknowledge]

Thanks for all of your help Koen! Finally got everything to work mostly based on your code suggestions. Appreciate all of your time and expertise!