Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Export query to excel template

Discussion in 'Information Technology' started by Beeeehh, Jul 28, 2006.

  1. Beeeehh

    Beeeehh
    Expand Collapse
    Guest

    Hi,
    here is my problem.
    I would like the action set to a button in a form to export a query
    with various fields ( field A, Field B,... ) to a determinate cell in
    an excel spreadsheet.
    Then save the template as the date of the day.
    I have look severals around. Found how to write from a text field to a
    cell.
    Or export the total query to a spreadsheet, but not the query to the
    cell I want.
    I need help.
    Can anybody show me the way.
    Best regards to you all.
    Beeeeehh
     
  2. Loading...

    Similar Threads Forum Date
    World Polish exports of meat for Jews, Muslims in limbo Breaking News May 16, 2013
    India's Leading Export: CEO's Inspirational Stories Jul 23, 2011
    India India's Latest Export: Rent-a-Womb Breaking News Dec 22, 2010
    Sikh News Banning of export of milk powder will be a retrogade step, says Punjab MP Gujral (New Breaking News Jun 4, 2008
    Sikh News Punjab's Steel Producers Seek Ban On Iron Ore Export (Nasdaq) Breaking News Feb 26, 2008

  3. fredg

    fredg
    Expand Collapse
    Guest

    On 21 Jun 2006 07:31:41 -0700, Beeeehh wrote:

    > Hi,
    > here is my problem.
    > I would like the action set to a button in a form to export a query
    > with various fields ( field A, Field B,... ) to a determinate cell in
    > an excel spreadsheet.
    > Then save the template as the date of the day.
    > I have look severals around. Found how to write from a text field to a
    > cell.
    > Or export the total query to a spreadsheet, but not the query to the
    > cell I want.
    > I need help.
    > Can anybody show me the way.
    > Best regards to you all.
    > Beeeeehh


    You can use the TransferSpreadsheet method to export the query to a
    Spreadsheet (the worksheet will be named the same as the query name).
    However you cannot specify a particular cell or range of cells.
    After you transfer the query to the new worksheet, use a Macro from
    inside Excel to select, copy, and paste the data into the cell(s) you
    want.
    --
    Fred
    Please respond only to this newsgroup.
    I do not reply to personal e-mail
     
  4. Beeeehh

    Beeeehh
    Expand Collapse
    Guest

    well,
    I figured that I could actually in the code, make the query, and then
    set each of the record to the place I wanted in the excel template.
    But the problem, is that I am not even able to make the query,
    This is what I have,

    Dim xl As Object
    Dim wb As Object
    Dim ws As Object
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim strSQL As String
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Qry - Daily")
    Set xl = CreateObject("Excel.Application")
    ' open a previously saved Excel template (or sheet)
    Set wb = xl.Workbooks.Open("C:\Report.xls")
    Set ws = wb.Worksheets("Report")
    ws.Name = "Report"
    xl.Visible = True
    ws.Cells(2, 3).Value = rs!Date
    ws.Cells(2, 5).Value = rs!ASA
    xl.Save
    xl.Quit

    and I am stuck with an error on Set rs = db.OpenRecordset("Qry -
    Daily") telling me
    run time error 3061 too few parameters expected 1.

    I just wanted to be able to do that in one time, not, exporting to a
    spreadsheet, the running a macro.

    Hope somebody can help.


    fredg wrote:
    > On 21 Jun 2006 07:31:41 -0700, Beeeehh wrote:
    >
    > > Hi,
    > > here is my problem.
    > > I would like the action set to a button in a form to export a query
    > > with various fields ( field A, Field B,... ) to a determinate cell in
    > > an excel spreadsheet.
    > > Then save the template as the date of the day.
    > > I have look severals around. Found how to write from a text field to a
    > > cell.
    > > Or export the total query to a spreadsheet, but not the query to the
    > > cell I want.
    > > I need help.
    > > Can anybody show me the way.
    > > Best regards to you all.
    > > Beeeeehh

    >
    > You can use the TransferSpreadsheet method to export the query to a
    > Spreadsheet (the worksheet will be named the same as the query name).
    > However you cannot specify a particular cell or range of cells.
    > After you transfer the query to the new worksheet, use a Macro from
    > inside Excel to select, copy, and paste the data into the cell(s) you
    > want.
    > --
    > Fred
    > Please respond only to this newsgroup.
    > I do not reply to personal e-mail
     
  5. Beeeehh

    Beeeehh
    Expand Collapse
    Guest

    I did it in a manner I am not trully still happy about, but the result
    is what I expected.
    I extracted the report to a temporary excel spreadsheet, then still in
    the access macro, grab each of the records contained in each cell of
    the temp file and place it in the spaces I wanted in my excel file
    template.
    Regards.

    Beeeehh wrote:
    > well,
    > I figured that I could actually in the code, make the query, and then
    > set each of the record to the place I wanted in the excel template.
    > But the problem, is that I am not even able to make the query,
    > This is what I have,
    >
    > Dim xl As Object
    > Dim wb As Object
    > Dim ws As Object
    > Dim rs As DAO.Recordset
    > Dim db As DAO.Database
    > Dim strSQL As String
    > Set db = CurrentDb()
    > Set rs = db.OpenRecordset("Qry - Daily")
    > Set xl = CreateObject("Excel.Application")
    > ' open a previously saved Excel template (or sheet)
    > Set wb = xl.Workbooks.Open("C:\Report.xls")
    > Set ws = wb.Worksheets("Report")
    > ws.Name = "Report"
    > xl.Visible = True
    > ws.Cells(2, 3).Value = rs!Date
    > ws.Cells(2, 5).Value = rs!ASA
    > xl.Save
    > xl.Quit
    >
    > and I am stuck with an error on Set rs = db.OpenRecordset("Qry -
    > Daily") telling me
    > run time error 3061 too few parameters expected 1.
    >
    > I just wanted to be able to do that in one time, not, exporting to a
    > spreadsheet, the running a macro.
    >
    > Hope somebody can help.
    >
    >
    > fredg wrote:
    > > On 21 Jun 2006 07:31:41 -0700, Beeeehh wrote:
    > >
    > > > Hi,
    > > > here is my problem.
    > > > I would like the action set to a button in a form to export a query
    > > > with various fields ( field A, Field B,... ) to a determinate cell in
    > > > an excel spreadsheet.
    > > > Then save the template as the date of the day.
    > > > I have look severals around. Found how to write from a text field to a
    > > > cell.
    > > > Or export the total query to a spreadsheet, but not the query to the
    > > > cell I want.
    > > > I need help.
    > > > Can anybody show me the way.
    > > > Best regards to you all.
    > > > Beeeeehh

    > >
    > > You can use the TransferSpreadsheet method to export the query to a
    > > Spreadsheet (the worksheet will be named the same as the query name).
    > > However you cannot specify a particular cell or range of cells.
    > > After you transfer the query to the new worksheet, use a Macro from
    > > inside Excel to select, copy, and paste the data into the cell(s) you
    > > want.
    > > --
    > > Fred
    > > Please respond only to this newsgroup.
    > > I do not reply to personal e-mail
     

Share This Page