SELECT based on a column in Excel

Hello,

I've constructed a code in VBA where I SELECT all orders from the last month and then I can do some analytics with this data. But I also want to fetch how these orders have been invoiced. What I've done is that I create a column in Excel where all the orderid's are that I want invoice data about. I can copy this column into my SQL-code manually with no problem but I want to make it automatic. What I'm trying to do is somthing like:

First i declare

Please Login or Register  to view this content.

(because I want the data I save in this variable to be saved in between subs.)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

But I get the compile error: Object required and the problem is rngOrderId in my sqlStr. How can I solve this problem?

Originally Posted by Richard Buttrey

You have a variable 'row'.

In the code you've posted no value has been assigned to it.

Well the row variable is working. It's been declared and I have no problems using it elsewhere so that's not the problem. The problem is the rngOrderId variable!

Originally Posted by Richard Buttrey

You may well have declared it but there was nothing in your original to indicate that was the case. We're not psychic - well not very often anyway!

Absolutely, I should have been more clear!

Originally Posted by Norie

You'll need to create a comma delimited string from the values in the range rngOrderId and use that in the IN clause of the SQL statement.

Something like this might work for numeric IDs.

Please Login or Register  to view this content.

PS The error was probably because you used Set for the, string, SQL statement, not the variable rngOrderId.

I Think my problem might be that the rngOrderId uses single quotation marks. Every order number looks like '12345' so that I have a string of

'12345','12364','12654'

. I'm fearing that this makes the SQL-string into VBA-comments? How can I get around this? I need the ' around the ordernumbers for the SQL to work.

I found out what the new problem was. I have alot of orders and that makes my sqlstatement longer than the maximum length which is 32.768 characters. I don't seem like I can get around that any way though!