locked
ClearContents works in one instance but not in another.... RRS feed

  • Question

  • Sorry this code is so long so I will post it last. I have a module "dataglobal" that has a function "rebuild_worksheets" and a subroutine "load_worksheets". Essentially they share the exact same code except that I will be slightly modifying "rebuild_worksheets". So far "load_worksheets" works very well. For some reason inside "rebuild_worksheets" the same exact call to "Worksheets("distinctabn").Cells.ClearContents" that works great in "load_worksheets" is failing in "rebuild_worksheets". My call to "rebuild_worksheets" nested inside the "ComboBox1_Change" event if that matters at all. I can't seem to figure out why the clear contents is working in one instance and failing in the other. Any light anyone can shed on this matter I would love to hear!

    Thank you MSDN!

    P.S. Run-time error '1004' CleaContents method of Range class failed.

    dataglobal:

    Public oCn As ADODB.Connection
    
    Public alldata As ADODB.Recordset
    Public categories As ADODB.Recordset
    Public thedrop As ADODB.Recordset
    
    Public ConnString As String
    Public qt As QueryTable
    
    Public Count As Integer
    Public SQL As String
    Public ws As Worksheet
    Public qtable As QueryTable
    Public tCategory As String
    
    
    
    
    Public Sub Open_Connection()
    
    Dim fPath As String
    fPath = ThisWorkbook.Path + "\" + ThisWorkbook.Name + ";"
    
    ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fPath + "Extended Properties=""Excel 12.0;HDR=YES"""
    
    Set oCn = New ADODB.Connection
    oCn.ConnectionString = ConnString
    oCn.Open
    
    End Sub
    
    Public Sub Close_Connection()
    
    End Sub
    
    Public Sub rebuild_worksheets()
    
    'distinctabn
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctabn").Activate
    SQL = "SELECT DISTINCT [ABN] FROM [nojansdatabase$] WHERE [ABN] <> """" AND [Category]=""" + tCategory + """ ORDER BY [ABN]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctabn").Cells.ClearContents
    Set qt = Worksheets("distinctabn").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctaddress
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctaddress").Activate
    SQL = "SELECT DISTINCT [Address] FROM [nojansdatabase$] WHERE [Address] <> """" ORDER BY [Address]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctaddress").Cells.ClearContents
    Set qt = Worksheets("distinctaddress").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctanzicode
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctanzicode").Activate
    SQL = "SELECT DISTINCT [ANZSIC Code] FROM [nojansdatabase$] WHERE [ANZSIC Code] <> """" ORDER BY [ANZSIC Code]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctanzicode").Cells.ClearContents
    Set qt = Worksheets("distinctanzicode").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctareacode
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctareacode").Activate
    SQL = "SELECT DISTINCT [Phone] FROM [nojansdatabase$] WHERE [Phone] <> """" ORDER BY [Phone]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctareacode").Cells.ClearContents
    Set qt = Worksheets("distinctareacode").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctbusinessname
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctbusinessname").Activate
    SQL = "SELECT DISTINCT [Business Name] FROM [nojansdatabase$] ORDER BY [Business Name]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctbusinessname").Cells.ClearContents
    Set qt = Worksheets("distinctbusinessname").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctcategory
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctcategory").Activate
    SQL = "SELECT DISTINCT [Category] FROM [nojansdatabase$] ORDER BY [Category]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctcategory").Cells.ClearContents
    Set qt = Worksheets("distinctcategory").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctcommenced
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctcommenced").Activate
    SQL = "SELECT DISTINCT [Commenced] FROM [nojansdatabase$] WHERE [Commenced] <> """" ORDER BY [Commenced]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctcommenced").Cells.ClearContents
    Set qt = Worksheets("distinctcommenced").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctemail
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctemail").Activate
    SQL = "SELECT DISTINCT [Email] FROM [nojansdatabase$] WHERE [Email] <> """" ORDER BY [Email]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctemail").Cells.ClearContents
    Set qt = Worksheets("distinctemail").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctfaxnumber
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctfaxnumber").Activate
    SQL = "SELECT DISTINCT [Fax] FROM [nojansdatabase$] WHERE [Fax] <> """" ORDER BY [Fax]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctfaxnumber").Cells.ClearContents
    Set qt = Worksheets("distinctfaxnumber").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctphonenumber
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctphonenumber").Activate
    SQL = "SELECT DISTINCT [Phone] FROM [nojansdatabase$] WHERE [Phone] <> """" ORDER BY [Phone]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctphonenumber").Cells.ClearContents
    Set qt = Worksheets("distinctphonenumber").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctpostcode
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctpostcode").Activate
    SQL = "SELECT DISTINCT [Postcode] FROM [nojansdatabase$] WHERE [Postcode] <> """" ORDER BY [Postcode]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctpostcode").Cells.ClearContents
    Set qt = Worksheets("distinctpostcode").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctsiccode
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctsiccode").Activate
    SQL = "SELECT DISTINCT [SIC Code] FROM [nojansdatabase$] WHERE [SIC Code] <> """" ORDER BY [SIC Code]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctsiccode").Cells.ClearContents
    Set qt = Worksheets("distinctsiccode").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctstate
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctstate").Activate
    SQL = "SELECT DISTINCT [State] FROM [nojansdatabase$] WHERE [State] <> """" ORDER BY [State]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctstate").Cells.ClearContents
    Set qt = Worksheets("distinctstate").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctsuburb
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctsuburb").Activate
    SQL = "SELECT DISTINCT [Suburb] FROM [nojansdatabase$] WHERE [Suburb] <> """" ORDER BY [Suburb]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctsuburb").Cells.ClearContents
    Set qt = Worksheets("distinctsuburb").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distincturl
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distincturl").Activate
    SQL = "SELECT DISTINCT [URL] FROM [nojansdatabase$] WHERE [URL] <> """" ORDER BY [URL]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distincturl").Cells.ClearContents
    Set qt = Worksheets("distincturl").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    
    End Sub
    
    Public Sub load_worksheets()
    
    'distinctabn
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctabn").Activate
    SQL = "SELECT DISTINCT [ABN] FROM [nojansdatabase$] WHERE [ABN] <> """" ORDER BY [ABN]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctabn").Cells.ClearContents
    Set qt = Worksheets("distinctabn").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctaddress
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctaddress").Activate
    SQL = "SELECT DISTINCT [Address] FROM [nojansdatabase$] WHERE [Address] <> """" ORDER BY [Address]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctaddress").Cells.ClearContents
    Set qt = Worksheets("distinctaddress").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctanzicode
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctanzicode").Activate
    SQL = "SELECT DISTINCT [ANZSIC Code] FROM [nojansdatabase$] WHERE [ANZSIC Code] <> """" ORDER BY [ANZSIC Code]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctanzicode").Cells.ClearContents
    Set qt = Worksheets("distinctanzicode").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctareacode
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctareacode").Activate
    SQL = "SELECT DISTINCT [Phone] FROM [nojansdatabase$] WHERE [Phone] <> """" ORDER BY [Phone]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctareacode").Cells.ClearContents
    Set qt = Worksheets("distinctareacode").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctbusinessname
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctbusinessname").Activate
    SQL = "SELECT DISTINCT [Business Name] FROM [nojansdatabase$] ORDER BY [Business Name]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctbusinessname").Cells.ClearContents
    Set qt = Worksheets("distinctbusinessname").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctcategory
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctcategory").Activate
    SQL = "SELECT DISTINCT [Category] FROM [nojansdatabase$] ORDER BY [Category]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctcategory").Cells.ClearContents
    Set qt = Worksheets("distinctcategory").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctcommenced
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctcommenced").Activate
    SQL = "SELECT DISTINCT [Commenced] FROM [nojansdatabase$] WHERE [Commenced] <> """" ORDER BY [Commenced]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctcommenced").Cells.ClearContents
    Set qt = Worksheets("distinctcommenced").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctemail
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctemail").Activate
    SQL = "SELECT DISTINCT [Email] FROM [nojansdatabase$] WHERE [Email] <> """" ORDER BY [Email]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctemail").Cells.ClearContents
    Set qt = Worksheets("distinctemail").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctfaxnumber
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctfaxnumber").Activate
    SQL = "SELECT DISTINCT [Fax] FROM [nojansdatabase$] WHERE [Fax] <> """" ORDER BY [Fax]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctfaxnumber").Cells.ClearContents
    Set qt = Worksheets("distinctfaxnumber").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctphonenumber
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctphonenumber").Activate
    SQL = "SELECT DISTINCT [Phone] FROM [nojansdatabase$] WHERE [Phone] <> """" ORDER BY [Phone]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctphonenumber").Cells.ClearContents
    Set qt = Worksheets("distinctphonenumber").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctpostcode
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctpostcode").Activate
    SQL = "SELECT DISTINCT [Postcode] FROM [nojansdatabase$] WHERE [Postcode] <> """" ORDER BY [Postcode]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctpostcode").Cells.ClearContents
    Set qt = Worksheets("distinctpostcode").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctsiccode
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctsiccode").Activate
    SQL = "SELECT DISTINCT [SIC Code] FROM [nojansdatabase$] WHERE [SIC Code] <> """" ORDER BY [SIC Code]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctsiccode").Cells.ClearContents
    Set qt = Worksheets("distinctsiccode").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctstate
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctstate").Activate
    SQL = "SELECT DISTINCT [State] FROM [nojansdatabase$] WHERE [State] <> """" ORDER BY [State]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctstate").Cells.ClearContents
    Set qt = Worksheets("distinctstate").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distinctsuburb
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctsuburb").Activate
    SQL = "SELECT DISTINCT [Suburb] FROM [nojansdatabase$] WHERE [Suburb] <> """" ORDER BY [Suburb]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctsuburb").Cells.ClearContents
    Set qt = Worksheets("distinctsuburb").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    'distincturl
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distincturl").Activate
    SQL = "SELECT DISTINCT [URL] FROM [nojansdatabase$] WHERE [URL] <> """" ORDER BY [URL]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distincturl").Cells.ClearContents
    Set qt = Worksheets("distincturl").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    End Sub
    

    Private Sub Workbook_Open()
    
    Open_Connection
    load_worksheets
    DataQuery.ComboBox1.RowSource = "distinctcategory!A1:A14"
    DataQuery.Show
    
    End Sub
    

    Private Sub ComboBox1_Change()
    
    tCategory = ComboBox1.Value
    rebuild_worksheets
    
    End Sub




    • Edited by S.e.p.y Saturday, May 19, 2012 6:44 AM
    Saturday, May 19, 2012 4:53 AM

Answers

  • Private Sub ComboBox1_Change()
    
    tCategory = ComboBox1.Value
    
    'distinctabn
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctabn").Activate
    SQL = "SELECT DISTINCT [ABN] FROM [nojansdatabase$] WHERE [ABN] <> """" AND [Category]=""" + tCategory + """ ORDER BY [ABN]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctabn").Cells.ClearContents
    Set qt = Worksheets("distinctabn").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    
    End Sub

    I have a solution like this code above. For some reason the code has to be in the event. I don't know why that is. Can anyone shed some light on why it would function one way and not the other?

    • Edited by S.e.p.y Saturday, May 19, 2012 7:41 AM
    • Proposed as answer by Dummy yoyo Tuesday, May 22, 2012 8:00 AM
    • Marked as answer by S.e.p.y Tuesday, May 22, 2012 8:01 AM
    Saturday, May 19, 2012 6:45 AM

All replies

  • In a very quick look.

    In Excel VBA, function can't change the structure of sheet/workbook

    ClearContents will delete the contents of sheet.It is ony allowable in Sub Routine.

    Saturday, May 19, 2012 6:16 AM
    Answerer
  • Private Sub ComboBox1_Change()
    
    tCategory = ComboBox1.Value
    
    'distinctabn
    Set alldata = New ADODB.Recordset
    ActiveWorkbook.Sheets("distinctabn").Activate
    SQL = "SELECT DISTINCT [ABN] FROM [nojansdatabase$] WHERE [ABN] <> """" AND [Category]=""" + tCategory + """ ORDER BY [ABN]"
    Set alldata = New ADODB.Recordset
    alldata.Source = SQL
    alldata.ActiveConnection = oCn
    alldata.Open
    Worksheets("distinctabn").Cells.ClearContents
    Set qt = Worksheets("distinctabn").QueryTables.Add(Connection:=alldata, Destination:=Range("A1"))
    qt.FieldNames = False
    qt.Refresh
    If Not alldata Is Nothing Then Set alldata = Nothing
    If Not qt Is Nothing Then Set qt = Nothing
    
    End Sub

    I have a solution like this code above. For some reason the code has to be in the event. I don't know why that is. Can anyone shed some light on why it would function one way and not the other?

    • Edited by S.e.p.y Saturday, May 19, 2012 7:41 AM
    • Proposed as answer by Dummy yoyo Tuesday, May 22, 2012 8:00 AM
    • Marked as answer by S.e.p.y Tuesday, May 22, 2012 8:01 AM
    Saturday, May 19, 2012 6:45 AM