Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    153
    Thanks
    28
    Thanked 3 Times in 2 Posts

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

    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.

    20150903 GetSheetsNames Error.JPG

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

    20150903 Tools-References.JPG

    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

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    153
    Thanks
    28
    Thanked 3 Times in 2 Posts
    Quote Originally Posted by RetiredGeek View Post
    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?
    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

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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:\BEKDocs\Excel\"    '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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    153
    Thanks
    28
    Thanked 3 Times in 2 Posts
    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
    Get Worksheet1.Sheets.Count
    Get list of Worksheet1.Sheets.Names
    Counter = 0
    For N= 1 to Worksheet1.Sheets.Count
    If Worksheet1.Sheets.Names (N)= Worksheet2.Sheets.Names(M) then
    Counter = Counter +1
    End If
    Next N
    If Counter = 0 then
    Worksheet2.Sheets(M).Copy After:= Worksheet1.Sheets.(say sheet3)
    End If
    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

    Trevor

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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!
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    153
    Thanks
    28
    Thanked 3 Times in 2 Posts
    Hi RetiredGeek & Rory,

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

    Thanks heaps.

    Cheers

    Trevor

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •