I am trying to write a query in VBA but have fallen at the last fence
This falls down at the last two lines marked with* where I get "Method Range of Object_Global failed". I have tried avoiding the Select and using the variable Dest but to no avail
Please Login or Register to view this content.
Can anyone see where I have gone wrong. I have not given the details of the SQLstr, but could it be there ? Or is it to do with using Select
Last edited by j_Southern; 03-23-2012 at 12:35 PM.
You need to reference a specific named ListObject, an instance of the ListObjects collection.
Bob
I am not sure what a ListObject is, they only seem to have become neccessary since Excel2007. Certainly there will only be one object at the range specified. There is only one QueryTable on each sheet anyway.
Should I run something like
For Every ListObject in ListObjects
to find what I am looking for
John
John, in Excel 2007 and 2010, a listobject is an Excel table. If you select inside your table, and then pick the Design tab from the Tables contextual ribbon tab, you will see the table name in the far left, where you can change it. It will likely be Table1 if you haven't changed it. You code would then be
(I think, I haven't actually tested it).Please Login or Register to view this content.
Bob
Thanks for that. I cant check at the moment, but I think there will be a problem. This is the first instance of the query, so there won't be a table until the refresh has been carried out!
I will try it out in the morning and let you know what happens.
John
Bob,
I have tried your suggestion and I get error "Object does not support method or property" or "Wrong number of arguments or invalid property assignment" depending on whether I use Dest as range or type it out fully as you have.
I am looking at the SQL string now in case this is the difficulty.
John
You don't show the SQL so I cannot comment, maybe a workbook and the data source would help.
Bob
The data source is my companies Sage accounting system so its not really possible to send it. I could pull part of two of the files I am using into two worksheets, but I dont think you can use an ODBC link from Excel to Excel. If that would help let me know
Any way here is the Function I use to generate the SQL string.
During my testing I had Remd out everything except SELECT, the first field, FROM one table, and I still got the error.Please Login or Register to view this content.
The parameter StartD is not used in this SQL string, but it is in others as a criterion to set the earliest WM.INVOICE_DATE
John
Last edited by j_Southern; 03-23-2012 at 11:15 AM. Reason: remove typo
Bob and others
I Have found the problem.
The replacement of
withPlease Login or Register to view this content.
did the trick. I realised that the first bit of code was used to refresh an existing query.Please Login or Register to view this content.
Thanks for your help anyway. I can mark the thread solved
John
You could always take the lump hammer approach
ActiveWorkbook.RefreshAll
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks