TransferSpreadsheet from Access to Excel. How do I change QueryName on export?

[KevH]

Currently, I'm storing a list of queries I'd like to export in a table. On the VBA side, I'm loading the table into a record set and then cycling through the list. In that loop, I'm using TransferSpreadsheet to export to Excel. Without any kind of aliasing, the end result is Excel tabs that say Transform1, Transform2, etc.

Is there a way to rename the query name so that each tab in Excel shows a user selected name?

Preferably the solution would be aliases stored in the table, but something entered at runtime is OK too.

Thanks!
Kev

 

[ease20022002]

If the names of the tables can be predefined, then add a field to the table you are looping through and put the names in that table so you can use that field to set the worksheet name.

If a user needs to define it at run time, that is a whole different story. It would depend on how many tabs need to be renamed and how many workbooks you are creating. You could always use a message box, or create a Form in access that would allow text for workbook names and worksheets.

Hopefully the above helps.

 

[KevH]

The first part you described is what I have now - Access creates a single WB and then exports several queries to their own sheets within the WB.

The problem is that the sheets are just using the query names. I'm looking for the next level.

For example, the queries all have default values like Transform1, but want to alias those to the selected filters. I can concatenate a name like "Filter3Subfilter1" easily enough, but how do I send THAT to Excel instead of Transform1.

Right now when I reference a name like "Filter3Subfilter1" it goes and looks for "Filter3Subfilter1" instead of Transform1 (and since there's no such thing as "Filter3Subfilter1", it errors out).

What I want is for it to look up the Transform1 query and then export it with the label "Filter3Subfilter1". I'd be building both the reference to the query and the generated label inside a loop so Transform2 will be exported as "Filter3Subfilter2", Transform3 will be exported as "Filter3Subfilter3" and so on.

Clear as mud?

 

[ease20022002]

Hi. I hope I am not too late in responding. I understand what you are saying. My question would be why not create another table that maps query names to worksheet names, and do something that is relational, so you could create the recordset by writing an in line SQL statement that would .OpenRecordSet. Then you have the appropriate worksheet name in the recordset in code (memory). That way you loop through the query names and you will have the appropriate worksheet name associated to it.

I believe you will have to do some data entry to make sure the mapping is correct. If there is a ton of queries... let me know.

 

[Derek Brown]

I would consider doing this from Excel.
A table in a worksheet containing the name of the database, the table or query name and the target worksheet name.
With a column in that table being a named range it would be easy to go through each record in the table (using VBA), transfer in the table/query and then rename the worksheet if necessary.
I currently use Excel to do this but for transferring between databases.
Where I want such tables/queries in an Excel workbook I use the linked data technique, with the recordset set to 'read only' and then either set them to refresh on opening the workbook or I create a macro to do this when required.
You did not say which version of Excel is being used - I am currently using Excel 2007/2010.

 

[boblarson]

An easy method to set the tabs to what you want is to use Excel Automation instead of the TransferSpreadsheet code. I have some on my website, where you can export a table/query to an Excel File and name the sheet specifically what you want. Derek's suggestion of a table is good but it can be in Access and you can use a DLookup to find out what the name you want based on the query name is and then pass that name to my function.


See my function here:

Public Function SendTQ2ExcelNameNewSheet(strTQName As String, 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 your sheet to

    
    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim fld As Field
    Dim strPath As String

    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
    On Error GoTo err_handler
 

    Set rst = CurrentDb.OpenRecordset(strTQName)
    Set ApXL = CreateObject("Excel.Application")

    Set xlWBk = ApXL.Workbooks.Add
    ApXL.Visible = True
    
    xlWBk.Worksheets("Sheet1").Name = strSheetName
    Set xlWSh = xlWBk.Worksheets(strSheetName)
   
    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

<?XML:NAMESPACE PREFIX = O /><O:P>

</O:P>

 

[Bernard Kellam]

I have a similar issue and the above code nicely addresses part of it - Thanks.
In addition to the above functionality, my solution requires that:
1. The resulting workbook should have a file name that is passed by the function and
2. The resulting workbook should have more than one worksheet.
How might that be accommodated?
Thanks any assistance is greatly appreciated.

 

[boblarson]

I have a similar issue and the above code nicely addresses part of it - Thanks.
In addition to the above functionality, my solution requires that:
1. The resulting workbook should have a file name that is passed by the function and
2. The resulting workbook should have more than one worksheet.
How might that be accommodated?
Thanks any assistance is greatly appreciated.

You can name the workbook using

xlWBk.SaveAs strFileName


and adding another worksheet is just as simple as

xlWBk.Worksheets.Add

and you can choose where by using the Before or After arguments of that.