VBA Query and Output To

[brorick]

Hello. I have a macro I would like to replace with VBA. My macro currently runs a few queries and exports the results to a text file. Then it runs the Excel Application using RunApp. The biggest frustration I have with this method is that there are several popup windows where the user has to select yes multiple times. I would rather have the VBA run in the background and perform everthing without the user being prompt for any interaction. Any thoughts?

For the purposes of this example we can refer to the queries as QueryA, QueryB and QueryC. The text document can be named mytext.txt. Any help is greatly appreciated. Thank you. :(

[matthewspatrick]

It might be helpful if you actually posted the code...

[brorick]

I am looking for the VBA code. I am currently using a Macro. I am not sure I understand your reply.

[matthewspatrick]

My apologies; I had not quite read it through.

You could try something like:


Sub test()

Dim xlApp As Object
Dim xlWb As Object

With DoCmd
.SetWarnings False
.OpenQuery "QueryA"
.OpenQuery "QueryB"
.OpenQuery "QueryC"
' use TransferSpreadsheet or TransferText for the export
.SetWarnings True
End With

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("path to file")
xlApp.Visible = True

Set xlWb = Nothing
Set xlApp = Nothing

End Sub




Question: if you want to open the export file in Excel, why export it as a text file?

[brorick]

Thank you matthewspatrick. I will give it a try.


Question: if you want to open the export file in Excel, why export it as a text file?

The Excel template that will open has built in VBA that will read the txt file and automatically clean up the data.

Thanks again for your assistance. :friends:

[brorick]

This is exactly what I was looking for and it worked like a charm. Thank you matthewspatrick. :clap:

[matthewspatrick]

You're welcome :)