Find the position of file name error

[hgus393]

Hi all,

I am reading in some files from excel into access. I am using the path name and the file name as input factors. Both the path and the file name are static, the only thing that changes is the date ie the the file name is called for example abc_02-08-2011.xlsx
The problem is that these files are saved by different people than myself and they may not always be accurate in their naming for example aBc_02-08-2011.xlsx Is there a way to step into the code to see in what position the error occurs? ie in this case the second position (B instead of b) and in that case return to the user where the error occurs?

This is the code I have:

Sub HS()
 
Dim sPath As String, sFile As String, 
Dim MyXl As Object
Dim dtdate As Date
 
dtdate = InputBox("Import date", "Hold on...!")
 
sPath = "C:\temp\"
 
 
If Dir(sPath) = "" Then
MsgBox ("Catalogue cannot be found!")
Exit Sub
End If
 
 
sFile = Dir$(sPath & "abc_" & dtdate & ".xlsx")
Set MyXl = CreateObject("Excel.Application")
 
Do While sFile <> ""
GetHS sPath, sFile, dtdate, MyXl
sFile = Dir
Loop
 
Set MyXl = Nothing
 
 
 
End Sub
 
Sub GetHS(sPath As String, sFile As String, dtdate As Date, MyXl As Object)
Dim rs As Recordset
Dim i As Double
Dim varVektor As Variant
Dim sSheet As String
 
Set MyXl = GetObject(sPath & sFile)
 
sSheet = "ALL"
 
Set rs = CurrentDb.OpenRecordset("HS")
i = 2
varVektor = MyXl.Worksheets(sSheet).Range("A" & i & ":G" & i).Value
 
Do While varVektor(1, 1) <> ""
rs.AddNew
rs![date] = dtdate
rs![Column1] = varVektor(1, 2)
rs![Column2] = varVektor(1, 3)
rs![Column3] = varVektor(1, 4)
rs![Column4] = varVektor(1, 5)
rs![Column5] = Abs(varVektor(1, 6))
rs![Column6] = Abs(varVektor(1, 7))
rs.Update
i = i + 1
varVektor = MyXl.Worksheets(sSheet).Range("A" & i & ":G" & i).Value
Loop
 
MyXl.Close SaveChanges:=False
End Sub

Cheers
Rob

 

[Galaxiom]

Using a capital B instead of lowercase will have no effect on the filename as Windows is case insensitive.

If you do have real naming problems and want to deal with them programatically then you need to be able to describe the nature of valid names in specific terms. Otherwise the computer would have no way of determining what it is looking for.

You would be better of to sort the problem with the naming of the files in the first place by using code in Excel to save the data.

 

[hgus393]

Using a capital B instead of lowercase will have no effect on the filename as Windows is case insensitive.

If you do have real naming problems and want to deal with them programatically then you need to be able to describe the nature of valid names in specific terms. Otherwise the computer would have no way of determining what it is looking for.

You would be better of to sort the problem with the naming of the files in the first place by using code in Excel to save the data.

Bad example I guess. Thanks for an alternative solution.