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!

4 Years
Discussion Span
Last Post by belenos46

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?


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.


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)
      obj = Nothing
    Catch ex As Exception
      obj = Nothing
    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)

    '~~>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

    '~~> Quit the Excel Application

    '~~> Clean Up

  End Sub

End Class

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!

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.