Mark A Matte
markamatte at hotmail.com
Wed May 9 13:26:50 CDT 2007
Thanks John,
I didn't know how to use a recordset...because the data I wanted out went
through some heavy formatting in the report. Thats why I think I am stuck
with the OUTPUTTO method, unless I choose to do all of my formatting after.
The report's formatting is dependant on user input...they decide what
columns are included, what order the columns are in, and the columns are
colored depending what order they are selected in. Most of this is done
behind the report in VBA.
That part I finished...and with the last input from Shamil...I got the
sheets into 1 workbook.
Thanks to All for the support and suggestions.
Thanks again,
Mark A. Matte
>From: "John Skolits" <askolits at ot.com>
>Reply-To: Access Developers discussion and problem
>solving<accessd at databaseadvisors.com>
>To: "'Access Developers discussion and problem
>solving'"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Report to Excel_
>Date: Wed, 9 May 2007 14:05:32 -0400
>
>I just tried this..
>
>After I pasted my data, I then added another worksheet like this:
>
>objXLBook.Worksheets.Add
>
>Then I referred to it as objXLBook.Worksheets(2)
>
>And was then able to paste data into another sheet in the same workbook.
>
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
>Sent: Wednesday, May 09, 2007 10:26 AM
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] Report to Excel_
>
>Thanks John,
>
>I got the formatting the way I needed it...I used the report to determine
>what order the columns would be in...and then changed the font,color,etc in
>excel.
>
>Now I just need to get the sheets into a single workbook.
>
>Thanks,
>
>Mark
>
>
>
> >From: "John Skolits" <askolits at ot.com>
> >Reply-To: Access Developers discussion and problem
> >solving<accessd at databaseadvisors.com>
> >To: "'Access Developers discussion and problem
> >solving'"<accessd at databaseadvisors.com>
> >Subject: Re: [AccessD] Report to Excel_
> >Date: Tue, 8 May 2007 18:02:47 -0400
> >
> >Mark,
> >
> >I've been kind-of following this thread and thought I'd share my code
>with
> >you.
> >It may not help you with what you're doing, but it's a slightly different
> >approach.
> >I use an Excel sheet as a template to do some of my formatting. I open
>it,
> >use the CopyFromRecordset function to paste the data into Excel, then do
>a
> >"Save AS". The nice thing about the template is you can make a formatting
> >change to it instead of having to do it in the code. There will probably
>be
> >some need to still do some vba-code formatting of the spreadsheet, but
>this
> >can cut down on some of the coding.
> >
> >Note: You can't paste the code below and run it because I cut it up a
> >little
> >to make it more succinct, but it may give you some other ideas. I also
>have
> >some error trapping codes at the end that may help.
> >
> >John
> >----
> >
> >
> >Private Sub btnExportToExcelX()
> >
> >On Error GoTo Err_btnExportToExcel
> >
> >Dim objXLBook As Excel.Workbook
> >Dim rsFinalBidQuery As dao.Recordset, varResults As Variant
> >Dim objXLRange As Excel.Range, objXLApp As Excel.Application
> >Dim objResultsSheet As Excel.Worksheet
> >Dim intMaxCol As Integer
> >Dim intMaxRow As Integer
> >Dim strExportFilename As String, strFullXLTemplateFilename As String
> >
> >strExportFilename =TestWorkbook.xls"
> >strFullXLTemplateFilename = "c:\ExcelTemplateFile.xls"
> >
> >Set objXLBook = GetObject(strFullXLTemplateFilename)
> >Set objXLApp = objXLBook.Parent
> >Set objResultsSheet = objXLBook.Worksheets(1)
> >
> >Set rsFinalBidQuery = CurrentDb().OpenRecordset("qryToExport")
> > rsFinalBidQuery.MoveLast
> > rsFinalBidQuery.MoveFirst
> >
> > '*Store the data in the worksheet
> > intMaxCol = rsFinalBidQuery.Fields.Count
> > If rsFinalBidQuery.RecordCount > 0 Then
> > intMaxRow = rsFinalBidQuery.RecordCount
> > With objXLApp
> > With objResultsSheet
> > .Range(.Cells(11, 2), .Cells(11 + intMaxRow, 2 +
> >intMaxCol)).CopyFromRecordset rsFinalBidQuery
> > End With
> > End With
> > End If
> >
> >rsFinalBidQuery.Close
> >
> >'Add some values to specific defined cells
> >objResultsSheet.Range("PartNum").Value = "PN# 1234"
> >objResultsSheet.Range("PartDesc").Value = "Description"
> >
> >objXLBook.Parent.Windows(objXLBook.Name).Visible = True
> >
> >'Save it as a new name
> >objXLBook.SaveAs strExportFilename
> >objXLApp.Visible = True
> >
> >' Release the object variable
> >Set objXLBook = Nothing
> >
> >BuildXLBidReport_EXIT:
> >Set rsFinalBidQuery = Nothing
> >
> >DoCmd.Hourglass False
> >DoCmd.Echo True
> >
> >Exit Sub
> >
> >BuildXLBidReport_ERR:
> >
> >'If user picks to cancel the opening of the worksheet because of the
>excel
> >macro message this error occurs
> >If Err = 287 Then Resume BuildXLBidReport_EXIT
> >
> >If Err = 70 Then
> > MsgBox " Make sure this worksheet **" & strExportFilename & "**
>isn't
> >alreday open.", 64, "Excel Sheet Open"
> > Resume BuildXLBidReport_EXIT
> >End If
> >If Err = 1004 Then
> > MsgBox " Make sure this worksheet **" & strExportFilename & "**
>isn't
> >alreday open.", 64, "Excel Sheet Open"
> > objXLBook.Application.Quit
> > ' Release the object variable
> > Set objXLBook = Nothing
> > Resume BuildXLBidReport_EXIT
> >End If
> >
> >'Display the error
> >
> > Dim strCallingObject As String
> > strCallingObject = "BuildXLBidReport" & " " &
> >Application.CurrentObjectName & " Line: " & Erl
> > lg_VarTemp = lbf_ErrorMessage(Err, Error, strCallingObject)
> > Resume BuildXLBidReport_EXIT
> >
> > Dim oApp As Object
> >
> > Set oApp = CreateObject("Excel.Application")
> > oApp.Visible = True
> > 'Only XL 97 supports UserControl Property
> > On Error Resume Next
> > oApp.UserControl = True
> >
> >Exit_btnExportToExcel:
> > Exit Sub
> >
> >Err_btnExportToExcel:
> > MsgBox Err.Description
> > Resume Exit_btnExportToExcel
> >
> >End Sub
> >
> >
> >
> >--
> >AccessD mailing list
> >AccessD at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/accessd
> >Website: http://www.databaseadvisors.com
>
>_________________________________________________________________
>Like the way Microsoft Office Outlook works? Youll love Windows Live
>Hotmail.
>http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migratio
>n_HM_mini_outlook_0507
>
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
_________________________________________________________________
Like the way Microsoft Office Outlook works? You’ll love Windows Live
Hotmail.
http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migration_HM_mini_outlook_0507