?
Solved

Search for Date/Time in Excel using Access 2007 VBA Project

Posted on 2011-10-15
31
Medium Priority
?
369 Views
Last Modified: 2012-05-12
Hi Experts - I am using the code provided in the solution to the question below to search for the current date and time in PDF documents.  Basically if today's date is found, store the associated time in a variable to be used in the parent code.

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_27380558.html

  I need similar code to search through an Excel spreadsheet for today's date and a time and store the time in 24 hour format in a variable labled "xlsTime".  The date and time can be in the same cell or separate cells.

Ultimately, the goal of this function is to compare the date/time in a spreadsheet to the date and time stored in other variables (RSdate & RStime) and return False if the date/time in the spreadsheet are less than RSdate & RStime.

I have attached a few example spreadsheets.  In the 322_BoA spreadsheet, I highlighted the date/time; they are in the number format instead of a date & time format. 322-BoACorr3-10140750-.xls clrates.xls 295-NYCB1-10140830-.xls
0
Comment
Question by:rsburge
  • 17
  • 14
31 Comments
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 36975106
OK....I am with you on this one too rsburge....

Stanbd by...

Cheers
Chris
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 36975112
Is this code run from VBA code in Access/Powerpoint/Word/Excel?

If it is in Excel then the coding can be written specifically in Excel VBA, but my thoughts are that if it is run elsewhere, we could convert the Excel spreadsheet to a "temporary" PDF and then use the already existing code I wrote for you in the solution on PDFs to extract the date.

The conversion to PDF is very simple and I would think the conversion to PDF and then seek is a simpler way to go and easier to builf in to your existing environment.

Please advise.

Cheers
Chris
0
 

Author Comment

by:rsburge
ID: 36975808
This could certainly work if it doesn't increase the amount of time it takes to get the date/time by more than a few seconds.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:rsburge
ID: 36975835
Thanks for looking at my questions!

Just a little more information...  The reason I say from Access 2007 is that my project an Access database that we use to download PDFs and Excel spreadsheets from the web and then we do a little manipulation to them and then we push the data from those documents into our database.

I am wondering if the workbook having multiple tabs will make a difference in your proposed solution.  The date/time is not always on the first tab.  Attached here are a few examples of multi-tab workbooks.  The other issue could be that some of the workbooks have so much data that they can be over 3mb in size.

The file "Fifth-Third" is probably the strangest because of how the time is displayed on the second tab in the upper right of the spreadsheet.

The file "Plaza" is only one tab, but the date/time is in a merged cell and I'm not sure if that will be an issue with your proposed solution or not.

The file "REMN" is only one tab, but it is a protected file and I'm not sure if that will be an issue with your proposed solution or not.

I think this covers all of the quirks I know of...  :)

 MTCorr-10140829.xls Correspondent-Rates-101411.xlsx Plaza-IE-10140911-.xls REMN.xls
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 36977308
OK Thanks.

I will test these files today against the code I have developed.

Cheers
Chris
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 36983607
Things are looking promising with me just tweaking the existing code you are using for PDFs to also process Spreadsheets!

Just a question.

Is there ever a danger that a PDF file you are using could also have the same name as an Excel file (apart from the extension)?

e.g. Could there ever be an existing PDF called PEMN.pdf? (Notice you have a file named PEMN.xls).
0
 

Author Comment

by:rsburge
ID: 36983674
Hi.  Yes, there are Excel files with the same name as PDF files with the exception of the file extension.

As a side note...  I posted another question that is related to the PDFToText code in the last question.  I think it is a simple question, but wanted to make sure to give you the points for it if you have time to look at it.  It is just adding 2 more time formats to the FindTime code.
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 36984821
OK...I will look at the new question.

Thanks for the info...I will have to use "temp" files then, since we do not want to create PDFs if they already exist (that is for the "non" pdfs of course).

Cheers
Chris
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 36985268
Well, the conversion to PDF worked (sort of) but was very messay, had screen interaction and was far too slow.

So I have opted for another method (much easier too) of searching for "patterns" in spreadsheets. The code will look familiar to the code we have already used with PDFs, but it does not shell to the PDFConverter (of course)_ and works totally internally within Excel object (so is totally hidden to the user and is very fast).

It may take a while so please be patient. The code is quite complicated.

I am working on it as fast as possible.

Cheers
Chris
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 36985299
What date are you searching for in the Excel files? (For the PDFs we used 10/4/2011)

I know we will be searching for "date()" eventually, bt this is for testing purposes only.

Cheers
Chris

0
 

Author Comment

by:rsburge
ID: 36986128
I have been manually checking the time on these for the last 2 years, so I think I can wait a bit longer to get it automated.  :)

The date on the files I attached earlier is 10/14.  We can use that date for testing.

I do have a piece of code that I use to convert excel sheets to pdf...  It is super basic but it might help in your solution...

 '--------------------------------PRINT TO PDF-------------------------------------------'
    xlws.ExportAsFixedFormat Type:=xlTypePDF, FileName:=filePath2
 '------------------------------------------------------------------------------------------'

This of course assumes that the excel is open and I have already set the following...

Set xlApp = CreateObject("excel.application")
Set xlwb = xlApp.Workbooks.Open(filePath)
    xlApp.DisplayAlerts = False
    xlApp.Visible = False
     Set xlws = xlwb.ActiveSheet
    filePath2 = SDdrive & ":\aLXE-Pricing\AFRwholesale\AFRwholesale.pdf"
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 36990260
I succeeded in converting to PDF, but as I said, even if I use your sample code (which would work) I still think that using Excels own "Search" functionality might be a better way to go.

That being said, if I find that I cannot satisfactorily get the Excel "Search" to work correctly, I will keep that bit of code in mind and see whether converting to PDF first is still OK speed wise using that code.

Stand By... (I am working on five answers at the moment and seem to be sitting at my PC all day! :-) (Good thing I am retired now).

Cheers
Chris
0
 

Author Comment

by:rsburge
ID: 36990304
:)  No worries on time.  I completely understand you have other things to do.  :)  I just really appreciate all of your help!
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 36994342
OK...I have finally made it all work after trying different approaches.

Conversion to PDF of the Excel files was a bad idea, using either the code I already had (too slow) or the suggestion you made (a bit faster, but still slow and siplayed a message saying "Processing" every time a PDF was created)
I could not suppress that message either.

So I have based my code SLIGHTLY on the way the PDF code was written, but I use an Excel object to search through each worksheet within each workbook file for the date (which it finds).

Sounds OK, but I have striuck a problem with the file
"Correspondent-Rates-101411.xlsx" because the time is actually mentioned in a cell BEFORE the date is quoted.

It actually says  "Effective @8:30 AM EST of:            14/10/2011"
The code normally searches from the cell in which the time is found
(in case the time is also written in that cell) and then moves one cell at a time to the right until it reaches the end column, then goes to the next row and keeps searching.

Needless to say in the file I mentioned, It reaches column numbers in the THOUSANDS on each page and takes a long time.

So I added a procedure to also search BACKWARDS if the time is not found on either the current row (where the date is) to the right of that date or on the next row. After going back to the opriginal row I then searching backwards
looking for the time pattern. The only problem we could have is if a time exists to the right of another date showing on the sheet.

   e.g.
 
              "Next Issue:   10/21/2011  12:00AM                    Printed: 10/14/2011"

Is that ever likley to happen?  

I will post the code as soon as I have this final bit fixed up.

It is now 4am and I have to get up at 8am, so I better get off to bed. (I'm going to kill myslef at this rate) :-)

Will post code ASAP

Cheers
Chris
0
 

Author Comment

by:rsburge
ID: 36995350
Hi.  I am sorry this is such a pain...  Yes, it is possible to encounter the scenario you are concerned with.
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 36996375
Well, will there ever be a time where there is NO time showing with the date?

If that IS the case, what would I do with the scenario above?

Would I report the time as 12:00 (which could be incorrect) or (if I find a date just before the time, as per my example:

              "Next Issue:   10/21/2011  12:00AM                    Printed: 10/14/2011"

would I assume (if there is no time on the next line (just under the "Printed" date) that no time was indicated? I think I will have to do that, otherwise there is no way we can succesfuly catch the data.


The following scenario though would be inaccurate:
    "Next Issue:   10/21/2011                       Printed at 12:00AM on 10/14/2011"

If your reports ALWAYS come in the same format, we could add a paramater to tell the computer which way the date and time is shown in that file
such as using what I call a "format indicator"       SLA = Same Line after
                                                                              NLU = Next line under
                                                                              SLB = Same line before
                                                                              NTS = No time shown

but in this case the NAME of the file must never change,a and any file name not recorded would test for the first two and (if not found) assume it is an "NTS".
I must warn that this method of attack would not be any good if the names of the files ever change (in which case default would come into action unless you recorded a "Format Indicator" for that report (we could have a text file which simply give the name of the various reports and the indicatorS for files which
are SLB:

           e.g. a file called "FormatIndicators.txt"
   
                              "SLB: Correspondent-Rates-101411.xlsx"

Also this plan would fail if the report name stayed the same, but the layout changed from "SLB" to another format (unless you noticed it).

The SIMPLEST way, of course, is just to live with some incorrect times in
such situations as my example where no time is showing for the date 10/14/2011 and the code we are using would report it as 12:00 in the following:

         "Next Issue:   10/21/2011  12:00AM                    Printed: 10/14/2011"

(I suppose it really boils down to how often the files change names or formats.
If rarely, then the use of  the "FormatIndicator" code might be the best option.

What plan do you recommend?

Cheers
Chris

                       


0
 

Author Comment

by:rsburge
ID: 36996684
Hi - I think we can go with your idea from your post earlier today.

If it is still taking a long time to search through the columns, the search can be limited to "A1:GA300".  The date/time will always be within those rows.

For those instances where no time is found, can the time be set at the system time rather than 12:00?

Thanks again for all of your help!
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 36999152
No problems!

I gave several plans of attack in comment 36996375, which one do you prefer?

Cheers
Chris
0
 

Author Comment

by:rsburge
ID: 37002002
I'm sorry I should have been more clear.

The formats in the Excel sheets changes regularly and without notice.  I was thinking the below would work with the possible changes/additions mentioned above.

<<<So I have based my code SLIGHTLY on the way the PDF code was written, but I use an Excel object to search through each worksheet within each workbook file for the date (which it finds).

Sounds OK, but I have striuck a problem with the file
"Correspondent-Rates-101411.xlsx" because the time is actually mentioned in a cell BEFORE the date is quoted.

It actually says  "Effective @8:30 AM EST of:            14/10/2011"
The code normally searches from the cell in which the time is found
(in case the time is also written in that cell) and then moves one cell at a time to the right until it reaches the end column, then goes to the next row and keeps searching.

Needless to say in the file I mentioned, It reaches column numbers in the THOUSANDS on each page and takes a long time.

So I added a procedure to also search BACKWARDS if the time is not found on either the current row (where the date is) to the right of that date or on the next row. After going back to the opriginal row I then searching backwards
looking for the time pattern. The only problem we could have is if a time exists to the right of another date showing on the sheet.>>>

These are the possible changes/additions I was thinking about...  Of course if you have a better idea, that is great too.

If it is still taking a long time to search through the columns, the search can be limited to "A1:GA300".  The date/time will always be within those rows.

For those instances where no time is found, can the time be set at the system time rather than 12:00?

It is possible that another date/time could be to the right of the time found as mentioned in your comments above, but it is unlikely.  It is much more likely in a PDF.

I hope this helps.


0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37004192
OK...Thx for that...working on it now...

Cheers
Chris
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37011347
OK...the code has changed a bit, as detailed in your other question about adding time formats to the existing code (Question 27400993).

The big question is: Why is the date in "Correspondent" file showing as
"14./10/2011" which is the format we use in Ausatralia, but not that covered by any of your date "patterns"?

Will you have dates showing in "dd/mm/yy" and "dd/mm/yyyy" format as well?
If so, we should expland your patterns to cover them. Of course, in some cases
"dd/mm/yy" and "dd/mm/yyyy" might be the same as "mm/dd/yy" and "mm/dd/yyyy" (e.g. 01/02/2011 could be either Feb 1  ( dd/mm/yyyy) OR
                                                                                  Jan 2   (mm/dd/yyyy)
It isn't simple, is it?  :-)

Having said that, I notice the date on page 1 of the  file
"Correspondent-Rates-101411.xlsx"is actually entered as "Now()"
so it will ALWAYS be the current date on which you are running the program!

Is that actually what you want? It does not make any sense to me, since you could have an OLD file but it will still have today's date!

I will still want the first question answered, though, in case you have files that come in "non-american" date format.

Please advise.

Cheers
Chris

0
 

Author Comment

by:rsburge
ID: 37012173
Let me look at all of the files I currently have in my system and get back to you.  I may need to do a small manipulation before running the find date/time function...  Please stand by...
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37181489
OK Renee,

How are things going with this one?

Cheers
Chris
0
 

Author Comment

by:rsburge
ID: 37223732
I am so sorry I just got complacent about this one.  I forgot I needed to close it out.  Everything is working well with the exception of one thing...  And maybe there is nothing we can do to get around it.

This is with regards to PDF's and not the Excel piece.

If I download a file that is not actually in a "PDF" format, meaning maybe the file wasn't ready to download yet, so when you try to open it Acrobat returns an error that says this is not the proper format.

Is there any kind of "check" or something I can add in the code that will exit the code if the file I try to search throws an error?

If this is not a simple answer, then don't worry about it.  I can always open a new question.  :)
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37226276
I am starting to lose track of things here.

Have I given the code to you to almost close this one out? (You say everything is working well, but I can't see in this question flow above where I supplied the code to you! (Unless you are using the code supplied in the last question I answered for you as referenced at the top of this question flow).

In answer to your questions about the "invalid format" (not completed PDF perhaps) once I work out the bit of code you have on hand which attempts to open the PDF, then it will return a code (or should) which will indicate success or not.
Please let me know what bit of code you are talking about here.

It shows how easy it is to lose track of where you are at if you leave a question for too long!

I await your response.

(P.S. I will be away for 4 weeks from about Dec. 23rd when I go to visit Mum and look after her over Christmas - she is 83)

Cheers
Chris
0
 

Author Comment

by:rsburge
ID: 37239033
Hello - I am using the code you supplied in the other question...

I am attaching the code where it gets "stuck" if the pdf file is "bad".

This is the line where it gets stuck in the Loop...
      Do While Not CKRFileExists(cTextFile) 'allow time for file to become available
        DoEvents
      Loop
 
Public Function PDFFindDateTime(cPDFFile As String, _
                                dDate As Date, _
                                cPDFToText As String, _
                                bAMPM As Boolean) As String
  Dim sText(13) As String
  Dim x         As Integer
  Dim cLine     As String
  Dim nHandle   As Integer
  Dim bFound    As Boolean
  Dim cRun      As String
  Dim nRet      As Long
  Dim strTime   As String
  Dim cTextFile As String

  If Dir(cPDFFile) <> "" Then
    sText(1) = Format(dDate, "mmmm d, yyyy")
    sText(2) = Format(dDate, "mmm-d-yyyy")
    sText(3) = Format(dDate, "mm/dd/yy")
   sText(4) = Format(dDate, "mm/dd/yyyy")
    sText(5) = Format(dDate, "m/d/yyyy")
    sText(6) = Format(dDate, "m/d/yy")
    sText(7) = Format(dDate, "mm_dd_yyyy")
    sText(8) = Format(dDate, "dddd, mmmm dd, yyyy")
    sText(9) = Format(dDate, "mmm-dd-yy")
    sText(10) = Format(dDate, "mmmm dd, yyyy")
    sText(11) = Format(dDate, "mmm dd, yyyy")
    sText(12) = Format(dDate, "mmm d, yyyy")
    sText(13) = Format(dDate, "d-mmm-yy")
    
    cTextFile = "D:\PDF2Text.txt"
'    cTextFile = CurDir() + "\PDF2Text.txt"
    
    If CKRFileExists(cTextFile) Then
      Kill cTextFile
    End If
    
    cRun = Chr(34) + cPDFToText + Chr(34) + " -layout " + _
           Chr(34) + cPDFFile + Chr(34) + " " + Chr(34) + _
           cTextFile + Chr(34)
    nRet = Shell(cRun, vbMinimizedNoFocus)

    If nRet > 0 Then   'The program ran successfully
    
      'don't continue until the PDFTText.exe has finished
      Do While CKRIsRunning("PDFTOTEXT")
        DoEvents
      Loop
    
      Do While Not CKRFileExists(cTextFile) 'allow time for file to become available
        DoEvents
      Loop

      For x = 1 To UBound(sText)
        nHandle = FreeFile
        Open cTextFile For Input As #nHandle

        Do While Not EOF(nHandle)
          Line Input #nHandle, cLine
          If InStr(cLine, sText(x)) > 0 Then
            PDFFindDateTime = cLine
            bFound = True
            strTime = FindTimeInPDF(nHandle, cLine, bAMPM)
            PDFdate = "Current"
            Exit Do
         Else
            PDFdate = "Not Current"
          End If
        Loop
        
        Close #nHandle
        
        If bFound Then
          Exit For
        End If
      
      Next
    Else
      MsgBox "Error: PDFToText.exe could not run!"
      End

    End If

  End If

  PDFFindDateTime = strTime
'  Debug.Print PDFFindDateTime
  'PDFtime = PDFFindDateTime
  Debug.Print PDFdate
  Debug.Print strTime
End Function

Open in new window

0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37240341
Renee,

Can you attach a file that has become "bad", please?

That way I can track down how to handle this situation.

Many thanks

Chris
0
 

Author Comment

by:rsburge
ID: 37243814
sure...  I will get one and upload it today.  Thanks!!
0
 

Author Comment

by:rsburge
ID: 37244436
0
 
LVL 13

Accepted Solution

by:
Chris Raisin earned 2000 total points
ID: 37247463
It is a matter of ascertaining whether the program "PDFToText.exe" successfully created a text file or not. If it fails, it does not produce the output file
(designated in our code as ""D:\PDF2Text.text") but it DOES return a number each time (which varies, even if using the same input file!)

There is no documentation supplied with the utility "PDFToText.exe" so the number it returns does not mean much to us (perhaps it indicates the number of characters processed before the program completed/crashed).

So the best option for us is to simply place a counter in the loop where we are waiting for the file to be created. We will get it to "bomb out" after (say) 1000 loops (as showing in the amended code segment below).

Substitute the below code for the current code:

     Do While Not CKRFileExists(cTextFile) 'allow time for file to become available
        DoEvents
      Loop


Cheers
Chris
Dim nAttempts as integer 
  'place the above declaration with all your other "dim" statements
  nAttempts = 0
  
  'allow time for file to become available
  Do While Not CKRFileExists(cTextFile) 
    nAttempts = nAttempts+1 
    DoEvents
    if nAttempts = 1000 then
      'display an error message or do something else  
      '(perhaps just exit function and do nothing!)   
      MsgBox "Unable to process" + vbCrLf + cPDFFile
      Exit Function
    endif 
  Loop

Open in new window

0
 

Author Comment

by:rsburge
ID: 37250108
Thank you for all of your help!  That works perfectly!

Have a great day!
Renee
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Starting up a Project

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question