Automating Excel

Posted on 2008-11-04
Last Modified: 2012-06-27

I need to automate Excel from VB.Net. What I need to do, is get data from a DtaBase such access, and populate that data into an excel file. The excel file has to be a template, so it already exists at a certain location on the machine. I am able to retrieve the data and I can populate to excel, but when it is finished, the file is not saved. What I want to do, is populate the template excel, then save it as a different excel file, allowing the user to choose the location and name of the new file. Below is the code I am using to populate excel.
Dim xcApp As New Microsoft.Office.Interop.Excel.Application

        Dim xcWB As Microsoft.Office.Interop.Excel.Workbook = xcApp.Workbooks.Open("C:\Documents and Settings\117152\My Documents\Visual Studio 2008\Projects\ECA\ECA\Documents\Simple.xlt")

        Dim xcWS As Microsoft.Office.Interop.Excel.Worksheet = xcWB.Worksheets("sheet1")

        Dim xcRange As Microsoft.Office.Interop.Excel.Range


Dim ADOConnection As ADODB.Connection

        Dim RS As ADODB.Recordset


        ADOConnection = New ADODB.Connection()

        RS = New ADODB.Recordset()


        ADOConnection.ConnectionString = myConnection



        RS.Open(SQLStr1, ADOConnection)


        While Not RS.EOF

            xcRange = xcWS.Range("A4")

            xcRange.Value = RS.Fields("transMnth").Value & " " & RS.Fields("transYear").Value

            xcRange = xcWS.Range("A5")

            xcRange.Value = RS.Fields("transIncome").Value


        End While





'clean up



        xcApp = Nothing

        xcWB = Nothing

        xcWS = Nothing

        xcRange = Nothing

Open in new window

Question by:0ne-0nly

    Author Comment

    Ok, I have found out that it is savibng the file to the My Documents folder, so what I need now is to know how to save it to a user selected forlder... and also I would like to automatically print it to the default printer while it is being saved. If a printer exists of course, if a printer does not exist then save the file and give the user a message thatthere is no printer.
    LVL 50

    Accepted Solution

    This is how I would do it in Excel - I can't test it on VB.Net, but I suggest that you intergrate the first sub in place of


    You will need to provide a new filename, under the fname variable


    Sub SavePrint()
        Dim myDir As String, fname As String
        myDir = BrowseForFolder
        fname = "yourfile"
        If myDir <> vbNullString Then
            xcWB.SaveAs myDir & "\" & fname
            MsgBox "user did not save file"
        End If
    End Sub
    Function BrowseForFolder(Optional OpenAt As Variant) As Variant
    'Function purpose:  To Browser for a user selected folder.
    'If the "OpenAt" path is provided, open the browser at that directory
    'NOTE:  If invalid, it will open at the Desktop level
        Dim ShellApp As Object
        'Create a file browser window at the default folder
        Set ShellApp = CreateObject("Shell.Application"). _
                       BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
        'Set the folder to that selected.  (On error in case cancelled)
        On Error Resume Next
        BrowseForFolder = ShellApp.self.Path
        On Error GoTo 0
        'Destroy the Shell Application
        Set ShellApp = Nothing
        'Check for invalid or non-entries and send to the Invalid error
        'handler if found
        'Valid selections can begin L: (where L is a letter) or
        '\\ (as in \\servername\sharename.  All others are invalid
        Select Case Mid(BrowseForFolder, 2, 1)
        Case Is = ":"
            If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
        Case Is = "\"
            If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
        Case Else
            GoTo Invalid
        End Select
        Exit Function
        'If it was determined that the selection was invalid, set to False
        BrowseForFolder = False
    End Function

    Open in new window

    LVL 46

    Expert Comment

    by:Wayne Taylor (webtubbs)
    Instead of opening the template, create a new workbook, using the template....

         Dim xcWB As Microsoft.Office.Interop.Excel.Workbook = xcApp.Workbooks.Add("C:\Documents and Settings\117152\My Documents\Visual Studio 2008\Projects\ECA\ECA\Documents\Simple.xlt")

    ...and save the file using something like this....

        Dim sfd As New SaveFileDialog
        sfd.Filter = "Excel Files (*.xls)|*.xls"
        sfd.FileName = "Filename.xls"
        If sfd.ShowDialog = System.Windows.Forms.DialogResult.OK Then
        End If

    Moving the Close statement to this snippet of code enables you to close the workbook without saving if the user clicks cancel on the dialog.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    761 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

    14 Experts available now in Live!

    Get 1:1 Help Now