xlr8urknowledge
New Member
- Joined
- Dec 7, 2012
- Messages
- 11
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
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
Last edited: