[AccessD] Export to excel

Hi Mark,

When I export a spreadsheet from Excel, I include the
Date/Hour/Minute/Second in the name of the file.  Just call this function
and include the result in the file's name.

You could write a timer-based Access sub to output the Excel file every X
minutes (and delete the previous file).

'--------------------

Public Function CurrentDateTimeString() As String

    Dim stgHour As String
    Dim stgMinute As String
    Dim stgSecond As String
    Dim dteDate As Date

    dteDate = CurrentDate

    If Len(Hour(Now())) = 1 Then
        stgHour = "0" & Hour(Now())
    Else
        stgHour = Hour(Now())
    End If

    If Len(Minute(Now())) = 1 Then
        stgMinute = "0" & Minute(Now())
    Else
        stgMinute = Minute(Now())
    End If

    If Len(Second(Now())) = 1 Then
        stgSecond = "0" & Second(Now())
    Else
        stgSecond = Second(Now())
    End If

    CurrentDateTimeString = Month(dteDate) & "-" & Day(dteDate) & "-" &
Year(dteDate) & " " & stgHour & stgMinute & stgSecond

    Exit Function
    ErrEx.Bookmark = BOOKMARK_ONERROR

End Function

'--------------------

Good Luck!
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Simms
Sent: Wednesday, November 09, 2011 2:27 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Export to excel

The huge problem with any export to Excel is data timeliness....i.e. How
"fresh" is the data ?
Any time someone opens up the Excel workbook, the question becomes "when was
this worksheet last updated from Access ?"
An OLEDB Connection to any Access table or query can be generated statically
within Excel or dynamically from Access using Excel automation. This
guarantees data freshness and synchronization.

The kicker is: not all Access Select queries will be addressable from
Excel...i.e. when they contain "non-standard" SQL functions like "IIF".
(This is another unforgiveable Microsoft "oversight" IMHO - there should
have been an exception for Access queries !).
In that case, you must go thru a "workaround" in creating a table on-the-fly
from the query definition....and then perform an insert from the query
result set. Of course the OLEDB connection property is always set to
reference the table, not the query. On top of that, you must run that Insert
query in the Excel query table BeforeQuery event ..this time with Access
automation. Again, that only has to be done if the Select query is complex
and not directly addressable via OLEDB.

IMHO, this approach really beats all of the others....despite the huge
hassle that Microsoft has imposed for complex queries Every time you open
the Excel workbook....boom...the latest data is just "there". Better yet: it
can be refreshed every 30/60/whatever minutes....even when users are
changing the data.


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com