I have two reports to create and they both have different column widths in Excel. My problem is, if I set the column width in one worksheet it seems like it is copying the same width to the second report. Below is the code I am using, maybe somebody can tell me what I am doing wrong.

Private sub cmd_first_report()
Dim excelVersion As Integer
Dim objexcel As Object
Dim objTemp As Object
Dim intFontsize As Integer

'--Create reference variable for the spreadsheet
Set objexcel = CreateObject("Excel.application")
objexcel.Visible = True
objexcel.Workbook.Add

'--Set Page Orientation to Landscape

    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    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
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With



'We need this line to ensure Excel remains visible if we switch to the active sheet
Set objTemp = objexcel


excelVersion = Val(objexcel.Application.Version)
If (excelVersion >= 8) Then
   Set objexcel = objexcel.ActiveSheet
End If


'--Set Column Width here
   Call Column_Width("A", 1, 18)
   Call Column_Width("B", 1, 20)
   Call Column_Width("C", 1, 12.85)
   Call Column_Width("D", 1, 12.85)
   Call Column_Width("E", 1, 8.45)
   Call Column_Width("F", 1, 9.6)
   Call Column_Width("G", 1, 9.86)
   Call Column_Width("H", 1, 12.45)
   Call Column_Width("I", 1, 11)

'--Start First Report here

codes....

End sub

Private sub cmd_Second_Report()


Dim excelVersion As Integer
Dim objexcel As Object
Dim objTemp As Object
Dim intFontsize As Integer


'--Create reference variable for the spreadsheet
Set objexcel = CreateObject("Excel.application")
objexcel.Visible = True
objexcel.Workbooks.Add

'We need this line to ensure Excel remains visible if we switch to the active sheet
Set objTemp = objexcel

excelVersion = Val(objexcel.Application.Version)
If (excelVersion >= 8) Then
   Set objexcel = objexcel.ActiveSheet
End If

'--Set Column Width here
   Call Column_Width("A", 1, 13.29)
   Call Column_Width("B", 1, 12.71)
   Call Column_Width("C", 1, 8)
   Call Column_Width("D", 1, 8.43)
   Call Column_Width("E", 1, 1.57)
   Call Column_Width("F", 1, 11.86)
   Call Column_Width("G", 1, 8.43)

'--Start Per Diem Report Here

codes...

End Sub


If the second report is chosen by the user first, the column width will be copied to the first report.

What am I doing wrong? Help...


Cheers,
jmss66

Do you see two instances in the task bar when both reports are open? (I'm wondering if perhaps VB is obtaining a 2nd reference to the running instance of Excel, despite your explicit CreateObject() as opposed to GetObject...

Is Column_Width a routine that you have written? If not, is it in the VB environment in which you are writing, or is it something that Excel itself provides?

Hi Brian,

I see two instances of Excel open when I click on both command button and yes column_width is a routine I wrote which passes the size of the columns. Below is the column_width rtn.

Public Sub Column_Width(strColumn As String, intRow As Integer, dblColWidth As Double)

    Dim strColumnWidth As String

    strColumnWidth = strColumn & intRow
    Range(strColumnWidth).Select
    Selection.ColumnWidth = dblColWidth

End Sub

Thanks,
Judy

Anybody please help me on this.

Thanks,
jmss66

Still no takers?