Send query info to Excel template (AXP / Win XP)

Topic

New Reply

I would like to run a query and then output data to a specific Excel template file. The data will be parsed and pasted to certain cells on the spreadsheet. Has anyone had experience with this sort of thing before?

Thanks,
Mark Santos

Reply | Quote

Replies

Hi Mark,

I’ve done this quite a bit on a large project last year.

I experimented in dumping the data into a template, but I always ran into problems with formatting and formulas. For example, I tried to preset a formula in the template, but I never knew exactly how many Access records I would dump into it. I found it inefficient to have more formula rows than Access records and unacceptable to have more Access records than formula rows.

Another issue I ran into was distribution and path locations of the XLT file. Although it’s not a huge problem to distribute an Excel template, it’s much easier not to!

What I ended up doing was building the “template” into code in Access. I built the formulas into Access and dumpted them into Excel – one for each record. This eliminated the problem of not knowing how many Access records to expect in the sheet.

To handle formatting (Font sizes, Borders, Cell background colors, etc…), I cheated a bit: I recorded a macro in Excel while I applied the formatting. Then I “borrowed” the code and modified it to fit an automation environment from within Access (i.e. substitute Excel’s Application object for the Excel Application variable used in Access).

The only downside to all of this is the fact that it’s “hard-coded” and much harder to modify than making changes to an Excel Template.

I also gave the user a “Preferences” setting that allowed them to open the finished Excel document immediately or choose to have it save to a specified path/filename. This was useful for some users who had another spreadsheet with links to the sheet created by my app. They could just generate the Excel sheet from Access and then refresh the data in their other spreadsheet. It worked quite well!

I’ll be happy to post a small example if anyone had trouble following my cryptic explanation.

HTH

Reply | Quote

Another way to do this is to create an Excel query. Create a new Excel file.

Menu choices: Data, Get External Data, Create New Query

Then select Access as datasource and select a query or table from your database. The query then can be modified in Excel or not. One of the options is to refresh the query upon opening the spreadsheet. Save your spreadsheet. To run the query just open the spreadsheet your created.

I use this method for creating updated charts.

Reply | Quote

Thanks Tom,
This sounds like what I need. I will have this spreadsheet saved as a template. If I can do what you say, the user should just be able to double click the template shortcut which will create a new spreadsheet from that template. Then they can save it as something else. I am also using it for a chart output.

Thanks,
Mark

Reply | Quote

FYI – I do not use a template, I make an Excel file and use that. Then if I need the file saved, I save it under another name. Once you have saved the file under another name you may want to stop refreshing the data. You do that by the following menu choices. Data, Get External Data, Data Range Properities, and then uncheck “Refresh data on file open”.

Reply | Quote

FWIW, here’s a custom code sample of one of my routines. I used 2 recordsets – one was from Column A to B, the other was only one column (D). Columns C and E have formulas in them (rather than static calculated values generated from a query).

The rest of the code is for formatting and protecting the sheet – the part that I copied from an Excel Macro and modified.

Public Sub FormatExcel()


Dim  db as DAO.Database
Dim rst as DAO.Recordset
Dim App as New Excel.Application
Dim Sheet as New Excel.Worksheet

set db = CurrentDb()
'Open First Recordset
Set rst = db.OpenRecordset("ExcelQry", dbOpenSnapshot)
    

    'Add and Select new Sheet
    App.Workbooks.Add
    App.Sheets("Sheet1").Select
    Set Sheet = App.ActiveSheet
    
    'Unprotect sheet
    Sheet.Unprotect
    
    'Paste data from First recordset starting at A3
    Sheet.Range("A3").CopyFromRecordset rst
    
    'Open Second Recordset
    Set rst = db.OpenRecordset("ExcelQry2", dbOpenSnapshot)
   
    'Paste data from Second recordset starting at D3
    Sheet.Range("D3").CopyFromRecordset rst
    
    
    Dim RowNum As Integer
    Dim X As Integer
    For X = 0 To rst.RecordCount - 1
        Sheet.Cells(X + 3, 3).Value = "=(100/B" & X + 3 & ")/100"
        Sheet.Cells(X + 3, 5).Value = "=IF(D" & X + 3 & "=" & Chr(34) & Chr(34) & "," _ 
         & Chr(34) & Chr(34) & ",D" & X + 3 & "/B" & X + 3 & ")"
    Next X
    
            
    'Set Column Widths
    Sheet.Columns("A:A").ColumnWidth = 9.71
    Sheet.Columns("B:B").ColumnWidth = 16.71
    Sheet.Columns("C:C").ColumnWidth = 16.71
    Sheet.Columns("D:D").ColumnWidth = 11.86
    Sheet.Columns("E:E").ColumnWidth = 18.86
    
    'Unlock Columns B and D
    Sheet.Range("B2:B65536,D2:D65536").Select
    App.Selection.Locked = False
    
    'Center Columns A,B,C and E
    Sheet.Range("A:C,E:E").Select
    With App.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    
    
    'Center D1 (The only column heading that_
    'wasn't centered in the last action
    Sheet.Range("D2").Select
    With App.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    
    'Format Column B with Numbers
    Sheet.Range("B3:B" & rst.RecordCount).Select
    App.Selection.NumberFormat = "0.0"
    
    'Format Column C with Percents
    Sheet.Range("C3:C65536").Select
    App.Selection.Style = "Percent"
    App.Selection.NumberFormat = "0.0%"
    
    'Format Column E with 3 decimal places
    Sheet.Range("E3:E65536").Select
    App.Selection.NumberFormat = "#0.0"
    
    
    'Format Column Headings: Underline and Bold
    Sheet.Range("A2:E2").Select
    App.Selection.Font.Bold = True
    App.Selection.Font.Underline = xlUnderlineStyleSingle
    
    'Enter Heading (with Shift Name)
    Sheet.Range("A1").Select
    App.ActiveCell.FormulaR1C1 = "Harris Weaver Set Information for " & ShiftStr
    
    'Format Heading (Merge and Center, Font properties)
    Sheet.Range("A1:E1").Select
    With App.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    
    App.Selection.Merge
    App.Selection.Font.Bold = True
    With App.Selection.Font
        .Name = "Arial"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    
    'Enter Column Names
    Sheet.Range("A2").Select
    App.ActiveCell.FormulaR1C1 = "Style"
    Sheet.Range("B2").Select
    App.ActiveCell.FormulaR1C1 = "Looms Per Job"
    Sheet.Range("C2").Select
    App.ActiveCell.FormulaR1C1 = "Job Percent"
    Sheet.Range("D2").Select
    App.ActiveCell.FormulaR1C1 = "Looms On"
    Sheet.Range("E2").Select
    App.ActiveCell.FormulaR1C1 = "Weavers Required"
    Sheet.Range("B3").Select
    
    'Finish up (protect the sheet, select a final cell)
    Sheet.Range("D3").Select
    Sheet.Protect
    
End Sub

HTH

Reply | Quote

Looks good. I may be able to use some of this as it looks a little more involved then what I need. How wold you call a template?

Thanks,
Mark

Reply | Quote

Hi Mark,

Actually, the example I posted above was going a little overboard. Here’s a much more simple example using a template:

Private Sub btnExport_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim xlApp As New Excel.Application
    Dim xlSheet As New Excel.Worksheet
    
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("qryChoir", dbOpenSnapshot)
    
    'Open the Template
    xlApp.Workbooks.Open "C:WindowsDesktopBook1.xlt"
    
    'Set the Sheet Variable
    Set xlSheet = xlApp.Worksheets("Sheet1")
    
    'Paste the Recordset (starting in cell A3
    xlSheet.Range("A3").CopyFromRecordset rst
    
    'Give control of Excel to the user
    xlApp.Visible = True
    
    'Close the Recordset
    rst.Close
    
    'Destroy object variables
    Set xlSheet = Nothing
    Set xlApp = Nothing
    Set rst = Nothing
    Set db = Nothing
End Sub

This gives the control to the user for modifying and/or saving. You can have the code save the newly created Excel file automatically and not even show it to the user…

Just as an aside (for anyone who is new with Automation coding), as you implement error handling be sure to destroy your object variables in the error handling code. In my earlier attempts I could easily trap errors, but I would exit the sub and forget to close Excel and destroy its variables….I’ve learned[smlie]

HTH

Reply | Quote

In the “qryChoir” portion of the code below, say that it is a query. In that query I have some variable that it prompts the user for such as [status]. How do I pass this value to the query in code? I am getting a RTE 3061 too few parameters, expected 2 when trying to run this.

Thanks,
Mark

Reply | Quote

If you’re trying to use a Parameter query, you’ll need to include the Parameters in the code. I haven’t done this with DAO in a while, but you should be able to find documentation for this in Access/VBA.

You’ll need to create a Parameter object (dim prm as DAO.Parameter), then assign values to the parameter and assign the parameter to the query (or qryDef, in DAO).

HTH

Reply | Quote

It’s starting to make some sense now. I’ll dig a bit further.

Thanks,
Mark

Reply | Quote