Export Strings to an Excel File

Question

Textbox1.Text >> "A1"

My goal is to open an existing excel spreadsheet, insert specific strings into specific cells on the spreadsheet, then print it out. As an added bonus, if anyone knows how I could code the program to also open this spreadsheet for the user to view, that would be really useful as well.

Thanks!

What code do you have so far? There are many different ways to accomplish what you intend — rwisch45, Jul 23 '13 at 17:00
Right now I just have a lot of textboxes that have text in them and I want to send that text to specific cells in excel. Ideally if I could do it with an ADODB connection that would be great since I am already using that, but otherwise I don't really care how its done, I just want a method. — Kevin Kesicki, Jul 23 '13 at 17:09
Possibly. I would rather just be able to pass those strings directly into excel though. — Kevin Kesicki, Jul 23 '13 at 18:32

rwisch45 · Answer 1 · 2013-07-23T19:40:20.600

0

You can use an OLEDB connection to update the spreadsheet. Then you could do the following to print it:

    Dim strFile As String = "c:\test.xls"
    Dim objProcess As New System.Diagnostics.ProcessStartInfo


    With objProcess
        .FileName = strFile
        .WindowStyle = ProcessWindowStyle.Hidden
        .Verb = "print"

        .CreateNoWindow = True
        .UseShellExecute = True
    End With
    Try
        System.Diagnostics.Process.Start(objProcess)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

After it is printed you could do a simple System.Diagnostics.Process.Start("c:\test.xls") to open the workbook for the user to view.

Alternatively, you could use an OLEDB connection to update the spreadsheet. Then you could open it via interop with something like this. You can also input the strings into the workbook at the same time (and not use the OLEDB connection). Add a reference to Microsoft.Office.Interop.Excel.

Imports Microsoft.Office.Interop
Public Class MyExcel
   Private Sub StartExcel()
     Dim xlApp As New Excel.Application
     xlApp.Visible = True
     Dim xlBook As Excel.Workbook = xlApp.Workbooks.Open("c:\test.xlsx")
     Dim xlSheet As Excel.Worksheet = xlBook.ActiveSheet
     Dim xlSheet2 As Excel.Worksheet = xlBook.Worksheets("Sheet 3")
     xlSheet2.Range("B5").Value2 = "my new string"
     xlSheet.Range("A1").Value2 = "my string"
     xlBook.PrintPreview()
   End Sub
End Class

edited Jul 23 '13 at 19:40

answered Jul 23 '13 at 17:20

rwisch45

3,692
2
25
36

The Interop version looks pretty slick but I would guess it would be hard to make bullet proof. Take a look at this thread: http://stackoverflow.com/questions/17781708/why-is-killing-the-excel-process-a-bad-thing#comment25958657_17781708 – rheitzman Jul 23 '13 at 17:43
@rheitzman That is a very valid point concerning the proper closing of the application. In this particular case though, it sounds like he wants to open the workbook for the user. The code I posted above will do just that and let the user exit the workbook/Excel like they normally would. – rwisch45 Jul 23 '13 at 17:45
It almost worked, except it said Excel.Application is not defined. It didn't work for both Excel references. Any idea what happened? – Kevin Kesicki Jul 23 '13 at 19:31
@KevinKesicki Did you add the `Imports Microsoft.Office.Interop` above your class? Did you add the reference to Microsoft.Office.Interop.Excel? For interop to work, Excel must be intalled on the target computer as well. – rwisch45 Jul 23 '13 at 19:35
@KevinKesicki My apologies. Add a `xlApp.Visible = True` line beneath the declaration of the new Excel applicaiont. I've edited my answer to include that. – rwisch45 Jul 23 '13 at 19:41
@rwisch45 thanks for your help so far, but it is still giving me trouble. Now it is telling me "'New' cannot be used on an interface." Any clue what the trouble might be? – Kevin Kesicki Jul 24 '13 at 12:25
@KevinKesicki Make sure you are only using 'New' when declaring the Excel application and not for the workbook or worksheet(s) – rwisch45 Jul 24 '13 at 12:42

score 0 · Answer 2 · answered Jul 23 '13 at 19:30

This may seem a bit crude but... It might help you. You can simply create a text file with an .XLS extension. You can put text in the file where Tab characters are recognized as column separators in Excel and Carriage Returns are recognized as row separators in Excel

ColA Row1   ColB Row1   ColC Row1
ColA Row2   ColB Row2   ColC Row2

In the example above, replace the spaces between the sections with Tabs, save it to your hard drive with an .XLS extension, then open in Excel, you'll see the text in separate columns and rows

score 0 · Answer 3 · answered Jul 24 '13 at 00:12

Using Excel automation is easy, but is recommended only for situations that are user-attended and user-interactive, as automated Excel can still display all sorts of modal dialogs requiring user intervention. If the application isn't supposed to be user-interactive, it would be better to use something like the Open XML SDK for Office, or a third party product (e.g. Aspose.Cells, which I use myself), which would run faster and not be prone to halting for user attention. In server/user-unattended applications, Microsoft specifically recommends that automation of Office applications not be done, and that Office application automation will not be supported, and could breach licensing requirements in these situations.

Correct on all accounts, but I think the OP is deploying this in a user attended environment — rwisch45, Jul 24 '13 at 13:29

Export Strings to an Excel File

3 Answers3