Exporting to File based on path in Text Box

[MCTampa]

I am trying to use the DoCmd.OutputTo function to output to a file based on the values in a text box.

Here is what I'm using:

 Do.Cmd.OutputTo acOutputQuery, "Monthly Detailed Report", Excel97-Excel2003Workbook(*.xls)", "Screen.ActiveForm!Text1216 & " \ "& Monthly Detailed Report......

The path to the file is in text box 1216 and it reads: G:\IRM\001 Javier Diaz
However I'm getting the error "Type Mismatch" and I cannot understand why.
Any help would be appreciated.

Mike

 

[Joe4]

"Screen.ActiveForm!Text1216

I don't think this is a valid text box call.

You would want this code called from a Form, and have the Text Box on that Form, then use a call like:

Me.Text1216

 

[MCTampa]

I don't think this is a valid text box call.

You would want this code called from a Form, and have the Text Box on that Form, then use a call like:

Me.Text1216

That did not work.

 

[Joe4]

Please post your entire code so we can see it (the ... doesn't tell us what else you got there, and where you put the quote marks is critical).

 

[stumac]

There is a few issues with your code if you copied and pasted it:

Do.Cmd - Should be DoCmd
Excel97-Excel2003Workbook(*.xls)" - is missing a " at the beginning
"Screen.ActiveForm!Text1216 - Has a " at the beginning

this should work:

DoCmd.OutputTo acOutputQuery, "Monthly Detailed Report", acFormatXLS, Me.Text1216 & "\Monthly Detailed Report.xls"

I prefer the TransferSpreadsheet method but either should work


Also you should give your text boxes more meaningful names, it makes adding code much more readable for future changes.

 

[MCTampa]

Here is my full code

Function Archive_Detailed()
'On Error GoTo Email_Report_Err

DoCmd.OutputTo acOutputQuery, "Monthly Detailed Report", "Excel97-Excel2003Workbook(*.xls)", "Screen.ActiveForm!text1216 & " \ "& Monthly Detailed Report as of " & MonthName(Screen.ActiveForm!Text1122, False) & " " & (Screen.ActiveForm!Text1141) & ".xls", False, "", , acExportQualityPrint
Proc____Email_Report_Exit:
    Exit Function
Proc____Email_Report_Err:
    MsgBox Error$
    Resume Proc____Email_Report_Exit
End Function

 

[Joe4]

You still have a double-quote at the very beginning that shouldn't be there if you are trying to call a variable. Understand that anything with double-quotes around it in Excel is treated as literal text. So anytime you want to return the value of a variable, it cannot be inside double-quotes, like you have, i.e.

EXAMPLE 1:

x = Me.text1216
MsgBox x

In this example, the message box would return the value of that text box.

EXAMPLE 2:

x = "Me.text1216"
MsgBox x

In this example, the message box would literally return the string "Me.text1216".

When building a string from a mixture of variables and hard-coded strings, remember to put all hard-coded string between double-quotes, but all variables need to be outside of double-quotes.
For example:

x = 1
y = 10
z = "Pick a number between " & x " and " & y
MsgBox z

What I would recommend doing is calculating your file name in a string variable, and use a MsgBox to return its value to the screen. Then you can easily check to see that you are building it correctly and it is bringing back the correct value, i.e.

Function Archive_Detailed()
'On Error GoTo Email_Report_Err

Dim fName as String
fName = Me.text1216 & "\Monthly Detailed Report as of " & MonthName(Me.Text1122, False) & " " & Me.[COLOR=#574123]Text1141[/COLOR] & ".xls"
MsgBox fName

DoCmd.OutputTo acOutputQuery, "Monthly Detailed Report", "Excel97-Excel2003Workbook(*.xls)", fName, False, "", , acExportQualityPrint
Proc____Email_Report_Exit:
    Exit Function
Proc____Email_Report_Err:
    MsgBox Error$
    Resume Proc____Email_Report_Exit
End Function

Also, where is this function located (in which module), and how/when is it being called?
Hopefully, it is being called from some Form event, specifically, the same Form that holds all these text boxes.

 

[MCTampa]

It makes more sense the way you laid it out, but I get an "Invalid use of Me keyword" error.

 

[MCTampa]

It gave me an "Invalid use of Keyword Me" error.
I removed it and for some reason it exports the file now but sends it to My Documents.

 

[Joe4]

It makes more sense the way you laid it out, but I get an "Invalid use of Me keyword" error.

Right, which is why I asked this:

Also, where is this function located (in which module), and how/when is it being called?
Hopefully, it is being called from some Form event, specifically, the same Form that holds all these text boxes.

We need to know how this code is being invoked. In order to use "Me", the Form containing those text boxes needs to be the active form at the time that the function is being called to run.
If it is not, we need to understand exactly how this is all running.