Excel via Access help

[Guest]

I am using code from post of 11/4/2004 Manipulating Excel via Access by Ken
Snell.
I need to be able to import the data into tbltapes that is has relationship
to tblTracker. When the record is being created the date and requestorname is
being entered. The requestor form has a button that user should clickto pull
the correct data only from the spreadsheet file and ensure that it matches
the correct record id. The information should populate the list box that is
on the form. The user will name the Excel filename the same as the
requestorname along with the date ex: Cindy Grover 041105.xls. How do I code
this to ensure that the right data gets with the correct record? Do I really
need spaces within the Excel filename? All the data should be listed in the
list box before the form is closed.

 

[Graham Mandeno]

Hi Cindy

I understand you just want to make a filename out of the contents of two
fields on your form.

If Me.RequestorName contains the string "Cindy Grover" and Me.RequestDate
contains the date #11-apr-2005#, then:
strFilename = Me.RequestorName & Format(Me.RequestDate, " mmddyy") _
& ".xls"
will give you the string: "Cindy Grover 041105.xls"

The spaces are certainly not necessary, but will do no harm. Use the
Replace function to remove them from the RequestorName:
strFilename = Replace(Me.RequestorName, " ", "") _
& Format(Me.RequestDate, "mmddyy") & ".xls"
will give you: "CindyGrover041105.xls"

You might also like to consider using "yymmdd" instead of "mmddyy" to format
the date, so that the files sort in date order.