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.

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

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!

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! ) - time for sleep! - shutting down....

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.

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

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

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.

Re: Help converting VBscript to Visual Basic 2010 express

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!