Plz Help

[Shariq.khan]

Hi! I want to Create a Blank Workbook using VB. But I've a problem with following Code:
I'm getting and Error: i.e. Object Required on the Line
With Application.FileSearch.
Plz Help me so that i can complete my work.

My Code is below:

Sub CreateBook()
x = InputBox("Enter WOrkbook Name")
Dim strDir As String
strDir = "E:\Check"
With Application.FileSearch
.LookIn = strDir
.FileName = x & ".xls"
.FileType = msoFileTypeAllFiles

If .Execute() > 0 Then

Workbooks.Open (strDir & "\" & x & ".xls")
Exit Sub
Else

Workbooks.Add.SaveAs (strDir & "\" & x & ".xls")
ActiveWorkbook.Close
End If
End With
End Sub

 

[Trevor G]

Please stick to the forum rules, you already have a thread on this.

Try adjusting to this

Workbooks.Add
ActiveWorkbook.SaveAs (strDir & "\" & x & ".xls")

 

[Shariq.khan]

I've a problem On This line. & still not removed.

"With Application.FileSearch"


Sub CreateBook()
x = InputBox("Enter WOrkbook Name")
Dim strDir As String
strDir = "E:\CHECK"
With Application.FileSearch
.LookIn = strDir
.FileName = x & ".xls"
.FileType = msoFileTypeAllFiles

If .Execute() > 0 Then

Workbooks.Open (strDir & "\" & x & ".xls")
Exit Sub
Else
Workbooks.Add
ActiveWorkBook.SaveAs (strDir & "\" & x & ".xls")
ActiveWorkBook.Close
End If
End With
End Sub

 

[Ruddles]

You don't say which version of Excel you're using but I think Application.FileSearch was removed after 2003.

Try Googling excel application.filesearch to check.

 

[JackDanIce]

Doesn't the forum rules also request a useful header is used instead of a generic one?

Shariq - have you tried searching for other code that can open a file?

 

[Trevor G]

You don't say which version of Excel you're using but I think Application.FileSearch was removed after 2003.

Try Googling excel application.filesearch to check.

In the first thread I asked whcih version and the reply came back with 2003.

 

[Trevor G]

I've a problem On This line. & still not removed.

"With Application.FileSearch"


Sub CreateBook()
x = InputBox("Enter WOrkbook Name")
Dim strDir As String
strDir = "E:\CHECK"
With Application.FileSearch
.LookIn = strDir
.FileName = x & ".xls"
.FileType = msoFileTypeAllFiles

If .Execute() > 0 Then

Workbooks.Open (strDir & "\" & x & ".xls")
Exit Sub
Else
Workbooks.Add
ActiveWorkBook.SaveAs (strDir & "\" & x & ".xls")
ActiveWorkBook.Close
End If
End With
End Sub

I have just tested this in version 2002 and it works, I would ask if you have option explicit at the top of the module? Have you checked the references in the VBA screen to make sure that Excel and Office Objects have been selected?

Option Explicit

Sub CreateBook()
Dim x As String
x = InputBox("Enter workbook name")
Dim strDir As String
strDir = "C:\VBA\"
With Application.FileSearch
.LookIn = strDir
.Filename = x & ".xls"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
Workbooks.Open (strDir & x & ".xls")
Exit Sub
Else
Workbooks.Add
ActiveWorkbook.SaveAs strDir & x & ".xls"
ActiveWorkbook.Close
End If
End With
End Sub