Member Avatar

Hi All,

Please help me with this task - this is the only thing stopping me from completing the project.

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click        
        ' Here we go. All the data in the form to an Excel sheet. Cross your fingers...
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object

        'Start a new workbook in Excel- this isn't really what I need though, I need it to open a template and populate it.
        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Open("C:\Users\jagtars\Desktop\HI Resources\HI.xlsx")

        'Add data to cells of the first worksheet in the new workbook
        oSheet = oBook.Worksheets(1)
        oSheet.Range("H5").Value = CustomerName.Text
        oSheet.Range("K5").Value = dropdown_serverVirtualisation.Value
        oSheet.Range("L5").Value = dropdown_desktopVirtualisation.Value
        oSheet.Range("M5").Value = dropdown_oracle.Value
        oSheet.Range("N5").Value = dropdown_sqlServer.Value

        'Save the Workbook and Quit Excel
        MessageBox.Text = "Exported!"
    End Sub
End Class

This works but I have a few issues.

Firstly, I would like to reference the template file within the project folder and secondly I would like a prompt to for SaveAs.

Currently this code open up this files from a folder in my Desktop and saves it as a new file in my Desktop.

How can i modify this code to do the two things I require.

You can reference the folder where the app is running by using the Application.StartupPath property. In .net the SaveFileDialog class is used to save a file. You could also go with a simple InputBox to request the new file name.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.