Asked by duncanb7
at 2024-07-08 08:19:47
Point:250 Replies:8 POST_ID:828356USER_ID:11059
Topic:
Visual Basic Programming;Microsoft Excel Spreadsheet Software;Microsoft Office Suite
I have one file xls called "advancedata2010" in my folder, and I want to create
one new other file called "data2010" without the word of "advanced". The code is as
follows. But it won't create file of data2010 , and then I solved the problem
by rename the similar name file of "advanceddata2010" to "advacnedata", re-run
the program, and then the file of "data2010" is shown up and created
So my question is how to filesearch the eaxct name of file in VBA ?
Please advise
Duncan
Sub main()
Dim wkdir As String
Dim datafilename As String
wkdir = "d:excel"
datafilename = "data2010"
Call DoesWorkBookExist(wkdir, datafilename)
Sub DoesWorkBookExist(wkdir As String, datafilename As String)
'Test to see if a Workbook exists
Dim i As Integer
With Application.FileSearch
.LookIn = wkdir
'* represents wildcard characters
.Filename = datafilename
If .Execute > 0 Then 'Workbook exists
' Debug.Print "There is a Workbook."
Else 'There is NOt a Workbook
' Debug.Print "The Workbook does not exist"
'Workbooks.Add
Workbooks.Add.SaveAs wkdir & datafilename
ActiveWorkbook.Close False
End If
End With
End Sub
one new other file called "data2010" without the word of "advanced". The code is as
follows. But it won't create file of data2010 , and then I solved the problem
by rename the similar name file of "advanceddata2010" to "advacnedata", re-run
the program, and then the file of "data2010" is shown up and created
So my question is how to filesearch the eaxct name of file in VBA ?
Please advise
Duncan
Sub main()
Dim wkdir As String
Dim datafilename As String
wkdir = "d:excel"
datafilename = "data2010"
Call DoesWorkBookExist(wkdir, datafilename)
Sub DoesWorkBookExist(wkdir As String, datafilename As String)
'Test to see if a Workbook exists
Dim i As Integer
With Application.FileSearch
.LookIn = wkdir
'* represents wildcard characters
.Filename = datafilename
If .Execute > 0 Then 'Workbook exists
' Debug.Print "There is a Workbook."
Else 'There is NOt a Workbook
' Debug.Print "The Workbook does not exist"
'Workbooks.Add
Workbooks.Add.SaveAs wkdir & datafilename
ActiveWorkbook.Close False
End If
End With
End Sub
