Help converting VBscript to Visual Basic 2010 express

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 13891 Views - Last Post: 29 March 2011 - 03:06 PM Rate Topic: -----

#1 monkeyiam  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 63
  • Joined: 27-March 11

Help converting VBscript to Visual Basic 2010 express

Posted 27 March 2011 - 11:54 AM

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




Is This A Good Question/Topic? 0
  • +

Replies To: Help converting VBscript to Visual Basic 2010 express

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9388
  • View blog
  • Posts: 35,243
  • Joined: 12-June 08

Re: Help converting VBscript to Visual Basic 2010 express

Posted 27 March 2011 - 11:58 AM

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!
Was This Post Helpful? 1
  • +
  • -

#3 monkeyiam  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 63
  • Joined: 27-March 11

Re: Help converting VBscript to Visual Basic 2010 express

Posted 27 March 2011 - 12:14 PM

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
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9388
  • View blog
  • Posts: 35,243
  • Joined: 12-June 08

Re: Help converting VBscript to Visual Basic 2010 express

Posted 27 March 2011 - 12:30 PM

Not a problem. Just clarifying the lay of the land and getting some encouragement out there!
Was This Post Helpful? 0
  • +
  • -

#5 monkeyiam  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 63
  • Joined: 27-March 11

Re: Help converting VBscript to Visual Basic 2010 express

Posted 27 March 2011 - 02:12 PM

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



Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9388
  • View blog
  • Posts: 35,243
  • Joined: 12-June 08

Re: Help converting VBscript to Visual Basic 2010 express

Posted 27 March 2011 - 02:21 PM

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!
Was This Post Helpful? 0
  • +
  • -

#7 monkeyiam  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 63
  • Joined: 27-March 11

Re: Help converting VBscript to Visual Basic 2010 express

Posted 27 March 2011 - 04:18 PM

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! :o) - 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


Was This Post Helpful? 2
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9388
  • View blog
  • Posts: 35,243
  • Joined: 12-June 08

Re: Help converting VBscript to Visual Basic 2010 express

Posted 27 March 2011 - 06:10 PM

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.
Was This Post Helpful? 0
  • +
  • -

#9 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Help converting VBscript to Visual Basic 2010 express

Posted 27 March 2011 - 10:32 PM

monkey, you get a point from me for taking the trouble to learn what you can on your own. Good job!
Was This Post Helpful? 0
  • +
  • -

#10 monkeyiam  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 63
  • Joined: 27-March 11

Re: Help converting VBscript to Visual Basic 2010 express

Posted 28 March 2011 - 03:49 PM

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. :o)

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! :o)

'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
Was This Post Helpful? 0
  • +
  • -

#11 monkeyiam  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 63
  • Joined: 27-March 11

Re: Help converting VBscript to Visual Basic 2010 express

Posted 28 March 2011 - 05:46 PM

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




Was This Post Helpful? 0
  • +
  • -

#12 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9388
  • View blog
  • Posts: 35,243
  • Joined: 12-June 08

Re: Help converting VBscript to Visual Basic 2010 express

Posted 28 March 2011 - 06:03 PM

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!
Was This Post Helpful? 0
  • +
  • -

#13 _HAWK_  Icon User is online

  • Master(Of Foo)
  • member icon

Reputation: 1057
  • View blog
  • Posts: 4,097
  • Joined: 02-July 08

Re: Help converting VBscript to Visual Basic 2010 express

Posted 28 March 2011 - 07:46 PM

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.
Was This Post Helpful? 0
  • +
  • -

#14 monkeyiam  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 63
  • Joined: 27-March 11

Re: Help converting VBscript to Visual Basic 2010 express

Posted 29 March 2011 - 02:20 PM

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

:bananaman:
Was This Post Helpful? 0
  • +
  • -

#15 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9388
  • View blog
  • Posts: 35,243
  • Joined: 12-June 08

Re: Help converting VBscript to Visual Basic 2010 express

Posted 29 March 2011 - 02:23 PM

Bitch'n. Sell it for mad duckets!
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2