Excel macro not recogniising "*" wildcard sign in VB code
HI Guys, recently IT have changed a Registry key on my laptop to speed up Excel, but as a result my macro is not working any more. I am getting a run-time error "1004".
Sorry, we could not find "\\gazal\GFPUK\CTMR\Summary\*_FMLDN_Murexdata_*04Mar2021
The Code says :
Workbooks.OpenFilename:_ "\\gazal\GFPUK\CTMR\Summary\" & "*_FMDLN_Murexdata_*" & filePath1
The macro is not recognising the "*" wildcard sign any more since the Registry Key was changed. Anyone have any ideas how to fix it?
Sorry, we could not find "\\gazal\GFPUK\CTMR\Summary\*_FMLDN_Murexdata_*04Mar2021
The Code says :
Workbooks.OpenFilename:_ "\\gazal\GFPUK\CTMR\Summary\" & "*_FMDLN_Murexdata_*" & filePath1
The macro is not recognising the "*" wildcard sign any more since the Registry Key was changed. Anyone have any ideas how to fix it?
ASKER
No, its not working. It used to work before the registry key was changed. Would it be related to the filepath variable?
Sheets ("Setup").Activate
filepath1 = Activesheet.Range("ErrorLogRange")
Sheets ("Setup").Activate
filepath1 = Activesheet.Range("ErrorLogRange")
Workbooks.Open Filename:="\\gazal\GFPUK\C
ASKER
Hi Martin, that is the same code I had originally. Why would it suddenly not work after a Registry key change?
ASKER
When I record a macro, the code shows:
Workbooks.Open Filename:=
"\\gazal\GFPUK\CTMR\Summary\20210304230153_FMDLN_MurexDate_20210305_summary.xls."
Workbooks.Open Filename:=
"\\gazal\GFPUK\CTMR\Summary\20210304230153_FMDLN_MurexDate_20210305_summary.xls."
Justin
As far as I know you can't use wildcards in Workbooks.Open, you need to use the full path and filename.
Are you sure there isn't some other code that's getting that information, perhaps using Dir.
As far as I know you can't use wildcards in Workbooks.Open, you need to use the full path and filename.
Are you sure there isn't some other code that's getting that information, perhaps using Dir.
ASKER
Yep, Here's the code:
Sub Errorlog()
Dim filepath as string
Dim wb, wb1 as Workbook
Dim Target as Variant
Application.displayAlerts = False
Sheets ("Setup").Activate
filepath0 = ActiveSheet.Range ("filePath0")
filepath1 = Activesheet.Range ("ErrorlogRange")
fiename0 = ActiveWorkbook.Name
ChDir = "\\gazal\GFPUK\CTMR\Summary"
Workbooks.OpenFilename:=
"\\gaza1\GFPUK\Summary\" & "*_FMDLN_MurexDate_*" & filepath1
Sub Errorlog()
Dim filepath as string
Dim wb, wb1 as Workbook
Dim Target as Variant
Application.displayAlerts = False
Sheets ("Setup").Activate
filepath0 = ActiveSheet.Range ("filePath0")
filepath1 = Activesheet.Range ("ErrorlogRange")
fiename0 = ActiveWorkbook.Name
ChDir = "\\gazal\GFPUK\CTMR\Summary"
Workbooks.OpenFilename:=
"\\gaza1\GFPUK\Summary\" & "*_FMDLN_MurexDate_*" & filepath1
Is there a space character after Workbooks.Open? There needs to be one, but it is consistently missing in the Asker’s posts.
ASKER
Yep, sorry I am typing it out manually as i cannot transfer any code from my work laptop and not allowed to use the website from the work laptop,
Workbooks.Open Filename:= _
"\\gazal\GFPUK\CTMR\Summary\" & "*_FMDLN_MurexDate_" & filePath1
Workbooks.Open Filename:= _
"\\gazal\GFPUK\CTMR\Summary\" & "*_FMDLN_MurexDate_" & filePath1
I would have sworn that Workbooks.Open could not use a wildcard, but another forum suggests it is possible in limited circumstances not met with code in this thread. https://stackoverflow.com/questions/19527415/using-a-wildcard-to-open-an-excel-workbook
The recommended procedure uses Dir function with wildcard to capture the exact file name and path, then plug that into Workbooks.Open.
ASKER
What syntax would I use for the Dir function?
Justin
What is the name of the file you are trying to open and where is it located?
Is it the only Excel file in the folder?
What is the name of the file you are trying to open and where is it located?
Is it the only Excel file in the folder?
ASKER
"\\gazal\GFPUK\CTMR\Summary\" & "*_FMDLN_MurexDate_" & filePath1.
It is not the only Excel file in the folder
It is not the only Excel file in the folder
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Fantastic help
Workbooks.OpenFilename:_ "\\gazal\GFPUK\CTMR\Summar