none
ses RRS feed

  • Question

  • I'm new to visual basic but an old mainframe programmer, I just wrote a little program with visual studio 2013 express visual basic where I'm reading as ASCII file and creating and writing a text file out of it, now I need to write the same file to Excel and SQL, but  I want to do it from the same program so I do not have to go create the Excel worksheet and the SQL database manually to then write to them, I would like to create them from my program if they do not exist and update them if they already exits on the directory. I don't know enough and don't find any sample codes online for VSTO 2013 express and Excel 2007 which is what I'm using, I read that with late binding I may be able to work with the Excel version I have, Can any one help?
    • Moved by Tina-Shi Tuesday, March 17, 2015 3:23 AM the issue is related to the excel
    Monday, March 16, 2015 1:18 AM

Answers

  • Hi m2intlc,

    >>now I need to write the same file to Excel and SQL

    Based on my understanding, there are many ways to write data into a sreadsheet like ADO.Net, Excel automation, Open XML. The solution depends on your requriement.

    >> I would like to create them from my program if they do not exist and update them if they already exits on the directory

    If you want to create workbook, we can use Excel object model to automate Excel application. And here is an example that create a workbook at specific folder path and write "Hello Word!" to cell A1:

      Dim ExcelApp As New Application
            ExcelApp.Visible = True
            Dim aWorkBook As Workbook
            aWorkBook = ExcelApp.Workbooks.Add
           aWorkBook.Worksheets("Sheet1").Range("A1") = "Hello Word!"
            aWorkBook.SaveAs("C:\Users\UserName\Desktop\book1.xlsx", XlFileFormat.xlOpenXMLWorkbook)
            ExcelApp.Quit()

    And also here is an helpful article about automate Excel using VB.Net for your reference:
    How to automate Microsoft Excel from Visual Basic

    If you want to user ADO.Net to read/write data from/into database, I  suggest that you can get more effective response from ADO.NET Managed Providers forum.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, March 19, 2015 2:10 AM
    Moderator

All replies

  • Hi m2intllc,

    Thank you for posting in MSDN.

    Since this issue is related to the Excel, so we will move this case to this Excel for Developer forum:

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev , you will get better support.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 17, 2015 3:22 AM
  • Hi Tina,

    thanks for looking into my question, however the issue is related to Visual Basic in Visual Studio 2013 Express and how to create and write an Excel spread sheet/workbook from it.

    Hope you can help post the question in the right forum

    Thanks

    Tuesday, March 17, 2015 1:39 PM
  • Hi m2intlc,

    >>now I need to write the same file to Excel and SQL

    Based on my understanding, there are many ways to write data into a sreadsheet like ADO.Net, Excel automation, Open XML. The solution depends on your requriement.

    >> I would like to create them from my program if they do not exist and update them if they already exits on the directory

    If you want to create workbook, we can use Excel object model to automate Excel application. And here is an example that create a workbook at specific folder path and write "Hello Word!" to cell A1:

      Dim ExcelApp As New Application
            ExcelApp.Visible = True
            Dim aWorkBook As Workbook
            aWorkBook = ExcelApp.Workbooks.Add
           aWorkBook.Worksheets("Sheet1").Range("A1") = "Hello Word!"
            aWorkBook.SaveAs("C:\Users\UserName\Desktop\book1.xlsx", XlFileFormat.xlOpenXMLWorkbook)
            ExcelApp.Quit()

    And also here is an helpful article about automate Excel using VB.Net for your reference:
    How to automate Microsoft Excel from Visual Basic

    If you want to user ADO.Net to read/write data from/into database, I  suggest that you can get more effective response from ADO.NET Managed Providers forum.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, March 19, 2015 2:10 AM
    Moderator