Scanning file name for particular data

[only412c]

I've created a system cap collect data from excel files. The system currently load data into Access from the Excel file. I want the system to be able to read the contract number put in the file name. For example, all contract start with a "C". It there a easy way to read the file to find the contract the user has selected? e.g., name of file is "Transdata.C3453.xls"?[\b]

 

[p27br]

you need to save the file name in a variable then use the mid function to get the contract numer :

dim strFileName as string
dim strContractNumber as string

strfilename = "Transdata.C3453.xls"
strcontractnumber = mid$(strfilename,12,4)

where are the filenames stored ? in a table ? the method to get the filename varies wether it's in a table or not.

 

[only412c]

The naming of the file may vary in length but the file will have a contract starting with "C". The last part of the file will be fixed in length e.g., "C3453.xls" How can I trim the ".xls" extension and have the final outcome just be "c3453"? I want to compare what the contract the user selected in the form and what the import file has as a contract.

Example:

User selects contract C3453
Contract is store in the variable StrContract
Strcontract is compare to the file name string variable
If strcontract = strcontractnumber
Then load the file ....
Else
MsgBox "Selected Contract does not match contract contract in file name, Please rename your file in correct format."
Endif

 

[GGleason]

only412c,

I got this module from another post, and I wanted to provide a link to give proper attribution, but I couldn't get to the site. If someone knows, then by all means place the link.

Here is the ReplaceString module code:
[tt]
Public Function ReplaceString(ByVal SourceString As String, ByVal OriginalString As String, ByVal NewString As String) As String

'Recursive function ReplaceString searches Source string and replaces ALL OCCURRENCES of OriginalString with NewString.
'If a value for NewString is ommitted (or IsEmpty), then all occurrences of OriginalString are removed from the SourceString!

Dim Position As Integer

If SourceString = "" Or IsNull(SourceString) Then
ReplaceString = SourceString
Else
Position = InStr(1, SourceString, OriginalString)
If Position > 0 Then
ReplaceString = (Mid$(SourceString, 1, Position - 1) & NewString & ReplaceString(Mid(SourceString, Position + Len(OriginalString)), OriginalString, NewString))
Else
ReplaceString = SourceString
End If
End If

End Function
[/tt]
To call the function, do something like this:
[tt]
strExcelFile = "C3453.xls"
strContract = ReplaceString(LCase(strExcelFile), ".xls", "&quot
[/tt]
hth,
GGleason

 

[paoconnell]

Use the built in functions Instr() and InstrRev() to select out the contract number:

Given that the file is in a string called Filename, the code is like this:
dim StartCon as Long
dim EndCon as long
StartCon=instr(Filename,".C",vbTextCompare)+1
'search will be case insensitive

EndCon=InstrRev(Filename,".&quot

ContractNo=Mid(Filename,StartCon,(EndCon-Startcon +1))

Pat O'Connell