Solved

VB.NET app works on development not on other machines

Posted on 2003-10-27
22
1,349 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
ID: 9632056
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
ID: 9632065
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
ID: 9632091
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
ID: 9632103
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
ID: 9632110
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
ID: 9632146
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
ID: 9632169
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
ID: 9632199
Is the version of Excel the same on the development machine and the other machine?
0
 

Author Comment

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

Author Comment

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

Expert Comment

by:the-edge
ID: 9632315
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Expert Comment

by:BC8
ID: 9632320
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
ID: 9633334
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
ID: 9634243
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
ID: 9634452
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
ID: 9636183
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
ID: 9637826
did you remove the reference to Microsoft Excel 10 Object library before add the reference to excel9.olb?
0
 

Author Comment

by:dkilby
ID: 9637832
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
ID: 9637908
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
ID: 9637921
0
 

Author Comment

by:dkilby
ID: 9638147
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
ID: 10498277
dkilby thanks for the excellent solution. I found that
      oExcelWorkbook.Close(true,excelFilename,Type.Missing);
also works.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

930 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

9 Experts available now in Live!

Get 1:1 Help Now