Searching through a text file in excel using databse commands????

[Mightyginger]

Hi,

I have a text file with a lot of dates in it - they are holidays. In excel I need to write some vba code whereby I can type a date into a cell and it then searches through the textfile and checks to see if that date exists - if it does then it's a holiday.

The textfile looks like this.

01/01/03
02/01/03
03/01/03
(e.g day/month/year)
but can be made up ina different format if necessary.


My first though was just to open the text file, read it into an array and then check through the array to see if it existed. Someone has suggested that it might be easier instead to set the textfile as a db and then search through it directly and treat it just like a db. I've not used any of these commands before and was wondering (this is a bit cheeky I know) if someone could give me a rough outline of how the code might do to once the textfile is set as the db to then query a variable against the entries to see if the variable exists in the file.


Thanks again guys,


Neil.

 

[Nikita6003]

Hiya Neil

had me day off yesterday (had a course on communication ;-)) so am a bit late

Can u give me an idea of how/where you get your filename variables from? I.e. from a worksheet, is it a predetermined list, is it all files in a particular directory, or something else?

Thanks!


Cheers
Nikki
Look, mommy, I'm flying!

 

[Mightyginger]

LOL

Hey, how you doing? I was hoping to have them all stored in a textfile in the directory, say called settings.txt. I would have a very simple spreadhseet which allows the user to change the directory and it changes the parameter in the text file.

As for the actual filenames themselves the files will be called gbp.txt, eur.txt, usd.txt and the gbp,eur or usd part will be passed in as an extra paramter in the function GingerBumpDates

 

[Mightyginger]

also, another problem I'm having....

basically I thought my date error problem was fixed - but it's not. VBA is still getting confused. Is there anyway that I can enter the date as an excel dateserial number, do all the date operations like adding days and then query the text file with the date serial number rather than the date and have the text file filled with date serials rather than normal dates?

If I do this should I still declare the date variable As Date because it then automatically converts it into a date or should I read it in as something different instead?


Neil.

 

[Nikita6003]

Hiya Neil,

for the variable text file name you could simply use the ReadLine option to read each line of your Settings.txt file, and use the filename your app reads per line as parameter to pass to your proc:

Sub LoopThroughSettingsFile()
    Dim l_sReadLine As String
    Dim l_datDate As Date
    
    'Open the SETTINGS file as text file; this allows reading the file line by line
    Open "N:\Settings.txt" For Input As #1
    
    'Loop though the SETTINGS.TXT file
    Do While Not EOF(1)
        'assign text of current line to l_sReadline
        Line Input #1, l_sReadLine
        
        l_sReadLine = Trim(l_sReadLine)
        
        If Not QueryTextFile(l_sReadLine, l_datDate) Then
            MsgBox "An error occurred trying to read the date file " & l_sReadLine
        End If
        
    Loop
    
    'Close text file
    Close #1
    
End Sub


 Function QueryTextFile(p_sFileName As String, p_datDate As Date) As Boolean

    Dim rsData As ADODB.Recordset
    Dim szConnect As String
    Dim szSQL As String
    
    QueryTextFile = True

    'Create the connection string.
    szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=H:\;" & _
                "Extended Properties=Text;"
                
    'Create the SQL statement.
    'szSQL = "SELECT * FROM Dates.txt WHERE Date = #02/01/03#;"
    szSQL = "SELECT * FROM " & p_sFileName & " WHERE Date = #" & p_datDate & "#;"

    Set rsData = New ADODB.Recordset
    rsData.Open szSQL, szConnect, adOpenForwardOnly, _
            adLockReadOnly, adCmdText
    
    'Check to make sure received data.
    If Not rsData.EOF Then
        'Dump returned data onto Sheet 1
        ThisWorkbook.Worksheets("Sheet1").Range("A1").CopyFromRecordset rsData
    Else
        QueryTextFile = False
        'MsgBox "No records returned.", vbCritical
    End If
    
    'Tidy up.
    rsData.Close
    Set rsData = Nothing
    
End Function

My guess is you'll need to tweak the code a little to allow for the Settings.txt file to be entered by the user - or you could take it straight off the worksheet.

As for the dates thisng:
yes, you can use dateserials in both the VBA and the txt files you're reading - this'll ensure you're using the correct dates throughout. No confusion possible if you use a date type for the variable you're storing the date in



Cheers
Nikki
Look, mommy, I'm flying!

 

[Mightyginger]

okay, back to the date problem I've found out some interesting stuff - and the source of the problem.

When it reads in the date intially
I type VBA shows Means
12/1/2001 12/1/2001 Dec 1, 2001
13/1/2001 1/13/2001 Jan 13, 2001
So to get round this - and make sure there can be no confusion I enter the day, month and year seperately as parameters and then use dateserial to put them togther - this mates sure there's no confusion as dateserial is always us format. Okay, so thats fine.

The problem is that when vba passes the dates between functions it passes it from us to uk format and so it's always changing. What I've done is use the format command to make sure that the format is always mm/dd/yyyy.

So, although a total pain in the arse I have the input to the main function not as a date but as all the integer parts and then put it together using date serial.


Questions about putting the variables into the sql statements still stands though matey


Neil.

 

[Mightyginger]

Didn't see you're post - thanks a million. Will check that out. At least my function now actually works properly ow though.


Neil.

 

[xlbo]

Neil - when I suggested using serial numbers, I didn't mean use date serial to put numbers together but actually to use the serial number of the date itself. You can get this by loading your text file into excel and in col B, using =VALUE(A1)
This will give you the serial numbers of the dates. I much prefer using serial numbers within code as they are not subject to the formatting wheims of M$. You would still be able to enter your date as a date - just make sure you have a line like the following:

Function GingerBumpDates(valdate As Date, num As Integer)
sDate = val(valDate)

where valDate has been entered in a valid date format

All date operations ie adding and subtracting days suddenly become much easier when you can just see your serial number incrementing to show changes in dates

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde