0

Recently my company just performed a server migration and now one of my Excel VBA is not working,

Here's my code snippet:

Dim strSQL  As String, conStr As String
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset

'On Error GoTo exitsub

Path = ActiveWorkbook.Sheets("Options").Cells(1, 6)
conStr = "Provider=Microsoft.ace.OLEDB.12.0;Data Source='" & Path & "';Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"
             
      
strSQL = "SELECT * From [MASTER]"
cnn.Open ConnectionString:=conStr 'stuck at this line
rs.Open strSQL, cnn, adOpenDynamic, adLockReadOnly

The place where I'm stuck at is when its trying to perform the SELECT query. The macro triggers when I save the file but doing so only give me this error:

enter image description here

I've checked my reference for Microsoft ActiveX Data Object, and confirmed that the latest one I have (16.0) is selected.

How do I fix this?

hjh93
  • 570
  • 11
  • 27
  • What path do you have in `ActiveWorkbook.Sheets("Options").Cells(1, 6)`? – FaneDuru Nov 22 '22 at 08:05
  • thats the file path linking to this Excel document. Used to get the file in my connection string, `conStr` in the next line – hjh93 Nov 22 '22 at 08:26
  • This was obvious... Is it updated to the migrated server, with the correct workbook path? Of course, if the path has been changed. – FaneDuru Nov 22 '22 at 08:30
  • yes. what i 1st did was change the server name to the migrated server. even clicked on the link (minus the excel file name) to confirm it took me to the correct file location. – hjh93 Nov 22 '22 at 08:45

1 Answers1

0

Problem

Server-side automation of office is not supported https://support.microsoft.com/en-us/topic/considerations-for-server-side-automation-of-office-48bcfe93-8a89-47f1-0bce-017433ad79e2

Possible workaround

Do it with .Net https://stackoverflow.com/a/52050257/495455

Possible solution/workaround

https://knowledge.informatica.com/s/article/107777?language=en_US

Solution

This is as designed behavior from Microsoft for the Excel driver which currently does not have feature to read from a password protected Excel file, unless it is open.

Workaround

Open the Excel file whose definition is being imported and provide the correct password and try to import the definition from Designer.

Jeremy Thompson
  • 61,933
  • 36
  • 195
  • 321