Interacting with Excel using vb.net

[belenos46]

So, I'm currently trying to open a .xls/.xlsx file, tell it to run a macro to refresh the workbooks, and then save the file as a different file name, thus leaving the original untouched.

Now, technically, it is working, because the last person who held my position wrote a dll to handle the interation. However, it leaves everything declared as an object, which is great up until you have someone who programs option strict on, option explicit on. Then I can't declare my objects at the last minute, and the whole assembly falls apart.

It also sometimes falls apart anyway. He was a sloppy programmer at best.

What I need to do it keep or improve that functionality, but be able to keep my programs option strict on, option explicit on. I'm looking for basically any help here (an actual project that does this would be a great help, naturally, but I haven't found one), up to and including pointers for rewriting the dll so that it complies with my needs.

Thanks for your help, everyone!

[Begginnerdev]

When you say that it refreshes the workbooks, are you meaning it updates data with current data and then saves as a different workbook?

Are you using the original workbook as a template, or as a backup for the previously updated data?

[belenos46]

I am using the initial workbook as a template: it has a macro that pulls data from a variety of sources. That works fine. All I really need to do is activate the macro and save the resulting changes as a new document.

[kRod]

I created a XLXM workbook added a simple macro that open a messagebox and puts the number 2 in B1 cell then saves the workbook as a XLSX file and does not save the 2 in cell B1 of the xlsm file. Here is the code. Hope this helps.

Imports Microsoft.Office.Interop
Imports System.IO



Public Class Form1
  Dim xlApp As Excel.Application
  Dim xlWorkBook As Excel.Workbook
  Dim xlWorkSheet As Excel.Worksheet


  Private Sub releaseObject(ByVal obj As Object)
    Try
      System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
      obj = Nothing
    Catch ex As Exception
      obj = Nothing
    Finally
      GC.Collect()
    End Try
  End Sub



  Private Sub btnMacro_1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMacro_1.Click
    Dim fldr As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments

    xlApp = New Excel.ApplicationClass
    xlApp.DisplayAlerts = False
    xlWorkBook = xlApp.Workbooks.Open(fldr & "\book1.xlsm")
    xlWorkSheet = CType(xlWorkBook.Worksheets(Index:=1), Excel.Worksheet)
    xlApp.Run("Sheet1.RunMe")

    '~~>Save the workbook
    **xlWorkBook.SaveAs(Filename:=fldr & "\BOOK2.xlsx", FileFormat:=51)**

    'xlWorkSheet.SaveAs(fldr & "\TEST.xlsx", FileFormat:=51)

    '~~>Close the WorkBook and DO NOT Save Changes - FALSE
    xlWorkBook.Close(False)

    '~~> Quit the Excel Application
    xlApp.Quit()

    '~~> Clean Up
    releaseObject(xlApp)
    releaseObject(xlWorkBook)



  End Sub


End Class

[belenos46]

Ah, perfect. Thank you, this was just the thing I needed. I should be able to knock the rest of this program out this afternoon.

Many thanks!