Bryce Bassett
asked on
Reading from Excel sheet using ADODB, works on one worksheet but not another?
In most of the Word and PowerPoint toolbars I develop using VBA, I use an Excel file to hold various kinds of data, which I read in using ADODB. I've used this method successfully for years.
I just updated a PowerPoint tool for one client, and now one person out of a team of four is experiencing an error message in the middle of that ADODB loading sequence. The weird thing is that only this one person is seeing the error. It works fine for me and for the other 3 people on his team. They are all running Office 2013. I know it is successfully creating the connection to the Excel file, and have verified that it correctly reads the data from the first worksheet and loads it into the first public variable, but when it tries to move on to the next worksheet, it claims it can't find that sheet. But the sheet is definitely there. And again it works for everyone else.
One more clue is that this same user, after first enabling the Add-In, sees the old "the macro cannot be found or has been disabled because of your security settings" error message. We've verified that macros are enabled, no stray Add-ins causing problems, not running 64 bit, etc.
Does anyone have any suggestions what might be causing these errors for this one user?
I just updated a PowerPoint tool for one client, and now one person out of a team of four is experiencing an error message in the middle of that ADODB loading sequence. The weird thing is that only this one person is seeing the error. It works fine for me and for the other 3 people on his team. They are all running Office 2013. I know it is successfully creating the connection to the Excel file, and have verified that it correctly reads the data from the first worksheet and loads it into the first public variable, but when it tries to move on to the next worksheet, it claims it can't find that sheet. But the sheet is definitely there. And again it works for everyone else.
Sub loadfromexcelw()
Dim Datafile As String
Dim objConnection As ADODB.Connection
Dim objRecSet As ADODB.Recordset
Dim x As Integer, y As Integer
Dim reccount As Integer, colcount As Integer, R As Integer, C As Integer
Datafile = contentlibraryfolder & "\ToolbarData.xlsm"
Set objConnection = New ADODB.Connection
objConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Datafile & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
objConnection.Open
Set objRecSet = New ADODB.Recordset
objRecSet.ActiveConnection = objConnection
'--------------------------------------------------read in headings ************(THIS WORKS FINE)*********************
objRecSet.Source = "SELECT * FROM [Headings$];"
objRecSet.Open , , adOpenStatic
reccount = objRecSet.RecordCount
colcount = objRecSet.Fields.count
ReDim headingsw(reccount - 1, 20)
objRecSet.MoveFirst
For x = 0 To UBound(headingsw, 1)
headingsw(x, 0) = objRecSet.Fields(0) ' load from column 1
For y = 1 To colcount - 1
If Not (IsNull(objRecSet.Fields(y))) Then headingsw(x, y) = objRecSet.Fields(y) ' load subheadings from cols 2 - end
Next y
objRecSet.MoveNext
Next x
objRecSet.Close
'----------------------------------------- read in icon keywords
objRecSet.Source = "SELECT * FROM [Icons$];"
objRecSet.Open , , adOpenStatic '****************FAILS HERE********************
reccount = objRecSet.RecordCount
colcount = objRecSet.Fields.count
ReDim IconDataw(reccount - 1, colcount - 1)
objRecSet.MoveFirst
For x = 0 To UBound(IconDataw, 1)
If Not (IsNull(objRecSet.Fields(0))) Then IconDataw(x, 0) = objRecSet.Fields(0) ' load from column 1
For y = 1 To colcount - 1
If Not (IsNull(objRecSet.Fields(y))) Then IconDataw(x, y) = objRecSet.Fields(y) ' load keywords from cols B, C, type from D
Next y
objRecSet.MoveNext
Next x
objRecSet.Close
'-----------------------------------------etc - code continues to read in several more worksheets in the same manner, then closes connection.
One more clue is that this same user, after first enabling the Add-In, sees the old "the macro cannot be found or has been disabled because of your security settings" error message. We've verified that macros are enabled, no stray Add-ins causing problems, not running 64 bit, etc.
Does anyone have any suggestions what might be causing these errors for this one user?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Any news with your 'Trying something else' ?
gowflow
gowflow
ASKER
Not yet. We tried the change from "Xml" to "Macro" by itself and that didn't solve it, but I sent him another version without the IMEX=1 and still waiting to hear back. I've read other stuff online like use single quotes to surround Extended Properties, or try JET instead of ACE. Or re-install Office. Any of those worth trying?
For sure without the IMEX=1 What I posted was the thing to try !!!
gowflow
gowflow
Question has been inactive for over 14 days and is now being classified as abandoned.
ASKER