[monkeyiam]

Hi,

I want to convert the following VBscript into Visual Basic 2010 express so I make a graphical interface for it. Any help is much appreciated. This script basically runs through excel workbooks in a folder and prints out worksheet 3 of each workbook.

'~~> Folder where the Excel Files are.
Const strSourcePath = "C:\test"
'~~> Path for the log File
Const LogPath = "C:\test\"
'~~> Name of the Log File
Const strFile = "Log.txt"

Dim objFSO, objExcel, objWorkbook, objSheet
Dim objFolder, colFiles, objFile, a

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
Set objFolder = objFSO.GetFolder(strSourcePath)
Set colFiles = objFolder.Files
Set a = objFSO.CreateTextFile(LogPath & strFile,True)

For Each objFile In colFiles
    Set objWorkbook = objExcel.Workbooks.Open(objFile)
    Set objSheet = objExcel.Workbooks(1).Worksheets(3)
    a.WriteLine(objWorkbook.name & " Printed at " & time)
    '~~> Print to Default Printer
    objSheet.PrintOut
    objWorkbook.Close False
Next

a.Close

Set a = Nothing
Set objSheet = Nothing
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing

[modi123_1]

Okay how far along have you gotten? Are you stuck at a line of code? Have you debugged through it? Just say'n - DIC is not a code writing (or script converting) site. You can totally do this!

[monkeyiam]

Hi,

Sorry for being rude. your responce is fair. I am new to programming, so forgive me. I will try and do what I can and then report back when I get stuck.

Cheers

[modi123_1]

Not a problem. Just clarifying the lay of the land and getting some encouragement out there!

[monkeyiam]

Okay, went to MSN and fried my brain a bit and actually quite happy that I ahve managed to print out a specific worksheet within a workbook! So that's one problem done. I would appreciate some help with looping through all workbooks in a folder and printing out sheet 5 if it is present. I am just not sure where to even begin. So, just to clarify, I have a folder which contains around 30 workbooks. In each workbook are around 15 worksheets. I need to loop through each workbook and print off sheet 5.Tia

Imports Microsoft.Office.Interop.Excel



Public Class Form1



    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim objexcel As New Application
        Dim objworkbook As Workbook
        Dim objsheet As Worksheet

        objexcel.Visible = False


        objworkbook = objexcel.Workbooks.Open("c:\print\myfile.xls")

        objsheet = objworkbook.Worksheets("sheet 5")

        objsheet.PrintOutEx()
    End Sub

[modi123_1]

Kudos to the work so far!

Break your problem down in steps.

1. Finding files in a folder.
2. Opening said files.
3. Finding specific page.
4. If there print it.
5. If not move on.

The first can be accomplished by checking out the methods in the System.Io.DirectoryInfo. Say the "GetFiles" method that gets a list of all the files in a directory.
http://msdn.microsof...ectoryinfo.aspx

Sweet - now use a for-loop to cycle through all the files there. Bam! You now can access them!

I think you can hook that into the rest of the code you have nicely!

[monkeyiam]

Sensei,

Tonight has been a good learning curve for me. You have given me the courage to do what I though I couldn't do. Thanks to your guidence, I have surely learned more than I would have if you had simply given me the code!
I get squiggly green lines on 2 variables but the code seems to run well:

Variable 'objworkbook' is used before it has been assigned a value. A null reference exception could result at runtime.
Variable 'objsheet' is used before it has been assigned a value. A null reference exception could result at runtime.

I had trouble shutting down the Excel process but found a good artical on MSN which resolved it.

Next to produce a log file and then it's on to the fun Graphical design stuff!

Thanks again for your much needed input and for enforcing the lay of the land! ) - time for sleep! - shutting down....

 Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim di As New DirectoryInfo("C:\print")
        Dim fiarr As FileInfo() = di.GetFiles()
        Dim fri As FileInfo

        Dim objexcel As New Application
        Dim objworkbook As Workbook
        Dim objsheet As Worksheet

        objexcel.Visible = False

        For Each fri In fiarr

            objworkbook = objexcel.Workbooks.Open("c:\print\" & fri.Name)

            On Error GoTo skip
            objsheet = objworkbook.Worksheets("Week 5")



            objsheet.PrintPreview()

skip:
        Next

        objworkbook.Close()



        releaseObject(objsheet)
        releaseObject(objworkbook)
        releaseObject(objexcel)

    End Sub
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

[modi123_1]

Kudos on the self reliance and confidence!

Regarding:

Quote

Variable 'objworkbook' is used before it has been assigned a value. A null reference exception could result at runtime.
Variable 'objsheet' is used before it has been assigned a value. A null reference exception could result at runtime.

This is the compiler warning you that if you try and access these objects before they are instantiated, or initialized, shit could blow up. Basically you either assign another instantiated object to them *or* use the 'new' like you did in line six.

If an object is null or nothing then it points to no memory space. Things crash and your app doesn't work.

[BobRodes]

monkey, you get a point from me for taking the trouble to learn what you can on your own. Good job!

[monkeyiam]

Thanks for the point Bob! and thanks for the tips Mod.

I got around the object null situ by doing this, Dim objworkbook As Workbook = Nothing

When I added the folderBrowserDialog it had issues with the Microsoft.Office.Interop.Excel namespace so I had to rem it out and manually insert it. See code. It works but I wasn't sure how else to do this.

Well, very happy with my finished product. really running out of time now so could do with some help to put this baby to bed. )

I have added a progress bar which increments through each file. It would look better if I could first determine how many files there are to start with and automatically adjust the bar accordingling to give a realisted guide of when the process is over.

Finally, i have noticed that it skips files where the worksheet says for example week5 rather than week 5 or some other spelling mistake. To resolve this I would like to keep track of what files have been skipped and then show on screen at the end.

Listen, due to being "zone out" in code, I burnt the bloody potatoes this evening and had crispy steak. I'm in the dog house so throw me a bone! )

'Imports Microsoft.Office.Interop.Excel
Imports System.IO.DirectoryInfo
Imports System.IO

Public Class Form1


    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim di As New DirectoryInfo(TXTDir.Text)
        Dim fiarr As FileInfo() = di.GetFiles()
        Dim fri As FileInfo
        Dim filecount As Integer = 0

        Dim objexcel As New Microsoft.Office.Interop.Excel.Application
        Dim objworkbook As Microsoft.Office.Interop.Excel.Workbook = Nothing
        Dim objsheet As Microsoft.Office.Interop.Excel.Worksheet = Nothing

        objexcel.Visible = False

        For Each fri In fiarr

            Label6.Text = fri.Name
            ProgressBar1.Increment(1)
            objworkbook = objexcel.Workbooks.Open(TXTDir.Text & fri.Name)



            On Error GoTo skip
            objsheet = objworkbook.Worksheets(CBWsheet.SelectedItem)



            'objsheet.PrintPreview()

skip:
        Next

        objworkbook.Close()



        releaseObject(objsheet)
        releaseObject(objworkbook)
        releaseObject(objexcel)
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Try
            With FolderBrowserDialog1

                .RootFolder = Environment.SpecialFolder.Desktop

                .Description = "Select Folder."

                .ShowNewFolderButton = False

                If .ShowDialog = Windows.Forms.DialogResult.OK Then


                    'ListBox1.Items.Add(.SelectedPath)
                    TXTDir.Text = .SelectedPath & "\"

                End If

            End With
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, Me.Text)
        End Try


    End Sub

    Private Sub CBWsheet_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CBWsheet.SelectedIndexChanged
        Label4.Text = CBWsheet.SelectedItem
    End Sub
End Class

TIA

[monkeyiam]

Ok, I had a bash at it and almost have it sorted. Just struggling with tracking skipped files. I have created a second form called summary to display some stats. Printed stats are displayed ok,and skipped stats are displaying the same info as printed, which is not what I want.

Module Module1
    Public filecount
    Public SkipFile
    Public LBPrSuc(50)
    Public LBPrNeg(50)
End Module

    Private Sub Form2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Populate sucessfull listbox with filenames that have been printed
        Label2.Text = filecount
        For t = 1 To filecount
            ListBoxSuc.Items.Add(LBPrSuc(t))
        Next
        'Populate unsucessfull listbox with filenames that have been printed
        Label3.Text = SkipFile
        For t = 1 To SkipFile
            ListBoxNeg.Items.Add(LBPrNeg(t))
        Next

    End Sub

Here is the snippet of code to show where I'm going wrong. I use on error goto skip when an error ocurs. As the "code to track names that did not print" is still between the loop it is flawed. I'm now confused as to where to put it. Need expert advice.

            On Error GoTo skip
            LBPrSuc(filecount) = fri.Name ' Array to hold file names of sucessfully printed files
            objsheet = objworkbook.Worksheets(CBWsheet.SelectedItem)



            'objsheet.PrintPreview()

skip:

            SkipFile = SkipFile + 1
            LblSkipped.Text = SkipFile
            LBPrNeg(SkipFile) = fri.Name ' Array to hold file names of unsucessfully printed files

        Next

        objworkbook.Close()



        releaseObject(objsheet)
        releaseObject(objworkbook)
        releaseObject(objexcel)
        Me.Visible = False

        Form2.Visible = True
    End Sub

[modi123_1]

Quote

It would look better if I could first determine how many files there are to start with and automatically adjust the bar accordingling to give a realisted guide of when the process is over.

Perhaps the standard count on the collection?

Dim foo As New IO.DirectoryInfo("path")
foo.GetFiles.Count

Quote

Finally, i have noticed that it skips files where the worksheet says for example week5 rather than week 5 or some other spelling mistake

Avoid goto's.. they are unslightly.

Why not just have a try/catch dumped in there. If an exception occurs increment a counter... or hell add the file name to a list of strings! then you know how many (you can get the .count on the list) and which ones!

Loose example:

  Dim objworkbook As Microsoft.Office.Interop.Excel.Workbook = Nothing
        Dim objsheet As Microsoft.Office.Interop.Excel.Worksheet = Nothing

        Dim foo As New IO.DirectoryInfo("")
        foo.GetFiles.Count()

        Dim sSkippedFiles As New List(Of String)

        For Each temp As IO.FileInfo In foo.GetFiles
            Dim objexcel As New Microsoft.Office.Interop.Excel.Application
            Try
                objworkbook = objexcel.Workbooks.Open("")
            Catch ex As Exception
                sSkippedFiles.Add(temp.Name)
            End Try
        Next

        Console.WriteLine(String.Format("how many skipped? {0}", sSkippedFiles.Count.ToString))
        For Each temp As String In sSkippedFiles
            Console.WriteLine(String.Format("skipped file: {0}", temp))
        Next

That last post - I am not sure what you are using for variables. Explicitly define them!

[_HAWK_]

If you ever find excel still open in task manager, try

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)

instead of

System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

Word and Excel both can be hard to end their process, but this is a sure fire way.

[monkeyiam]

Thanks people for all your support. I have now finished my first project!!!!!!!!!!!

[modi123_1]

Bitch'n. Sell it for mad duckets!