Output to Excel Question

[BWG3]

I'm using the "Output To" command in the macro design. Is there any way to have the command output to Excel without having to save it somewhere on your computer first? I just want to click the button to run the macro and to have it just open up the report in Excel. Thanks in advance.

 

[boblarson]

Nope, not using that method. Using code you can do it.

 

[BWG3]

Is there code available? I'm not the greatest VB programmer at the moment.

 

[boblarson]

On my website I have code for sending tables, queries, and recordsets of forms to Excel and all use the method which doesn't include automatically saving. Here's one of them.

 

[BWG3]

Thank you for your help.

 

[BWG3]

I'm getting a compile error : Invalid outside procedure when I run the module. Am I doing something wrong? Does something need to be open, as well?

 

[boblarson]

I'm getting a compile error : Invalid outside procedure when I run the module. Am I doing something wrong? Does something need to be open, as well?

1. Did you make sure to put the code I gave into a STANDARD MODULE (not in a form or report module)?

2. what line does it stop at if you run DEBUG > COMPILE?

 

[BWG3]

To create the module, I have been clicking on the modules tap and selecting NEW. Or is not incorrect.

It compiles fine, but now when I try to run the code I get "there was an error executing the command.

 

[boblarson]

To create the module, I have been clicking on the modules tap and selecting NEW. Or is not incorrect.

It compiles fine, but now when I try to run the code I get "there was an error executing the command.

From your description you have been doing it correctly. So, how about a post to show the code which you are using to call that function?

 

[BWG3]

I name the module "Excel"
"Search_Excel_1" is my table
"Excel_1" is the name of the sheet (not sure what this actually means though)

Then I went to switchboard manager and selected command: Run Code and Function Name: SendTQ2Excel.

This is what is in my module and table "Search_Excel_1" has data in it. When i click switchboard button, I get the error.

Option Compare Database
Public Function SendTQ2Excel(strTQName As String, Optional strSheetName As String)
' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to name it to

Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As Field
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
On Error GoTo err_handler
Set rst = CurrentDb.OpenRecordset(Search_Excel_1)
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Sheet1")
If Len(Excel_1) > 0 Then
xlWSh.Name = Left(Excel_1, 34)
End If
xlWSh.Range("A1").Select

For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
rst.MoveFirst
xlWSh.Range("A2").CopyFromRecordset rst
xlWSh.Range("1:1").Select
' This is included to show some of what you can do about formatting. You can comment out or delete
' any of this that you don't want to use in your own export.
With ApXL.Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select
rst.Close
Set rst = Nothing
Exit Function
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function
End Function

 

[boblarson]

Okay, first lesson here is that the code I provided is generic so you can use it for almost anything. You do NOT modify it (except the parts which deal with formatting). You SEND the object to it.

So, change this:
Set rst = CurrentDb.OpenRecordset(Search_Excel_1)

back to the way it was:

Set rst = CurrentDb.OpenRecordset(strTQName)


And then you CALL it passing the name of the query you want to use:

Call Send2Excel("Search_Excel_1")

or if using a macro you put
Search_Excel_1
in the Arguments

 

[BWG3]

I'm not sure what you mean by Call. Where do I call the function? When the switchboard manager asks me for the function name, I put Send2Excel("Search_Excel_1"). I'm still getting the error.

If I want to run the module in a Macro, what action do I use?

I'm sorry this is my last try. I won't keep bugging you with this.

 

[boblarson]

I'm not sure what you mean by Call. Where do I call the function? When the switchboard manager asks me for the function name, I put Send2Excel("Search_Excel_1"). I'm still getting the error.

If I want to run the module in a Macro, what action do I use?

So what is Search_Excel_1? Is it a form or a query?

I'm sorry this is my last try. I won't keep bugging you with this.

don't worry about it. We'll keep working it until we get it. No need to give up too easily.

If necessary, can you post a copy of your database with bogus data substituted for any real data?

 

[BWG3]

I have an append query that dumps data into a table. The table is called search "Search_Excel_1" and the query is called "Search_Excel".

I set up a macro to first run the query, then I want to run the module code that you gave me take the data in table "Search_Excel_1" and just open excel with the data in it. Then I just want to run a delete query to delete the data in the table for next use.

I think my database is too big to attached. It's 4MB

 

[boblarson]

I think my database is too big to attached. It's 4MB

What you would need to do is to run COMPACT AND REPAIR and then ZIP the file (you can zip by right clicking on the file and selecting SEND TO > COMPRESSED FOLDER). If it is still over 2 MB then you can delete the records from it (make sure to compact and repair again) and just fill in a couple of records.

If you wish, I have a free Database Reset tool which will delete all records from your tables and then compacts it so you can then zip it.

 

[BWG3]

I did do that and it took it down from 5mb to 4. I only have 7 records in the data so far and for some reason I cannot open the link you sent me. I'll have to look in to why it won't open.

If you have an external email account, I can send it to you.

 

[BWG3]

I have attached the zip file of the database. When you open the database, the switchboard should appear. See to exactly what I'm trying to do, just click the "reports" button, then click the "more" button, and then click the "search in excel" button. You will then be prompted to save the file somewhere on your computer. I just want to eliminate all this and just have the excel sheet open up when someone clicks the "search in excel" button. If you go to the macros tab and look in "search_excel_output", you'll see exactly what is happening when you click the "search in excel" button. I have your module code in the database, but it is currently not being used anywhere. Some hopefully you can help me out in where I'm gonig wrong. Thanks for your help.