Mark A Matte
markamatte at hotmail.com
Thu May 10 09:22:23 CDT 2007
Hello All,
In A97 I have isolated my newest problem. I using the following to output a
report to excel:
DoCmd.OutputTo acOutputReport, strReport, acFormatXLS, strpathTemp1
The machine in question has excel 03...and when it tries to open the file
created above...I get an error: "an invalid sheetname was used and had to
be corrected"
I looked at this file on a box with just office 97...and the sheetname was
Sheet1...no problems...I can manually change it back to Sheet1 and it saves
fine.
Any one have any ideas?
Thanks,
Mark A. Matte
>From: "Mark A Matte" <markamatte at hotmail.com>
>Reply-To: Access Developers discussion and problem
>solving<accessd at databaseadvisors.com>
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] Report to Excel_
>Date: Wed, 09 May 2007 18:26:50 +0000
>
>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
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
_________________________________________________________________
More photos, more messages, more storage—get 2GB with Windows Live Hotmail.
http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migration_HM_mini_2G_0507