Excel 2010: Cannot update. Database or object is read-only

Topic

New Reply

I have an Excel (2010) Workbook with a command button on a Sheet containing the following code:

Code:

Private Sub CommandButton1_Click()

Dim TestCol As Collection
Dim ThisWorkbook As String

Dim Msg, Button, Title, Response As String
Button = vbExclamation
Title = ActiveSheet.Name & “Private Sub CommandButton1_Click()…”    ‘   Amend as necessary

ThisWorkbook = ActiveWorkbook.Name

Set TestCol = GetSheetsNames(ThisWorkbook)

Msg = “ThisWorkbook:   ” & ThisWorkbook & vbCrLf & _
      “TestCol(2):    ” & TestCol(2)
Response = MsgBox(Msg, Button, Title)

End Sub

The above code calls a UDF defined in a Module called “GetSheetsNames(WorkBookName as string) as Collection” containing the following code:

Code:

Function GetSheetsNames(WorkBookName As String) As Collection

‘   Creates a collection (Col) of sheet names from a workbook

Dim objConn As ADODB.Connection
Dim objCat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim sConnString As String
Dim sSheet As String
Dim Col As New Collection

Dim Msg, Button, Title, Response As String
Button = vbExclamation
Title = “Module1: Function GetSheetsNames()…”

sConnString = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
            “Data Source=” & WorkBookName & “;” & _
            “Extended Properties=Excel 8.0;”

Set objConn = New ADODB.Connection

        Msg = ” #1:  objConn set,    WorkBookName:  ” & WorkBookName
        Response = MsgBox(Msg, Button, Title)

objConn.Open sConnString

        Msg = “#2:  WorkBookName:  ” & WorkBookName
        Response = MsgBox(Msg, Button, Title)

 Exit Function

(The function procedure actually continues on to do other things but for the purposes of demonstration is ended after the Msg/Response block).

I developed the above code in a “Test Workbook.xlsm” where it worked perfectly.

I then copied and pasted the above code sets into my “Working Workbook.xlsm” and it now produces the following error after displaying Msg #1: and before attempting to display Msg #2 with the code line “objConn.Open sConnString” highlighted in the VBE.

41914-20150903-GetSheetsNames-Error

I’ve checked Tools>References in VBE and have the following:

41915-20150903-Tools-References

These Tool>References are identical to those in my Test Workbook

By now I’d be tearing my hair out, but there’s not much left so I’ve resisted that temptation, so now I’m seeking assistance from the Gurus who frequent this place. Of course, any and all clues to help find the cause of the error, or better still a solution, will be greatly appreciated.

Cheers

Trevor

Reply | Quote

Replies

B.A.B,

If I might ask, why are you trying to do this with ADO (“usually” used in Access) vs just using standard Excel VBA to accomplish the task? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

B.A.B,

If I might ask, why are you trying to do this with ADO (“usually” used in Access) vs just using standard Excel VBA to accomplish the task? :cheers:

Hi RetiredGeek,

Everything is in Excel VBA. Why am I using ADO? Because that was code I found on the web that worked when I tested it. Can you suggest alternative code that will do the job.

I’m looking to create a collection of sheet names from “This Workbook” and “Another Workbook”, then comparing the two sheet name collections before importing the sheets into my “This Workbook”.

Cheers

Trevor

Reply | Quote

Trevor,

Here’s some code to get you started.

Code:

Option Explicit

Sub CompareWkBks()

   Dim wkbFirst  As Workbook
   Dim wkbSecond As Workbook
   Dim wkbMain   As Workbook
   Dim zFileSpec As String
   Dim zDirPath  As String

   zDirPath = "G:BEKDocsExcel"    'Your Default Directory here!
   Set wkbMain = ActiveWorkbook
   
'*** Get First File ***
   zFileSpec = zGetFileName(zDirPath)
   If (zFileSpec = "") Then Exit Sub   '*** No file selected ***
   Set wkbFirst = Workbooks.Open(zFileSpec)
   
'*** Get Second File ***
   zFileSpec = zGetFileName(zDirPath)
   If (zFileSpec = "") Then Exit Sub   '*** No file selected ***
   Set wkbSecond = Workbooks.Open(zFileSpec)
   
   wkbMain.Activate
   
   MsgBox "Workbook: " & wkbFirst.Name & " has #" & _
                         Format(wkbFirst.Sheets.Count, "##") & " sheets." & _
                         vbCrLf & _
          "Workbook: " & wkbSecond.Name & " has #" & _
                         Format(wkbSecond.Sheets.Count, "##") & " sheets.", _
          vbOKOnly, "Workbook Sheets to Compare"
          
'*** Cleanup Code
          
   wkbFirst.Close SaveChanges:=False  '*** Note: Change this if you want to save!
   wkbSecond.Close SaveChanges:=False
   
   Set wkbFirst = Nothing
   Set wkbsecont = Nothing

End Sub            'CompareWkBks

Note: This assumes the code above is in it’s own workbook much better that way because it let’s you test any two workbooks w/o having to copy the code around.

Function to display dialog to select files:

Code:

'                        +--------------------+                 +----------+
'------------------------|   zGetFileName     |-----------------| 02/22/15 |
'                        +--------------------+                 +----------+

Public Function zGetFileName(Optional vInitialDir As Variant) As String

'Note: This function can be called directly passing the initial directory
'      or you can call the zGetDirectory function first to obtain the
'      initial directory or call w/o argument to use the current directory
'      as the starting point.
 
    Dim lngCount  As Long
    Dim dlgMyFile As FileDialog
    
    Set dlgMyFile = Application.FileDialog(msoFileDialogOpen)
       
    With dlgMyFile
    
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewLargeIcons
        .FilterIndex = 1   'Excel Files excludes csv, xla, xhtml, etc.
'*** Note: if the InitialFileName is a Directory it should be followed by an
'***       ending  to prevent the dir name showing up in the file name box.
        .InitialFileName = vInitialDir
        .Show
 
        If .SelectedItems.Count  0 Then _
          zGetFileName = .SelectedItems(1)
 
    End With
    
    Set dlgMyFile = Nothing
    
End Function   'zGetFileName()

You can use a ForEach loop to pull the worksheet names:

Code:

   
   For Each sht In wkbFirst.Sheets
      Debug.Print sht.Name
   Next sht

You can use this function to check if the sheet exists in the other workbook:

Code:

Option Explicit

Function SheetExists(xlWkBk As Workbook, xlWkShtNm As String) As Boolean

'*** Returns True if the Sheet Name already exists in the workbook ***
'*** Returns False if it is safe to create a sheet by that name    ***
   SheetExists = False
   On Error GoTo NoSuchSheet
   If Len(xlWkBk.Sheets(xlWkShtNm).Name) > 0 Then SheetExists = True

NoSuchSheet:

End Function

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RetiredGeek,

Many thanks for the Code suggestions.

Maybe I’m thick, but I can’t see the point of the “Sub CompareWkBks()” procedure when I know already that I want to import some, but maybe not all, the sheets from Workbook2 (which is closed, at least initially) into Workbook1 (which is open), but to only import those sheets that do not exist in Workbook1.

Also, I cannot see the point of having “Sub CompareWkBks()” in yet another workbook, which if I distribute my workbook to others means I have to distribute 2 workbooks and establish a means of maintaining the relationship between them on their personal (not business environment) computers:( Much simpler if only one workbook is needed.

I have no issue with getting the filename of either Workbook1 or Workbook2, so “zGetFileName” doesn’t really help me.

In my case, both Workbook1 and Workbook2 can have “Daily Sheets” with same Sheet.Names, which names, although text, are all in a pre-defined date format (“dd mmm yyyy”). Other than the Daily Sheets, the various workbooks from which the imports are to be made are the same, have other identical “Summary”, “Analysis”, etc sheets that do not need to be imported. The workbooks are all being created from a workbook template, typically saved monthly as daily data is accumulated.

The problem I am having is collating a list (ie a Collection) of the Worksheet1.Sheet.Names that exist so they can be compared with a similar list of Worksheet2.Sheet.Names, and then to only import those sheets from Workbook2 that do not exist in Workbook1. This process requires repeatedly checking the Worksheet1.Sheet.Names as each sheet is imported for Workbook2, otherwise duplicates are imported into Workbook1 (been there, done that). The process goes like this:

Get a list of Worksheet2.Sheets.Names
Get Worksheet2.Sheets.Count
For M= 1 to Worksheet2.Sheets.Count
[INDENT]Get Worksheet1.Sheets.Count
Get list of Worksheet1.Sheets.Names
Counter = 0
For N= 1 to Worksheet1.Sheets.Count
[INDENT]If Worksheet1.Sheets.Names (N)= Worksheet2.Sheets.Names(M) then
[INDENT]Counter = Counter +1[/INDENT]
End If
[/INDENT]Next N
If Counter = 0 then
[INDENT]Worksheet2.Sheets(M).Copy After:= Worksheet1.Sheets.(say sheet3)[/INDENT]
End If[/INDENT]
Next M

Thus the heart of my task is the collation of a Collection of the Sheet.Names in each workbook using the UDF “GetSheetsNames” and it is that function that is giving me problems in my “real-life” workbook, whereas it worked perfectly in my test workbook.

As I said above, maybe I’m thick in the head, but I can’t see how you code suggestions help to resolve my problem.

Thanks again for your help,

Cheers:cheers:

Trevor

Reply | Quote

A couple of observations:

1. ThisWorkbook is not a good variable name – it’s an intrinsic Excel object.
2. If you’re using 2010, you should be using Microsoft.ACE.OLEDB.12.0 as the Provider, not Microsoft.Jet.OLEDB.4.0
3. Similarly, if you are trying to access newer format files like .xlsx or .xlsm, the Extended properties should not be ‘Excel 8.0’ which relates to .xls files. You should use ‘Excel 12.0 Xml’ for .xlsx files, ‘Excel 12.0 Macro’ for .xlsm and ‘Excel 12.0’ for .xlsb files. ‘Excel 8.0’ will only work on the newer formats if the file in question is open, in which case ADO is pretty pointless!

Reply | Quote

Trevor,

You can just loop through the Workbook2 sheet names calling the SheetExists function and if it returns false then copy it to Workbook1.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RetiredGeek & Rory,

Thanks for your help. The suggestions both of you made have guided me to success:clapping:

Thanks heaps.

Cheers

Trevor

Reply | Quote