VB.NET app works on development not on other machines

I have created a small app, that creates a report in Excel then emails the report out.  It works perfectly fine on the machine I developed it on (Win XP Pro). I have used a setup program in the project to build the app and ran the setup to which it worked fine.  When i tried to install it on another machine(win 2000 pro)  The app runs fine and creates the report but then and errror comes up near the end.  It seems to either be when it trys to save the excel spreadsheet or when it tries to email it.  Here is my code and the error, can someone please see where I am going wrong.  Thanks

System.NullReferenceException: Object reference not set to an instance of an object.
   at Excel.WorkbookClass.SaveAs(Object Filename, Object FileFormat, Object Password, Object WriteResPassword, Object ReadOnlyRecommended, Object CreateBackup, XlSaveAsAccessMode AccessMode, Object ConflictResolution, Object AddToMru, Object TextCodepage, Object TextVisualLayout, Object Local)
   at Daily_Attendance_Report.Form1.Form1_Load(Object sender, EventArgs e)
   at System.Windows.Forms.Form.OnLoad(EventArgs e)
   at System.Windows.Forms.Form.OnCreateControl()
   at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
   at System.Windows.Forms.Control.CreateControl()
   at System.Windows.Forms.Control.WmShowWindow(Message& m)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ContainerControl.WndProc(Message& m)
   at System.Windows.Forms.Form.WmShowWindow(Message& m)
   at System.Windows.Forms.Form.WndProc(Message& m)
   at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


        xlApp.Application.Workbooks(1).SaveAs(filename)

        xlApp.Application.Quit()

        Call SendFile(filename, searchDate, reportTime)

End Sub

    Public Sub SendFile(ByVal sFile As String, ByVal reportDate As Date, ByVal reportTime As DateTime)

        Dim sFileLocation, sSubject, sBody, sRecipient, sCCrecipient As String
        Dim appOutlook, mitItem, olMailItem As Object

        sFileLocation = sFile
        sSubject = "Daily Attendance Report " & reportDate & " At " & reportTime
        sBody = "Please find requested document attached"
        appOutlook = CreateObject("Outlook.Application")
        mitItem = appOutlook.CreateItem(olMailItem)
        sRecipient = "test@test.com"
        sCCrecipient = "test@test.com"

        With mitItem
            .to = sRecipient
            .cc = sCCrecipient
            .Subject = sSubject
            .body = sBody
            .Attachments.Add(sFileLocation)
            .send()
        End With

        mitItem = Nothing
        appOutlook = Nothing

        Dim st As String
        Dim x, cols As Integer

    End Sub

dkilbyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

the-edgeCommented:
maybe Escel is not installed in the other machine (win 2000). try to run the app from another pc with MS Excel installed

the edge
dkilbyAuthor Commented:
Excel is installed on the other machine, I can see the report being generated no problem at all.  the problem seems to happen when it is either trying to save the report or trying to email it, I am not sure which one.
bruintjeCommented:
if it's in the send action, it could be the security patch that's installed in outlook and when you try to automate a mail message it doesn't allow it resulting in an error

but that's just a guess, since you have it working on your development machine without problems

can you insert a try finally block to catch the error?
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

dkilbyAuthor Commented:
I don't believe it is outlook, i have several other programs written with VB 6.0 that send emails and they all work no problem with sending the emails.

"can you insert a try finally block to catch the error?"  What do you mean by this, I am new to VB .NET, and how do I do this?

Thanks
the-edgeCommented:
if you can install vs.net in that machine, you can see at which line vb break execution, if you cannot then try to add try-catch expression to your app and put a messagebox within the catch expression that show the line it stop, something like this:

Try
        xlApp.Application.Workbooks(1).SaveAs(filename)
Catch err As Exception
        MessageBox.Show("Save File")
End Try

        xlApp.Application.Quit()

Try
        Call SendFile(filename, searchDate, reportTime)
Catch err As Exception
        MessageBox.Show("Send File")
End Try

....

so you can understand where the bug is...

the edge
dkilbyAuthor Commented:
Message Box with Save File popped up.  So it seems to be the saving of the file.
the-edgeCommented:
so the problem maybe is the filename string or the permission rigths to create file.
try to add thi second messagebox:

Try
        xlApp.Application.Workbooks(1).SaveAs(filename)
Catch err As Exception
        MessageBox.Show(filename)
End Try

and write here the string that the messagebox show.
maybe the path is wrong...

the edge
BC8Commented:
Is the version of Excel the same on the development machine and the other machine?
dkilbyAuthor Commented:
I am not sure, it may not be.  I think development is 2002 and other machine 2000.
dkilbyAuthor Commented:
name of fileName = c:/attendReport/attendance_2003_10_27_2pm.xls
the-edgeCommented:
i've tried to reproduce the error in my machine and i've discovered that the execution stop because the folder "attendReport" doesn't exist in my HD. i've created that folder, then tried again and it worked fine, the file was created.
check the existence of that folder in your win200 machine.

the edge
BC8Commented:
This could be the source of your problem.  On the development machine, you add a reference to Excel.  The version of the reference is 10.  However, on the other machine, the version is 9.  Your app is looking for 10 but can only find 9.

When referencing Excel, I always compile on another machine which has a previous version of Excel installed (2000).  That way my app will work with 2000 and newer.  That is my 'work around'.  I'm sure there is an easier way, but I have never had the time to investigate.
the-edgeCommented:
dkilbi note that when you declare variables in sendfile sub:

        Dim sFileLocation, sSubject, sBody, sRecipient, sCCrecipient As String
        Dim appOutlook, mitItem, olMailItem As Object

only sCCrecipient is declared as string and olMailItem as object, all others variable as declared with not specified type.
the rigth variables declaration is:
 
        Dim sFileLocation As String, sSubject As String, sBody As String, sRecipient As String, sCCrecipient As String
        Dim appOutlook As Object, mitItem As Object, olMailItem As Object


the edge
dkilbyAuthor Commented:
okay, I ran the app again but removed the line:

        xlApp.Application.Workbooks(1).SaveAs(filename)

I had also tired this :

        xlSheet.SaveAs(filename)

The app failed both times at those lines,  I than removed those lines and saved and put an excel spreadsheet in the attendReport folder with the name of the file that was usually being saved.  When I ran the app, it generated the report just fine, and then sent the one I had put in the folder just fine.  The problem with the code comes down to the one line,  I have a folder with the correct name, the report generates completely fine so surely if it was a problem with the versions of excel it wouldn't complete it.  I can not compile the app on machine with Excel 2000, is there anything else I can do to save the excel spreadsheet.

Thanks
the-edgeCommented:
try to copy the EXCEL9.OLB file from the win 2000 machine (it's located in the Office Folder) to your develop machine in your project folder, remove the reference to Microsoft Excel 10 Object library and add a reference to the excel9.olb file you have copied.
i can't try if it works 'cause i have only excel 2000.
maybe it works, maybe you need to copy also the MSO9.dll from your win2000 machine.
let me know if it works
maybe you need to register both excel9.olb and mso9.dll in your develop machine

also try to specify the workbook format before save setting the property  xlapp.Application.Workbooks(1).FileFormat

the edge

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dkilbyAuthor Commented:
I copied over the excel9.olb file and try referencing to it, but it keeps using the 10.0, if i register the ms09.dll will that mess things up.

I tried the app on a different win 2000 machine with Excel 2002 and it worked so this seems to be the issue, now I just need to figure out how to fix it.

thanks
the-edgeCommented:
did you remove the reference to Microsoft Excel 10 Object library before add the reference to excel9.olb?
dkilbyAuthor Commented:
Yes, it would not allow me to reference excel9.olb until I removed the reference to 10.0

It did not show up in the window where the 10.0 was I had to browse and select the file excel9.olb.
the-edgeCommented:
when you install the app in win2000 the installer copy also the excel10.old and the mso10.dll files (i suppose they have this name in office 2002)?

if the answer is 'no' try to copy these files manually in the app path and, if it needs, register mso10.dll.
the-edgeCommented:
dkilbyAuthor Commented:
I think i figured out a way.  I changed the save to :

xlApp.Application.Workbooks(1).SaveCopyAs(filename)

and it worked.  Thanks for all your help.

anurag_enthoughtCommented:
dkilby thanks for the excellent solution. I found that
      oExcelWorkbook.Close(true,excelFilename,Type.Missing);
also works.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.