Hi, i'm creating an excel front end for an access db and currently the getting data bit is working nicely..
so i was happily coding away until this happened!:
and the range.clear failed... this is irratating as if i remove it it will leave aspects on the page and be confusing for both the program and the user :-(Sheets("Edit Package").Range("D9").Clear rst.Open "Select [Channel Brand] From ChannelBrandPackageLink where [Package ID] = " & Cells(9, 2).Value, conn Sheets("Edit Package").Range("D9").CopyFromRecordset rst
and chance you can point me in the right direction?
thanks!
Jonathan
Last edited by Jollyfrog; 10-06-2010 at 12:15 PM.
Failed how? Error, explosion, sudden rain of jelly? If by chance it was an error, what was the error message?
i get a "class method of range class failed!"
i assumed i'd only get that if i called a sheet which wasn't the active one.. apparently this isn't the case!
thanks
I suppose your intention is to clear the contents of a given cell:
I can't think of any error message in this case unless the sheet doesn't exist, the cell is protected or part of an array.Sheets("Edit Package").Range("D9").Clearcontents
Thanks tried that, its not protected, and i removed any associated security and formatting, validation, associated with the cell and ran the code again!, but same error
What happens if you try to empty this cell manually ?
(is a querytable connected to this cell ?)
Removing content manually works fine! and it is not connected to a query table
i alos tried
but this produces the same error!Range("D10").Select Selection.ClearContents
Are you sure the name Sheets("Edit Package") is written correctly ?
Are several workbook open ? In that case you should mention the workbook too:
another approach:Workbooks("opened.xls").Sheets("Edit Package").range("D8").clearcontents
Record a macro emptying this cell; compare the recorded macro with you current code.
the code gives me the error, subset out of range!
i tried a macro which gave me the code:
but this errors too when i use it!Range("D10").Select Selection.ClearContents
hmm when i use the codeas a seperate function it seems to work ok...Workbooks("package.xlsm").Sheets("Edit Package").Range("D9").ClearContents
must be an issue somewhere else in my code
Private Sub TempCombo_Change() Dim conn As New ADODB.Connection Dim sNWind As String Dim rst As New ADODB.Recordset sNWind = _ "M:\Television and Broadband\CHANNEL ECONOMICS\Database\Database Actual\ChannelEconomics.accdb" conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";" rst.Open "Select [Package Name] From Package where Operator = '" & Cells(2, 6).Value & "'", conn Sheets("Edit Package").Range("l1").CopyFromRecordset rst rst.Close If Cells(2, 7).Value <> 0 Then rst.Open "Select [Package Name] From Package where Operator = '" & Cells(2, 6).Value & "' and [Package Name] = '" & Cells(2, 7).Value & "'", conn Sheets("Edit Package").Range("B1").CopyFromRecordset rst rst.Close rst.Open "Select Operator From Package where Operator = '" & Cells(2, 6).Value & "' and [Package Name] = '" & Cells(2, 7).Value & "'", conn Sheets("Edit Package").Range("B2").CopyFromRecordset rst rst.Close rst.Open "Select [Tier Level] From Package where Operator = '" & Cells(2, 6).Value & "' and [Package Name] = '" & Cells(2, 7).Value & "'", conn Sheets("Edit Package").Range("B3").CopyFromRecordset rst rst.Close rst.Open "Select [Subscription Fee] From Package where Operator = '" & Cells(2, 6).Value & "' and [Package Name] = '" & Cells(2, 7).Value & "'", conn Sheets("Edit Package").Range("B4").CopyFromRecordset rst rst.Close rst.Open "Select [Digital/Analogue] From Package where Operator = '" & Cells(2, 6).Value & "' and [Package Name] = '" & Cells(2, 7).Value & "'", conn Sheets("Edit Package").Range("B5").CopyFromRecordset rst rst.Close rst.Open "Select [Package Date] From Package where Operator = '" & Cells(2, 6).Value & "' and [Package Name] = '" & Cells(2, 7).Value & "'", conn Sheets("Edit Package").Range("B6").CopyFromRecordset rst rst.Close rst.Open "Select [subscriber %] From Package where Operator = '" & Cells(2, 6).Value & "' and [Package Name] = '" & Cells(2, 7).Value & "'", conn Sheets("Edit Package").Range("B7").CopyFromRecordset rst rst.Close rst.Open "Select active From Package where Operator = '" & Cells(2, 6).Value & "' and [Package Name] = '" & Cells(2, 7).Value & "'", conn Sheets("Edit Package").Range("B8").CopyFromRecordset rst rst.Close rst.Open "Select ID From Package where Operator = '" & Cells(2, 6).Value & "' and [Package Name] = '" & Cells(2, 7).Value & "'", conn Sheets("Edit Package").Range("B9").CopyFromRecordset rst rst.Close 'Range("D10").Select 'Selection.ClearContents 'Sheets("Edit Package").Range("D10").Clear Workbooks("package.xlsm").Sheets("Edit Package").Range("D9").ClearContents rst.Open "Select [Channel Brand] From ChannelBrandPackageLink where [Package ID] = " & Cells(9, 2).Value, conn Sheets("Edit Package").Range("D9").CopyFromRecordset rst rst.Close End If Set rsData = Nothing Set dbData = Nothing End Sub
I bet you got that because you didn't adapt my code (or it would be a rare coincidence that one of the opend workbooks is called 'opened.xls'the code gives me the error, subset out of range!
I think it's time to put your workbook here and tell us how many workbooks are opened at the same time the macro is running and where you store the macro you are running.
what the..... now it works..
i opened a different workbook to do something else and then came back to it and it worked..... i've been on this since yesterday
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks