asked on

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

the-edge

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

dkilby

ASKER

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.

Brian Mulder

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?

dkilby

ASKER

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-edge

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

dkilby

ASKER

Message Box with Save File popped up. So it seems to be the saving of the file.

the-edge

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

BC8

Is the version of Excel the same on the development machine and the other machine?

dkilby

ASKER

I am not sure, it may not be. I think development is 2002 and other machine 2000.

dkilby

ASKER

name of fileName = c:/attendReport/attendance_2003_10_27_2pm.xls

the-edge

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

BC8

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-edge

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

dkilby

ASKER

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

ASKER CERTIFIED SOLUTION

the-edge

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dkilby

ASKER

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-edge

did you remove the reference to Microsoft Excel 10 Object library before add the reference to excel9.olb?

dkilby

ASKER

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-edge

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-edge

also take a look at:

http://www-level3.experts-exchange.com/questions/20705091/Changing-references-by-code.html

maybe there is the answer

dkilby

ASKER

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_enthought

dkilby thanks for the excellent solution. I found that
oExcelWorkbook.Close(true,excelFilename,Type.Missing);
also works.