Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    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 <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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".

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.<pre>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").ColumnWidth = 11.86
    Sheet.Columns("E:E").ColumnWidth = 18.86

    'Unlock Columns B and D
    Sheet.Range("B2:B65536,D265536").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
    </pre>

    HTH <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Hi Mark,

    Actually, the example I posted above was going a little overboard. Here's a much more simple example using a template:<pre>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</pre>

    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 <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  9. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    using Parameter with DAO

    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

  11. #11
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using Parameter with DAO

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

    Thanks,
    Mark

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •