Variable from Exported Excel File

  • Thread starter Thread starter LJG
  • Start date Start date
L

LJG

Hi Guys,

How can I get a variable from an exported excel report, as I need it to help
with the naming of the file?

TIA
 
OK here we go

I have a button on a form that creates and saves an exported excel file. To
identify who the file needs to be sent to I need to include information that
is contained in the file to use for this purpose and as the file does not
generate any variables I cannot get the information I require from the file,
this is my code:

Private Sub cmdAppoint_email_Click()
Dim strMamName As String

Select Case MsgBox("Please attach to Outlook diary date and send to account
manager" _
, vbOKCancel Or vbInformation Or vbDefaultButton1, "New
Appointment email Attachment")

Case vbOK

End Select
DoCmd.OutputTo acOutputQuery, "qryAppointment_email", acFormatXLS,
"C:\database\dataexport\Appointments\strMamName" & "_" & UsrName & ".xls"
Call MsgBox("Your Appointment has exported to:" _
& vbCrLf & "" _
& vbCrLf & "C:\database\appointments\strMamName" _
& vbCrLf & "" _
& vbCrLf & "Please attached to email and send to Account
Manager" _
, vbInformation, "Account Manager Appointment Export")

End Sub

For strMamName I want to use this as a variable to get the name required

Hope that is clearer

TIA
Les
 
It's not clear to me exactly how you want to use strMamName, but it appears
that you want to use its value in the string that defines the filename being
created, is that correct?

This is very doable. We just need you to tell us how to identify the value
that you want to use from the query as strMamName. Is it the name of a field
in the query? Is it the data value in one of the fields in the query?
 
Hi Ken,

Yes, strMamName will define the filename being created and it will be a data
value form the query.

Thanks
Les
 
So the query "qryAppointment_email" will contain just one record? Try this
modified code (from what you posted); note that I have not "cleaned" up your
code, just added a step and then I fixed the OutputTo and Call MsgBox lines
to use the variable:


Private Sub cmdAppoint_email_Click()
Dim strMamName As String

Select Case MsgBox("Please attach to Outlook diary date and send to account
manager" _
, vbOKCancel Or vbInformation Or vbDefaultButton1, "New
Appointment email Attachment")

Case vbOK

End Select
strMamName = DLookup("mamName", "qryAppointment_email")
DoCmd.OutputTo acOutputQuery, "qryAppointment_email", acFormatXLS,
"C:\database\dataexport\Appointments\" & strMamName & "_" & UsrName & ".xls"
Call MsgBox("Your Appointment has exported to:" _
& vbCrLf & "" _
& vbCrLf & "C:\database\appointments\" & strMamName _
& vbCrLf & "" _
& vbCrLf & "Please attached to email and send to Account
Manager" _
, vbInformation, "Account Manager Appointment Export")

End Sub
 
Hi Ken,

I have tried that suggestion but keep getting error message at line:

strMamName = DLookup("MamName", "qryAppointment_email")

the error is:

Run-time error '2001':

You canceled the previous operation.

yet the statement above reads:

Select Case MsgBox("Please attach to Outlook diary date and send to account
manager" _
, vbOKCancel Or vbInformation Or vbDefaultButton1, "New
Appointment email Attachment")

Case vbOK

End Select

Any suggestions, ? I have tried simply commenting this out with no luck

Thanks
Les
 
That misleading error message can arise if you've misspelled something in
your DLookup statement. Make sure that the field and table names are typed
correctly.
 
Back
Top