I need some help. I have a workbook (A) and i want open another workbook (B) and run a macro that is inside B. Workbook (B) have a macro that make a sql connection to another workbook (C) to extract data, when i run this macro in the workbook (B) work fine, but if i open the workbook (B) from workbook (A) and run the macro i get a message from Windows “The file is open” (WorkBooK (C)). How can I skip this message.
I get the error when open the connection data.
The code to open the Workbook(B) is :
Sub OpenWorkBookandRun()
tarPath = "..............\"
strfilename = "..........xlsm"
Application.Workbooks.Open tarPath & strfilename
'Run Macro
Workbooks(strfilename).Activate
Application.Run "'................'!Module1.Macro"
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
And the code in the workbook (B) to open data origin is:
' Establezco los parametros de la conexion ADO, el RecordSet y la Cadena de conexion
Set oCONN = New ADODB.Connection
Set oRS = New ADODB.Recordset
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & OrigDatos & ";Extended Properties=""Excel 12.0;HDR=Yes;"";"
' Abro la conexion y ejecuto la sentencia SQL
If Not (oCONN Is Nothing) Then
If (oCONN.State And adStateOpen) = adStateOpen Then oCONN.Close
End If
oCONN.Open strConn <--- Line where i get the error
oRS.Open strSQL, oCONN, 1, 3
Note. Sometimes in the fisrt workbook I get the same messages, but only sometimes and I don’t know why because anybody has the origin of data open
Are the workbook (C) already open when you run the macro from Workbook (A)? Other question is, do you need to write something in Workbook (C)? If you don't you can use a parameter in you connection string to connect with readonly, like this:
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Data Source=" & OrigDatos & ";Extended Properties=""Excel 12.0;HDR=Yes;""
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments