If statement / error trapping

[jonnj]

In the code below, I am importing dbf files from DB4.
I recorded a macro and have made adjustments that I need so far.

I created a inputbox variable spath, which pulls the file from a db4 folder with two subfolders. i.e ( c:\db4\port\111111.

The spath inputbox gets the dbf from the subfolder with 6 digits in this case the "111111". This subfolder has many folders with the 6 digits. When a correct subfolder number is entered, the file is imported. When an incorrect number is entered into the input box, I get a MS JET OLE DB INITIALIATION INFORMATION ERROR, which I want to avoid.

What I am looking for is an if statement that sees that the folder number is not listed and gives a msgbox error and ends the sub. I have had trouble tying in the variable to do so...

for example, the variable ds= 426950 and the if statement

If spath <> ds Then
MsgBox "Assignment Number not Found"
Exit Sub
End If
.

....work fine because 425950 is a listed number. I cannot leave the ds=426950 in the code. I need something that will replace that and know that number entered into the spath is correct or incorrect.

thank you in advance for any help you can give me on this problem.





Private Sub CommandButton2_Click()
'
' ImportName Macro
' Macro recorded 5/9/2006 by laxlunn
'
'
Dim spath As String
Dim ds As Double
ds = 426950


spath = InputBox("Enter Assignment Number", "Import Address", "")


If spath <> ds Then
MsgBox "Assignment Number not Found"
Exit Sub
End If

If Not IsNull(Range("Aa1001:bh1002")) Then
Range("Aa1001:bh1002").Select
Selection.ClearContents
Range("a1").Select

Else
If spath = vbNullString Then
Range("Aa1001:bh1002").Select
Selection.ClearContents
Range("a1").Select
Exit Sub
End If
End If



With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\db4\PORT\" & spath & ";Mode=Share Deny Write;Extended Proper" _
, _
"ties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=18;Jet OLED" _
, _
"Batabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Passwo" _
, _
"rd="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet O" _
, "LEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"), Destination _
:=Range("aa1001"))
.CommandType = xlCmdTable
.CommandText = Array("F_hist")
.Name = "F_hist"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
'.SourceDataFile = "C:\db4\PORT\426950\F_HIST
.Refresh BackgroundQuery:=False
End With
End Sub



John

 

[PHV]

Have a look at the Dir function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Loomah]

John
Where've I seen that suggestion before!!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[jonnj]

Hello and thansks PHV and Loomah (again) for replying.

I had tried the Dir function before...and now again after your advice. I also have googled the Dir function but I cannot get it to work. I cannot tie in my spath variable which is a subfolder name against the dir function I created. Therefore the if statement cannot execute itself properly.

Any other leads would be most appreciated.

Thx again
John

 

[PHV]

Why googling ? The F1 key is your fiend !
A starting point:
If Dir("C:\db4\PORT\" & spath, vbDirectory) = "" Then
MsgBox "Assignment Number " & spath & " not Found"
Exit Sub
End If


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jonnj]

Hi PHV...

Not only was that the starting point..it was the solution.
I had something close to this with vbdirectory, but couldnt put all the pieces to together nicely the way you did.

Thank you very much, your time and help

Hope you hit the lottery.

John