Jim Moss
jim.moss at jlmoss.net
Fri Jan 6 12:48:36 CST 2006
John,
Here's an example of Excel creation using CopyFromRecordset and
formatting. You should be able bind a control to a cell, just start macro
recording, do your control thing, and save the macro. Then you can copy
the vba into your access module. You can also use macro recording to
figure out cell locking.
Jim
Set Rs1 = DbA.OpenRecordset("SELECT SupplierName, InvoiceNumber, PONumber,
DueDate, ERP, LocDesc, AmountInUSD, Status, APKey FROM
qrsAPTrialBalanceNorthAmerica WHERE SupplierName = GetSupplierNameLB();",
dbOpenSnapshot)
' CopyFromRecordset code
intMaxCol = Rs1.Fields.Count
If Rs1.RecordCount > 0 Then
Rs1.MoveLast
Rs1.MoveFirst
intMaxRow = Rs1.RecordCount
Set objXL = CreateObject("Excel.Application")
With objXL
.Visible = False
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
.Range(.cells(2, 1), .cells(intMaxRow, _
intMaxCol)).CopyFromRecordset Rs1
End With
With objSht
.Columns("G:H").select
.Columns.NumberFormat = "#,##0.00"
End With
With objSht
.Columns("M:M").select
.Columns.NumberFormat = "#,##0.00"
End With
End With
End If
objXL.Sheets("Sheet1").select
objXL.Sheets("Sheet1").Name = GetCOID()
objSht.cells(1, 1).select
objSht.cells(1, 1).FormulaR1C1 = "ReportDate"
objSht.cells(1, 1).interior.colorindex = 19
objSht.cells(1, 2).select
objSht.cells(1, 2).FormulaR1C1 = "GLAcct"
objSht.cells(1, 2).interior.colorindex = 19
objSht.cells(1, 3).select
objSht.cells(1, 3).FormulaR1C1 = "SupplierName"
objSht.cells(1, 3).interior.colorindex = 19
objSht.cells(1, 4).select
objSht.cells(1, 4).FormulaR1C1 = "InvoiceNumber"
objSht.cells(1, 4).interior.colorindex = 19
objSht.cells(1, 5).select
objSht.cells(1, 5).FormulaR1C1 = "InvoiceDate"
objSht.cells(1, 5).interior.colorindex = 19
objSht.cells(1, 6).select
objSht.cells(1, 6).FormulaR1C1 = "Curr"
objSht.cells(1, 6).interior.colorindex = 19
objSht.cells(1, 7).select
objSht.cells(1, 7).FormulaR1C1 = "OriginalAmount"
objSht.cells(1, 7).interior.colorindex = 19
objSht.cells(1, 8).select
objSht.cells(1, 8).FormulaR1C1 = "RemainingAmount"
objSht.cells(1, 8).interior.colorindex = 19
objSht.cells(1, 9).select
objSht.cells(1, 9).FormulaR1C1 = "ERP"
objSht.cells(1, 9).interior.colorindex = 19
objSht.cells(1, 10).select
objSht.cells(1, 10).FormulaR1C1 = "Location"
objSht.cells(1, 10).interior.colorindex = 19
objSht.cells(1, 11).select
objSht.cells(1, 11).FormulaR1C1 = "Grouping"
objSht.cells(1, 11).interior.colorindex = 19
objSht.cells(1, 12).select
objSht.cells(1, 12).FormulaR1C1 = "Category"
objSht.cells(1, 12).interior.colorindex = 19
objSht.cells(1, 13).select
objSht.cells(1, 13).FormulaR1C1 = "AmtInUSD"
objSht.cells(1, 13).interior.colorindex = 19
objSht.cells(1, 14).select
objSht.cells(1, 14).FormulaR1C1 = "Acct"
objSht.cells(1, 14).interior.colorindex = 19
objSht.cells(1, 15).select
objSht.cells(1, 15).FormulaR1C1 = "Co"
objSht.cells(1, 15).interior.colorindex = 19
'objSht.Rows("1:1").Select
objSht.rows("1:1").AutoFilter
objSht.rows("2:2").select
objXL.ActiveWindow.FreezePanes = True
objWkb.SaveAs ("J:\Month End Reports\" & strUser &
"\APTrialBalance_" & strDate & "_" & MyERPOUR)
objWkb.Close
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Rs0.MoveNext
Loop
> And here is an example of adding data to an Excel file from the Automation
> help file...
>
> Sub bulkTransfer()
>
> Dim xlApp As Excel.Application
> Dim xlBook As Workbook
> Dim xlSheet As Worksheet
>
> 'Start a new workbook in Excel.
> Set xlApp = CreateObject("Excel.Application")
> Set xlBook = xlApp.Workbooks.Add
>
> 'Create an array that contains 3 columns and 100 rows.
> Dim aryData(1 To 100, 1 To 3) As Variant
> Dim intCount As Integer
>
> For intCount = 1 To 100
> aryData(intCount, 1) = "ORD" & Format(r, "0000")
> aryData(intCount, 2) = Rnd() * 1000
> aryData(intCount, 3) = aryData(intCount, 2) * 0.7
> Next
>
> 'Add headers to the worksheet on row 1.
> Set xlSheet = xlBook.Worksheets(1)
> xlSheet.Range(xlSheet.Cells(1,1),xlSheet.Cells(1,3)).Value =
> Array("Order
> ID", "Amount", "Tax")
>
> 'Transfer the array to the worksheet, starting at cell A2.
> xlSheet.Range("A2").Resize(100, 3).Value = aryData
>
> 'Save the workbook and quit Excel.
> xlBook.SaveAs "C:\My Documents\ArrayDump.xls"
> xlApp.Quit
> Set xlSheet = Nothing
> Set xlBook = Nothing
> Set xlApp = Nothing
>
> End Sub
>
> Lambert
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
> Sent: Friday, January 06, 2006 12:47 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Can't write to an Excel spreadsheet
>
>
> Oh yea, I remember. What a PITA. That throws a major wrench in the
> works.
> I was hoping to open a predefined spreadsheet, with locked columns, named
> ranges etc., then just export data to it, attach to an email and send.
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
> Sent: Friday, January 06, 2006 12:41 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Can't write to an Excel spreadsheet
>
> There were a couple of threads on this a few weeks ago.
>
> Apparently M$ lost some law suit and the end result was they had to
> disable
> the ability of Access to update Excel. You can still export to Excel, but
> that's it - I think.
>
> Lambert
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
> Sent: Friday, January 06, 2006 12:30 PM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] Can't write to an Excel spreadsheet
>
>
> I created a spreadsheet in a workbook. Placed text names in the top row
> for
> four columns, saved the sheet. I then linked the sheet to Access. With
> no
> data in the spreadsheet, the table shows no data, and there is no new
> record
> row. If I try to append data to Columns A and B using an append query, I
> get a "you are using a non-updateable query" or something similar. If I
> name a range and link to the named range, with actual data in a few
> records,
> the entire range shows, the data shows in the first few rows, but the
> whole
> thing is non-updateable.
>
> I thought that an Excel spreadsheet was updateable from Access. Am I
> doing
> something wrong or is Excel indeed not updateable from Access?
>
> John W. Colby
> www.ColbyConsulting.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>