using a variable as the table name argument in DoCmd.OutputTo

[michalaw]

I'm trying to construct a macro that will allow users to export the results
of any query in a database to Excel, by either selecting the query from a
list, or typing the name of the query into an InputBox.
Is it possible to use the value of a control on a form, or the value entered
in a InputBox, as the table name argument with DoCmd.OutputTo or
DoCmd.TransferSpreadsheet?
Is it possible to get Access to populate a listbox with the names of all the
queries in the database?
If anyone can tell me how to write the code that accomplishes either of
these two goals, I'd appreciate it.

 

[Douglas J. Steele]

To be honest, I don't know whether it can be done with a macro (I never use
them), but you can certainly refer to the content of a control, or what's
returned in an InputBox, as a parameter to your OutputTo or
TransferSpreadsheet command in VBA.

If you've currently got something like:

DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, "Employee.rtf", True

you'd use the following to use whatever's in a control name MyTextBox as the
name of the file:

DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, Me.MyTextBox, True

or (safer)

If IsNull(Me.MyTextBox) = False Then
DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, Me.MyTextBox,
True
End If

To use the result of InputBox, try:

Dim strInput As String

strInput = InputBox("Where should I put the file?", "Output Location")
If Len(strInput) > 0 Then
DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, strInput, True
End If


To populate a listbox with all of the queries in your database, you can use
the following SQL as the Row Source:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((Left([Name],3))<>"~sq"))
ORDER BY MSysObjects.Name;

Of course, that will give you all queries: SELECT, INSERT INTO, UPDATE and
DELETE. You'll probably want to be more selective...

 

[Klatuu]

Although I avoid Macros, it is possible to reference a form control to get
the name of query. The syntax in the Table text box of the macro builder is:
=[Forms]![MyFormName]![MyControlName]

This was only tested with a text box. I am confident it would work with a
combo, but I doubt it would work with a multi select list box.

To be honest, I don't know whether it can be done with a macro (I never use
them), but you can certainly refer to the content of a control, or what's
returned in an InputBox, as a parameter to your OutputTo or
TransferSpreadsheet command in VBA.

If you've currently got something like:

DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, "Employee.rtf", True

you'd use the following to use whatever's in a control name MyTextBox as the
name of the file:

DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, Me.MyTextBox, True

or (safer)

If IsNull(Me.MyTextBox) = False Then
DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, Me.MyTextBox,
True
End If

To use the result of InputBox, try:

Dim strInput As String

strInput = InputBox("Where should I put the file?", "Output Location")
If Len(strInput) > 0 Then
DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, strInput, True
End If


To populate a listbox with all of the queries in your database, you can use
the following SQL as the Row Source:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((Left([Name],3))<>"~sq"))
ORDER BY MSysObjects.Name;

Of course, that will give you all queries: SELECT, INSERT INTO, UPDATE and
DELETE. You'll probably want to be more selective...



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I'm trying to construct a macro that will allow users to export the
results
of any query in a database to Excel, by either selecting the query from a
list, or typing the name of the query into an InputBox.
Is it possible to use the value of a control on a form, or the value
entered
in a InputBox, as the table name argument with DoCmd.OutputTo or
DoCmd.TransferSpreadsheet?
Is it possible to get Access to populate a listbox with the names of all
the
queries in the database?
If anyone can tell me how to write the code that accomplishes either of
these two goals, I'd appreciate it.

 

[Douglas J. Steele]

It definitely won't work with a multi-select list box. Referring to a
mult-select list box will always return Null, even if only one element in it
is selected.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Although I avoid Macros, it is possible to reference a form control to get
the name of query. The syntax in the Table text box of the macro builder
is:
=[Forms]![MyFormName]![MyControlName]

This was only tested with a text box. I am confident it would work with a
combo, but I doubt it would work with a multi select list box.

To be honest, I don't know whether it can be done with a macro (I never
use
them), but you can certainly refer to the content of a control, or what's
returned in an InputBox, as a parameter to your OutputTo or
TransferSpreadsheet command in VBA.

If you've currently got something like:

DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, "Employee.rtf",
True

you'd use the following to use whatever's in a control name MyTextBox as
the
name of the file:

DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, Me.MyTextBox,
True

or (safer)

If IsNull(Me.MyTextBox) = False Then
DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, Me.MyTextBox,
True
End If

To use the result of InputBox, try:

Dim strInput As String

strInput = InputBox("Where should I put the file?", "Output Location")
If Len(strInput) > 0 Then
DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, strInput,
True
End If


To populate a listbox with all of the queries in your database, you can
use
the following SQL as the Row Source:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((Left([Name],3))<>"~sq"))
ORDER BY MSysObjects.Name;

Of course, that will give you all queries: SELECT, INSERT INTO, UPDATE
and
DELETE. You'll probably want to be more selective...



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I'm trying to construct a macro that will allow users to export the
results
of any query in a database to Excel, by either selecting the query from
a
list, or typing the name of the query into an InputBox.
Is it possible to use the value of a control on a form, or the value
entered
in a InputBox, as the table name argument with DoCmd.OutputTo or
DoCmd.TransferSpreadsheet?
Is it possible to get Access to populate a listbox with the names of
all
the
queries in the database?
If anyone can tell me how to write the code that accomplishes either of
these two goals, I'd appreciate it.

 

[Klatuu]

I am aware of that. I did not want to make an absolute statement because
someone will come back with some bizarre way of doing it.

<A man who owns only a hammer sees all problems as nails>

It definitely won't work with a multi-select list box. Referring to a
mult-select list box will always return Null, even if only one element in it
is selected.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Although I avoid Macros, it is possible to reference a form control to get
the name of query. The syntax in the Table text box of the macro builder
is:
=[Forms]![MyFormName]![MyControlName]

This was only tested with a text box. I am confident it would work with a
combo, but I doubt it would work with a multi select list box.

To be honest, I don't know whether it can be done with a macro (I never
use
them), but you can certainly refer to the content of a control, or what's
returned in an InputBox, as a parameter to your OutputTo or
TransferSpreadsheet command in VBA.

If you've currently got something like:

DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, "Employee.rtf",
True

you'd use the following to use whatever's in a control name MyTextBox as
the
name of the file:

DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, Me.MyTextBox,
True

or (safer)

If IsNull(Me.MyTextBox) = False Then
DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, Me.MyTextBox,
True
End If

To use the result of InputBox, try:

Dim strInput As String

strInput = InputBox("Where should I put the file?", "Output Location")
If Len(strInput) > 0 Then
DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, strInput,
True
End If


To populate a listbox with all of the queries in your database, you can
use
the following SQL as the Row Source:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((Left([Name],3))<>"~sq"))
ORDER BY MSysObjects.Name;

Of course, that will give you all queries: SELECT, INSERT INTO, UPDATE
and
DELETE. You'll probably want to be more selective...



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm trying to construct a macro that will allow users to export the
results
of any query in a database to Excel, by either selecting the query from
a
list, or typing the name of the query into an InputBox.
Is it possible to use the value of a control on a form, or the value
entered
in a InputBox, as the table name argument with DoCmd.OutputTo or
DoCmd.TransferSpreadsheet?
Is it possible to get Access to populate a listbox with the names of
all
the
queries in the database?
If anyone can tell me how to write the code that accomplishes either of
these two goals, I'd appreciate it.

 

[test]

test

I am aware of that. I did not want to make an absolute statement because
someone will come back with some bizarre way of doing it.

<A man who owns only a hammer sees all problems as nails>

It definitely won't work with a multi-select list box. Referring to a
mult-select list box will always return Null, even if only one element in it
is selected.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Although I avoid Macros, it is possible to reference a form control to get
the name of query. The syntax in the Table text box of the macro builder
is:
=[Forms]![MyFormName]![MyControlName]

This was only tested with a text box. I am confident it would work with a
combo, but I doubt it would work with a multi select list box.

:

To be honest, I don't know whether it can be done with a macro (I never
use
them), but you can certainly refer to the content of a control, or what's
returned in an InputBox, as a parameter to your OutputTo or
TransferSpreadsheet command in VBA.

If you've currently got something like:

DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, "Employee.rtf",
True

you'd use the following to use whatever's in a control name MyTextBox as
the
name of the file:

DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, Me.MyTextBox,
True

or (safer)

If IsNull(Me.MyTextBox) = False Then
DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, Me.MyTextBox,
True
End If

To use the result of InputBox, try:

Dim strInput As String

strInput = InputBox("Where should I put the file?", "Output Location")
If Len(strInput) > 0 Then
DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, strInput,
True
End If


To populate a listbox with all of the queries in your database, you can
use
the following SQL as the Row Source:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((Left([Name],3))<>"~sq"))
ORDER BY MSysObjects.Name;

Of course, that will give you all queries: SELECT, INSERT INTO, UPDATE
and
DELETE. You'll probably want to be more selective...



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm trying to construct a macro that will allow users to export the
results
of any query in a database to Excel, by either selecting the query from
a
list, or typing the name of the query into an InputBox.
Is it possible to use the value of a control on a form, or the value
entered
in a InputBox, as the table name argument with DoCmd.OutputTo or
DoCmd.TransferSpreadsheet?
Is it possible to get Access to populate a listbox with the names of
all
the
queries in the database?
If anyone can tell me how to write the code that accomplishes either of
these two goals, I'd appreciate it.