Print Excel file using VB

vb6 India
  • 10 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






























































































  • 10 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






































































































  • 10 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

               






  • 10 years ago
    Help if anybody knows

  • 10 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...

    Sad [:(]Sad [:(]Sad [:(]








Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“I invented the term Object-Oriented, and I can tell you I did not have C++ in mind.” - Alan Kay