• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

Modifying existing excel document without making it visible.

I'm writing a code in VB.Net that will add data to an excel spreadsheet that already exists.  I need to know how I can use VB.net to call this file up, add the data to the rows/columns it belongs to and close it without the client ever seeing this happen.  Simply giving them a MsgBox saying it completed successfully, or an error message.  

Plus is it possible to add a progress bar during that process of adding data, saving and closing so they can see % wise how long it will take?
0
lkingpinl
Asked:
lkingpinl
  • 7
  • 5
  • 2
1 Solution
 
Mikal613Commented:

set myExcel = new excel.application

myExcel.visible = False 'This sets it not to be visible
0
 
lkingpinlAuthor Commented:
Ok, visibility works.  But then I need to add the data they recorded, save the file, close excel (so it doesn't run in memory anymore) and display a success message box.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
lkingpinlAuthor Commented:
Ok, I've got my code working now to where it will add data and save the document.  It also properly closes excel and clears it from memory.  

Now I just need to display a messagebox asking them if they want to view the result.  If yes, then it opens the excel document in read-only mode, if no, then exit.
0
 
Mikal613Commented:

if msgbox "Do you want to view the document",vbYesNo = vbyes then
'   open file
else
 unload me
end if
0
 
abbdanCommented:
This might be a bit of a round about way of accomplishing what you want to do but I use something similar to achieve the same goal.  You can write some code that does a net send to the users with the success message box.  You can also create an access database and make a live link to an excel spreadsheet range and use user ASP.NET to pipe data to that table.  Net send example:  
set users to receive the message in the list2 listbox and loop through the list

Msage = "C:\winnt\system32\Net Send " & Chr(34) & Trim(List2.List(List2.ListIndex)) & _
    Chr(34) & Chr(32) & Me.Text1.Text
Shell (Msage), vbMinimizedNoFocus

Hope this helps.
0
 
lkingpinlAuthor Commented:
I should clarify that this is in vb.net not vb6
0
 
Mikal613Commented:
when you wite the msgbox make sure its yesno
0
 
lkingpinlAuthor Commented:
I did the msgbox, but when I hit yes, it closes the application.  Here's a snip of my code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        objApp = New Excel.Application
        objApp.Visible = False
        objBook = objApp.Workbooks.Open("K:\RT_DCX\Receivefiles2004_1.xls", Readonly:=False)

        Dim newRow As Integer

        newRow = objBook.ActiveSheet.UsedRange.Rows.Count + 1
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 1) = DateTimePicker1.Value
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 5) = strMethod
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 6) = TextBox1.Text
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 7) = TextBox2.Text
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 8) = strYourName
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 9) = TextBox3.Text
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 10) = strLoc
        objBook.Sheets("RECEIVED FILES").Cells(newRow, 11) = TextBox4.Text

        objBook.SaveAs(Filename:="K:\RT_DCX\Receivefiles2004_1.xls")
        Dim response As MsgBoxResult
        MsgBox("Do you want to view the document?", MsgBoxStyle.YesNo)
        If response = MsgBoxResult.Yes Then
            objApp = New Excel.Application
            objApp.Visible = True
            objBook = objApp.Workbooks.Open("K:\RT_DCX\Receivefiles2004_1.xls", Readonly:=True)
        Else
            End
        End If
        objBook.Close()
        objApp.Quit()

    End Sub

    Private Function closeExcel() As Short
        Dim count As Short = 0
        Dim excelInstance As System.Diagnostics.Process
        Dim excelInstances() As Process = System.Diagnostics.Process.GetProcessesByName("Excel")
        For Each excelInstance In excelInstances
            Try
                excelInstance.Close()      '<-- you can use close or kill .. up to you
                excelInstance.Kill()
                count += 1
            Catch ex As Exception
            End Try
        Next
        Return count                 '<--- this is gonna tell you the # of excel instances closes
    End Function


Any ideas?
0
 
Mikal613Commented:
Dim response As MsgBoxResult
        response  = MsgBox("Do you want to view the document?", MsgBoxStyle.YesNo) <---- assign the value
        If response = MsgBoxResult.Yes Then
            objApp = New Excel.Application
            objApp.Visible = True
            objBook = objApp.Workbooks.Open("K:\RT_DCX\Receivefiles2004_1.xls", Readonly:=True)
        Else
            End
        End If
        objBook.Close()
        objApp.Quit()
0
 
abbdanCommented:
I should have clarified that the code below was intended for vb6 but that the idea behind getting the message to your users of a successfull post is still viable.  The shell command is accessable in vb.net and its syntax is usually visable as soon as you type the word shell in the vb.net editor.  VB.net also makes use of the listbox but really a simple array is all you need if you know who your users are.

Msage = "C:\winnt\system32\Net Send " & Chr(34) & Trim(List2.List(List2.ListIndex)) & _
    Chr(34) & Chr(32) & Me.Text1.Text
Shell (Msage), vbMinimizedNoFocus
0
 
Mikal613Commented:
If MsgBox("Do you want to view the document", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
           objApp = New Excel.Application
            objApp.Visible = True
            objBook = objApp.Workbooks.Open("K:\RT_DCX\Receivefiles2004_1.xls", Readonly:=True)
    Else
                    End
    End If
0
 
lkingpinlAuthor Commented:
Works great thanks!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now