Exporting Query Results to Existing Excel Workbook (1 Viewer)

Dnphm

Registered User.
Local time
Today, 03:13
Joined
Jul 13, 2010
Messages
11
Hey,

Here is my situation. I have queries that displays email addresses of our employees in each department. Due to employees leaving from time to time, these queries are updated regularly to accomodate for those changes. I also store these email records in a large Email Contact workbook in Excel with each department having its own worksheet tab. When data is updated in access, I want it updated in the excel workbook as well so I can send it off to certain people. My first worksheet in my excel workbook is where I make Counts of all of the records in each department (tab). When updated, the number will change according to the number of records in each tab.

*Each tab's title is identical to my query's name. So I want it to overwrite.

What I have been doing is creating a macro using the TransferSpreadsheet action and exporting the queries to individual Excel spreadsheets in a directory. Then, I would import those individual spreadsheets into the large workbook and making changes that way. I was hoping there is a more efficient way of doing this.

So I have been trying to work this out and I have started to get close to where I want but I am having trouble with something. I have gone away from using macros so I created several modules for each export query. Here is the code:

Code:
Public Sub ExportQuery1ToExcel()
Const FILE_PATH As String = "[B]C:\Directory\[/B]"
Dim FullPath As String
    strFullPath = FILE_PATH
    DoCmd.TransferSpreadsheet acExport, , "[B]QUERY_NAME[/B]", strFullPath & "[B]EXCEL WORKBOOK[/B]", False
    MsgBox ("Export is complete.")
End Sub

I would use this same code for each query export, changing the query name in the code.

The problem I am encountering is that rather than overwriting the worksheets in the excel workbook, it would just create a new worksheet tab with the title, "QUERY_NAME_1".

So, it exports the queries to the existing excel workbook but it won't overwrite the same worksheets. Any ideas?

Thanks, much appreciated
 

JamesMcS

Keyboard-Chair Interface
Local time
Today, 10:13
Joined
Sep 7, 2009
Messages
1,819
Yeah annoyingly you can't specify a range (as you can when importing). Have you considered just creating a new spreadsheet every time? Good for backup purposes then too.... Or you could use Kill to make it delete the old spreadsheet, and then rename it as you wish.
 

JamesMcS

Keyboard-Chair Interface
Local time
Today, 10:13
Joined
Sep 7, 2009
Messages
1,819
Oh, and welcome to the forum :)
 

boblarson

Smeghead
Local time
Today, 03:13
Joined
Jan 12, 2001
Messages
32,059
You can use this code from my website to put it into an existing worksheet. You might need some adjustments depending on the way the worksheet is set up.
 

boblarson

Smeghead
Local time
Today, 03:13
Joined
Jan 12, 2001
Messages
32,059
If I were to create a command button, how can I run this code?

Thanks

First you copy the code I have into a new STANDARD MODULE.

Then you can just call it in the click event of the button by passing the query name, the sheet name and the path to the database. If you are going to want to do multiple queries to multiple sheets you should add code into the code I have at the end to save the worksheet and close it so you can move on to the next by calling it again with a new query name and new sheet name.

The code would be to call it:
Code:
Call SendTQ2XLWbSheet("MyQueryNameHere", "SheetNameHere", "PathAndFileNameHere")
 

Dnphm

Registered User.
Local time
Today, 03:13
Joined
Jul 13, 2010
Messages
11
First you copy the code I have into a new STANDARD MODULE.

Then you can just call it in the click event of the button by passing the query name, the sheet name and the path to the database. If you are going to want to do multiple queries to multiple sheets you should add code into the code I have at the end to save the worksheet and close it so you can move on to the next by calling it again with a new query name and new sheet name.

The code would be to call it:
Code:
Call SendTQ2XLWbSheet("MyQueryNameHere", "SheetNameHere", "PathAndFileNameHere")

Okay, it seems to be going through the process but I get an error message that states, "Select method of Range class failed."

The range of data on the Excel worksheet that I want updated is A2:A348.
 

boblarson

Smeghead
Local time
Today, 03:13
Joined
Jan 12, 2001
Messages
32,059
Okay, it seems to be going through the process but I get an error message that states, "Select method of Range class failed."

The range of data on the Excel worksheet that I want updated is A2:A348.

Do you get a message where you can click DEBUG? If so, do it so we can see which line it is that is having an issue.
 

Dnphm

Registered User.
Local time
Today, 03:13
Joined
Jul 13, 2010
Messages
11
There is no Debug. It runs the code, opening up the excel workbook but the worksheet isn't updated.

The error message appears in Access.
 

boblarson

Smeghead
Local time
Today, 03:13
Joined
Jan 12, 2001
Messages
32,059
There is no Debug. It runs the code, opening up the excel workbook but the worksheet isn't updated.

The error message appears in Access.

In the code you downloaded from my website, comment out the

On Error GoTo Err_Handler

by sticking a single quote at the beginning. Like this:

' On Error GoTo Err_Handler

Then you should get the debug message.
 

Dnphm

Registered User.
Local time
Today, 03:13
Joined
Jul 13, 2010
Messages
11
This is the line:

Code:
strPath = strFilePath
    Set rst = CurrentDb.OpenRecordset(strTQName)
    Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Open(strPath)
    ApXL.Visible = True
    Set xlWSh = xlWBk.Worksheets(strSheetName)
    [B][COLOR=red]xlWSh.Range("A1").Select
[/COLOR][/B]    For Each fld In rst.Fields
        ApXL.ActiveCell = fld.Name
        ApXL.ActiveCell.Offset(0, 1).Select
    Next
 

boblarson

Smeghead
Local time
Today, 03:13
Joined
Jan 12, 2001
Messages
32,059
Not sure why that would cause a problem.

Also, are you going to want to leave the current column headers? If so, we should remove this part anyway:

Code:
    xlWSh.Range("A1").Select
    For Each fld In rst.Fields
        ApXL.ActiveCell = fld.Name
        ApXL.ActiveCell.Offset(0, 1).Select
    Next
 

Dnphm

Registered User.
Local time
Today, 03:13
Joined
Jul 13, 2010
Messages
11
Not sure why that would cause a problem.

Also, are you going to want to leave the current column headers? If so, we should remove this part anyway:

Code:
    xlWSh.Range("A1").Select
    For Each fld In rst.Fields
        ApXL.ActiveCell = fld.Name
        ApXL.ActiveCell.Offset(0, 1).Select
    Next

I think I fixed the Range Class problem by inserting

Code:
xlWsh.Select

before the range code.

But it seems to still do everything except overwriting the worksheet and changing the data.
 

Dnphm

Registered User.
Local time
Today, 03:13
Joined
Jul 13, 2010
Messages
11
I figured it out how it works, but I have one more small question.

I will be running this for multiple queries, how can I not have it open the excel workbook in a new window. So, I want it to update but have it done in the same window that is open.

Thanks!
 

Users who are viewing this thread

Top Bottom