VB and EXCEL as reporting tool

'I use Excel as a reporting tool, pumping data from my database to predefined templates created in excel.
'The first time I run the report, everything is absolutely perfect
'If I close Excel (using the "x" in Excel) and try to run the report again, I get a number of inconsistencies


'I need to know how to make sure that all instances of excel running as closed.

'I set all the objects declared below = nothing at the end of the process, but this does not seem to do the trick.

'Please could someone tell me if what I'm doing wrong and if there is a better way of doing it.

'Also, is there a way to ensure that it works for both Excel 2000 (Office 2000) and Excel 2002 (XP), becuase it's not working in 2000??

'Below is an indication of what I've been doing


'declarations in my module

Public oXL As Excel.Application
Public oWB As Excel.workbook
Public oSheet As Excel.Worksheet
Public oRng As Excel.Range


Private Sub cmdReport_Click()


Set oXL = Nothing
Set oXL = CreateObject("Excel.Application")

oXL.Workbooks.Open App.Path & "Preset Reports.xlt", , False
Set oWB = oXL.Workbooks(1)

End Sub


'Cofiguring the templates
'-----------------------
With workbook
.Sheets("YTD").Select
Range("A1:M33").Select
With Selection
.Copy
End With
Sheets.Add.Name = adoAgentRS.Fields("agentName") & "-YTD"
Range("A1:N31").Select
ActiveSheet.Paste
Columns("A:A").ColumnWidth = 21.57
Columns("B:B").ColumnWidth = 5
Columns("C:C").ColumnWidth = 10.14
Columns("D:D").ColumnWidth = 5
Columns("E:E").ColumnWidth = 13
Columns("F:F").ColumnWidth = 5
Columns("G:G").ColumnWidth = 13
Columns("H:H").ColumnWidth = 5
Columns("I:I").ColumnWidth = 10.14
Columns("J:J").ColumnWidth = 13
Columns("K:K").ColumnWidth = 5
Columns("L:L").ColumnWidth = 10.14
Columns("M:M").ColumnWidth = 13

'//setting up the page for printing purposes
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
'//end of page configuration
End With

'PERFORM ALL ACTIONS THAT POPULATE THE EXCEL SHEET
'-------------------------------------------------

'I send the data to Excel using code similar to the line below

Cells(currentRow, 1) = adoDimensionsRS.Fields("dimensionDescription")

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories