Manipulate a file in code (VB.NET) without executing the file's macros

Question

I have an Excel file that has a bunch of VBA and macro code in it. When I open the file in Excel I can choose not to 'enable' them - so the values in the fields all stay as they were during the last save. I need to manipulate the values as they were last saved - so I don't want the macros (which look at the current date and update values accordingly) to run.

When I open it via our dot net code:

Dim oxlRep As Excel.Application
Dim oWBRep As Excel.Workbook
Dim oSheetRep As Excel.Worksheet
Dim oRngRep As Excel.Range
oxlRep.Open(path)

the vb code runs - throwing off the values. I've been looking for a way to open it without macros, or in 'secure' mode where the macros aren't run. If I simply double click the file and don't choose to enable macros the values are all there as I want them.

Usually we run this code within the month that the files are created, so we haven't seen this problem in the 3 or 4 years that it has been working. Now I need to go back to some of the old files and run some archival code...

Anyone have a suggestion?

Answer 1

Application.AutomationSecurity = msoAutomationSecurity.msoAutomationSecurityForceDisable

Try opening the workbook after this statement. I think, this will disable macros at Application Level (not at workbook level)

Hope that helps.

Answer 2

Is ADO any use to you? I can only give a script example, i'm afraid.

strLinkFile = "C:\Docs\LTD.xls"

Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=" & strLinkFile & ";" & _
       "Extended Properties=""Excel 8.0;HDR=YES;"""

Set rs = CreateObject("ADODB.Recordset")
rs.Open "Select * from [Sheet1$A1:B5]", cn, adOpenStatic