Print Excel file using VB

• • Reply

  5 years ago

  by radha_telanakula

  Radha

  , India

  Joined 6 years ago
  I am trying to print sheets in excel using vb.
  First i will get all the sheet names in a dialog box with checkboxes
  For printing i select the checkbox.
  Here i my coding.
  It generates a dialog box with check boxes.
  After selecting check box i click on Ok then get error at For Each Statement.
  The error is typemismatch.
  Please give solution.
  
  
  Dim Xl As Excel.Application
  Dim CWB As Excel.Workbook
  
  Private Sub Command1_Click()
  Set Xl = New Excel.Application
  Set CWB = Workbooks.Add
  Xl.Workbooks.Open ("D:\PayProject\PrintPaySlips\2007\PaystubJun2007.xls")
  
  
  
      Dim i1 As Integer
      Dim TopPos As Integer
      Dim SheetCount As Integer
      Dim PrintDlg As DialogSheet
      Dim CurrentSheet As Worksheet
      Dim cb As CheckBox
     Application.ScreenUpdating = False
  
  '   Check for protected workbook
      If ActiveWorkbook.ProtectStructure Then
          MsgBox "Workbook is protected.", vbCritical
          Exit Sub
      End If
  
  '   Add a temporary dialog sheet
      Set CurrentSheet = ActiveSheet
      Set PrintDlg = ActiveWorkbook.DialogSheets.Add
  
      SheetCount = 0
  
  '   Add the checkboxes
      TopPos = 40
      For i1 = 1 To ActiveWorkbook.Worksheets.Count
          Set CurrentSheet = ActiveWorkbook.Worksheets(i1)
  '       Skip empty sheets and hidden sheets
          If Application.CountA(CurrentSheet.Cells) <> 0 And _
              CurrentSheet.Visible Then
              SheetCount = SheetCount + 1
              PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
                  PrintDlg.CheckBoxes(SheetCount).Text = _
                      CurrentSheet.Name
              TopPos = TopPos + 13
          End If
      Next i1
  
  '   Move the OK and Cancel buttons
      PrintDlg.Buttons.Left = 240
  
  '   Set dialog height, width, and caption
      With PrintDlg.DialogFrame
          .Height = Application.Max _
              (68, PrintDlg.DialogFrame.Top + TopPos - 34)
          .Width = 230
          .Caption = "Select sheets to print"
      End With
  
  '   Change tab order of OK and Cancel buttons
  '   so the 1st option button will have the focus
      PrintDlg.Buttons("Button 2").BringToFront
      PrintDlg.Buttons("Button 3").BringToFront
  
  '   Display the dialog box
      CurrentSheet.Activate
     Application.ScreenUpdating = True
      If SheetCount <> 0 Then
          If PrintDlg.Show Then
                       
               For Each cb In PrintDlg.CheckBoxes
               
                  If cb.Value = xlOn Then
                      Worksheets(cb.Caption).Activate
                      ActiveSheet.PrintOut
  '                   ActiveSheet.PrintPreview 'for debugging
                  End If
              Next cb
  
  
  
  
  
  
  
              
              
          End If
      Else
          MsgBox "All worksheets are empty."
      End If
  
  '   Delete temporary dialog sheet (without a warning)
      Application.DisplayAlerts = False
      PrintDlg.Delete
  
  '   Reactivate original sheet
      CurrentSheet.Activate
  End Sub
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  Report abuse
• • Reply

  5 years ago

  by joe90

  J Alan

  , United Kingdom

  Joined 7 years ago
  Unfortunately I don't have VB6, but I did run your code in excel (VBA) and it seemed fine to me, so I'm not sure what the problem is. Here's the code I used:
  
  Public Sub Command1_Click()
  
      Dim WkSheet As Worksheet
      Set WkSheet = ActiveWorkbook.ActiveSheet
     
      Dim Xl As Excel.Application
      Set X1 = New Excel.Application
     
      Dim CWB As Workbook
      'Set CWB = Workbooks.Add
      'Xl.Workbooks.Open ("D:\PayProject\PrintPaySlips\2007\PaystubJun2007.xls")
      Set CWB = Workbooks.Open("c:\Test.xls")
      CWB.Activate
  
      Dim i1 As Integer
      Dim TopPos As Integer
      Dim SheetCount As Integer
      Dim PrintDlg As DialogSheet
      Dim CurrentSheet As Worksheet
      Dim cb As CheckBox
    
      Application.ScreenUpdating = False
  
  '   Check for protected workbook
      If ActiveWorkbook.ProtectStructure Then
          MsgBox "Workbook is protected.", vbCritical
          Exit Sub
      End If
  
  '   Add a temporary dialog sheet
      Set CurrentSheet = ActiveSheet
      Set PrintDlg = ActiveWorkbook.DialogSheets.Add
  
      SheetCount = 0
  
  '   Add the checkboxes
      TopPos = 40
      For i1 = 1 To ActiveWorkbook.Worksheets.Count
          Set CurrentSheet = ActiveWorkbook.Worksheets(i1)
  '       Skip empty sheets and hidden sheets
          If Application.CountA(CurrentSheet.Cells) <> 0 And _
              CurrentSheet.Visible Then
              SheetCount = SheetCount + 1
              PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
                  PrintDlg.CheckBoxes(SheetCount).Text = _
                      CurrentSheet.Name
              TopPos = TopPos + 13
          End If
      Next i1
  
  '   Move the OK and Cancel buttons
      PrintDlg.Buttons.Left = 240
  
  '   Set dialog height, width, and caption
      With PrintDlg.DialogFrame
          .Height = Application.Max _
              (68, PrintDlg.DialogFrame.Top + TopPos - 34)
          .Width = 230
          .Caption = "Select sheets to print"
      End With
  
  '   Change tab order of OK and Cancel buttons
  '   so the 1st option button will have the focus
      PrintDlg.Buttons("Button 2").BringToFront
      PrintDlg.Buttons("Button 3").BringToFront
  
  '   Display the dialog box
      CurrentSheet.Activate
      Application.ScreenUpdating = True
     
      If SheetCount <> 0 Then
          If PrintDlg.Show Then
                      
              For Each cb In PrintDlg.CheckBoxes
              
                  If cb.Value = xlOn Then
                      Worksheets(cb.Caption).Activate
  '                    ActiveSheet.PrintOut
                     ActiveSheet.PrintPreview 'for debugging
                  End If
              Next cb
                         
          End If
      Else
          MsgBox "All worksheets are empty."
      End If
  
  '   Delete temporary dialog sheet (without a warning)
      Application.DisplayAlerts = False
      PrintDlg.Delete
     
      CWB.Close
  
  '   Reactivate original sheet
  '   CurrentSheet.Activate
      WkSheet.Activate
     
  End Sub
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  Report abuse
• • Reply

  5 years ago

  by radha_telanakula

  Radha

  , India

  Joined 6 years ago
  I know its working good in VBA.
  But if i use this in VB Get error at ForEach statement.
  For Each cb In PrintDlg.CheckBoxes
               
                  If cb.Value = xlOn Then
                      Worksheets(cb.Caption).Activate
                      ActiveSheet.PrintOut
  '                   ActiveSheet.PrintPreview 'for debugging
                  End If
              Next cb
  
  
  
  
  
  
  
  
  
  
  
  Error is
  Runtime error 16 typemismatch
  
             
  
  
  
  
  
  
  Report abuse
• • Reply

  5 years ago

  by radha_telanakula

  Radha

  , India

  Joined 6 years ago
  Help if anybody knows
  
  
  Report abuse
• • Reply

  5 years ago

  by jireh

  Jireh

  , Philippines

  Joined 5 years ago
  just asking...
  
  have you installed an MSDN for your VB? i think the answer is right there. it will help you and idea to debug your program.
  
  
  sory if cant help...
  
  
  
  
  
  
  
  
  
  
  
  Report abuse