p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3 (http://p2p.wrox.com/forumdisplay.php?f=329)
-   -   Excel Formatting from Access (http://p2p.wrox.com/showthread.php?t=75776)

moshek August 18th, 2009 03:16 PM

Excel Formatting from Access
 
I am exporting a spreadsheet from Access and then formatting the spreadsheet. If I open the database and export the file it works the first time. If, however, I leave the database open and try to export a second time, it errs out "Runtime Error 1004 - Method "Cells" of Object "_Global" Failed. Here's the code. It errs on the line with the angry face. What am I missing?

[code]
Public Function ContLiab()
Dim GetDlg As FileDialog
Dim XLApp As Excel.Application
Dim MyFile As String
Set MyDb = CurrentDb
'ReportPath = "K:\JAMAA\Contingent Liabilities"
ReportPath = CurrentProject.Path
ReportFile = "\ContingentLiabilities_"
ReportDate = Format(Date, "mm-dd-yy")
varX = 1
ReportFile = ReportPath & ReportFile & ReportDate & ".xls"
MyDb.Execute "DELETE tbl_Contliab.* FROM tbl_Contliab;"

Set GetDlg = Application.FileDialog(msoFileDialogOpen)
GetDlg.Title = "Please select the raw data file to import"
GetDlg.InitialFileName = ReportPath & "\ContLiab.txt"
GetDlg.ButtonName = "Import"
GetDlg.Show
MyFile = GetDlg.SelectedItems.Item(1)

DoCmd.TransferText acImportFixed, "Contliab", "tbl_Contliab", MyFile
FileCopy ReportPath & "\Contingent Liabilities_TPL.xls", ReportFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CL_Comm", ReportFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CL_NoComm", ReportFile, True

''************ FORMATTING
Set XLApp = New Excel.Application
With XLApp
' 'Open the file that was selected so that we can play with it.
'
' '******************************Format External Data Worksheet ***************************************
' '.Visible = True
.Workbooks.Open ReportFile

.Sheets("Guidelines").Select
.Range("C14:M14").Select
.ActiveCell.FormulaR1C1 = "Contents Data Current As of: " & Format(Now, "dd mmm, yyyy - hh:nn AMPM")
.ActiveCell.Characters(Start:=1, Length:=40).Font.Name = "Arial"
.ActiveCell.Characters(Start:=1, Length:=40).Font.FontStyle = "Bold"
.ActiveCell.Characters(Start:=1, Length:=40).Font.Size = 10
.ActiveCell.Characters(Start:=1, Length:=40).Font.Strikethrough = False
.ActiveCell.Characters(Start:=1, Length:=40).Font.Superscript = False
.ActiveCell.Characters(Start:=1, Length:=40).Font.Subscript = False
.ActiveCell.Characters(Start:=1, Length:=40).Font.OutlineFont = False
.ActiveCell.Characters(Start:=1, Length:=40).Font.Shadow = False
.ActiveCell.Characters(Start:=1, Length:=40).Font.Underline = xlUnderlineStyleNone
.ActiveCell.Characters(Start:=1, Length:=40).Font.ColorIndex = xlAutomatic
.Range("C15").Select

.Sheets("CL_Comm").Select
.Range("A1:Y1").Select
.Selection.Font.Bold = True
.Columns("A:Y").Select
.Columns("A:Y").EntireColumn.AutoFit
.ActiveCell.SpecialCells(xlLastCell).Select
.Range(Selection, Cells(1)).Select[:(!]
.Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
.Range("A2").Select
.ActiveWindow.FreezePanes = True
.Range("A1:Y1").Select
.Selection.Interior.ColorIndex = 34
.Selection.Interior.Pattern = xlSolid

.ActiveCell.SpecialCells(xlLastCell).Select
.Range(Selection, Cells(1)).Select
.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
.Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Selection.Borders(xlEdgeLeft).Weight = xlThin
.Selection.Borders(xlEdgeLeft).ColorIndex = xlAutomatic
.Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
.Selection.Borders(xlEdgeTop).Weight = xlThin
.Selection.Borders(xlEdgeTop).ColorIndex = xlAutomatic
.Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Selection.Borders(xlEdgeBottom).Weight = xlThin
.Selection.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
.Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
.Selection.Borders(xlEdgeRight).Weight = xlThin
.Selection.Borders(xlEdgeRight).ColorIndex = xlAutomatic
.Selection.Borders(xlInsideVertical).LineStyle = xlContinuous
.Selection.Borders(xlInsideVertical).Weight = xlThin
.Selection.Borders(xlInsideVertical).ColorIndex = xlAutomatic
.Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Selection.Borders(xlInsideHorizontal).Weight = xlThin
.Selection.Borders(xlInsideHorizontal).ColorIndex = xlAutomatic

.Sheets("CL_NoComm").Select
.Range("A1:Y1").Select
.Selection.Font.Bold = True
.Columns("A:Y").Select
.Columns("A:Y").EntireColumn.AutoFit
.ActiveCell.SpecialCells(xlLastCell).Select
.Range(Selection, Cells(1)).Select
.Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
.Range("A2").Select
.ActiveWindow.FreezePanes = True
.Range("A1:Y1").Select
.Selection.Interior.ColorIndex = 34
.Selection.Interior.Pattern = xlSolid

.ActiveCell.SpecialCells(xlLastCell).Select
.Range(Selection, Cells(1)).Select
.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
.Selection.Borders(xlDiagonalUp).LineStyle = xlNone

.Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Selection.Borders(xlEdgeLeft).Weight = xlThin
.Selection.Borders(xlEdgeLeft).ColorIndex = xlAutomatic

.Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
.Selection.Borders(xlEdgeTop).Weight = xlThin
.Selection.Borders(xlEdgeTop).ColorIndex = xlAutomatic

.Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Selection.Borders(xlEdgeBottom).Weight = xlThin
.Selection.Borders(xlEdgeBottom).ColorIndex = xlAutomatic

.Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
.Selection.Borders(xlEdgeRight).Weight = xlThin
.Selection.Borders(xlEdgeRight).ColorIndex = xlAutomatic

.Selection.Borders(xlInsideVertical).LineStyle = xlContinuous
.Selection.Borders(xlInsideVertical).Weight = xlThin
.Selection.Borders(xlInsideVertical).ColorIndex = xlAutomatic

.Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Selection.Borders(xlInsideHorizontal).Weight = xlThin
.Selection.Borders(xlInsideHorizontal).ColorIndex = xlAutomatic

.Range("A2").Select
.Sheets("CL_Comm").Select
.Range("A2").Select

.Sheets("Guidelines").Select
.Range("A2").Select
.ActiveWorkbook.Save
.Quit
End With
Set XLApp = Nothing

MsgBox "Your file has been exported to " & ReportFile, vbOKOnly + vbInformation, "CSR MGT Notices"
End Function
[\code]


All times are GMT -4. The time now is 07:49 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.