Solved

VB.NET app works on development not on other machines

Posted on 2003-10-27
22
1,345 Views
Last Modified: 2007-12-19
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

0
Comment
Question by:dkilby
  • 9
  • 9
  • 2
  • +2
22 Comments
 
LVL 3

Expert Comment

by:the-edge
Comment Utility
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
0
 

Author Comment

by:dkilby
Comment Utility
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.
0
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
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?
0
 

Author Comment

by:dkilby
Comment Utility
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
0
 
LVL 3

Expert Comment

by:the-edge
Comment Utility
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
0
 

Author Comment

by:dkilby
Comment Utility
Message Box with Save File popped up.  So it seems to be the saving of the file.
0
 
LVL 3

Expert Comment

by:the-edge
Comment Utility
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
0
 

Expert Comment

by:BC8
Comment Utility
Is the version of Excel the same on the development machine and the other machine?
0
 

Author Comment

by:dkilby
Comment Utility
I am not sure, it may not be.  I think development is 2002 and other machine 2000.
0
 

Author Comment

by:dkilby
Comment Utility
name of fileName = c:/attendReport/attendance_2003_10_27_2pm.xls
0
 
LVL 3

Expert Comment

by:the-edge
Comment Utility
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
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Expert Comment

by:BC8
Comment Utility
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.
0
 
LVL 3

Expert Comment

by:the-edge
Comment Utility
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
0
 

Author Comment

by:dkilby
Comment Utility
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
0
 
LVL 3

Accepted Solution

by:
the-edge earned 500 total points
Comment Utility
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
0
 

Author Comment

by:dkilby
Comment Utility
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
0
 
LVL 3

Expert Comment

by:the-edge
Comment Utility
did you remove the reference to Microsoft Excel 10 Object library before add the reference to excel9.olb?
0
 

Author Comment

by:dkilby
Comment Utility
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.
0
 
LVL 3

Expert Comment

by:the-edge
Comment Utility
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.
0
 
LVL 3

Expert Comment

by:the-edge
Comment Utility
0
 

Author Comment

by:dkilby
Comment Utility
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.

0
 

Expert Comment

by:anurag_enthought
Comment Utility
dkilby thanks for the excellent solution. I found that
      oExcelWorkbook.Close(true,excelFilename,Type.Missing);
also works.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now