Space Issue in macro typical scenario

[toravietl]

Hi,

I have written one macro to get the data count from Oracle Database tables using excel macro.Its working fine but the problem is,i have lot of tables so I mentione all the tables in sql query that query im using in macro to get the results.When I use that sql with 2 or 10 table names that sql will be in one line in my code so it is working.but if I use more tables it is coming to another lines because of sql querry length.Because of that my sql is not working.

Sample sql used in macro working for less no of tables.I defined like,
strSQL="SELECT TABLENAME,COUNT(*) FROM BASETABLES WHERE TABLENAME IN('TABLE1','TABLE2')
The above one is working because entire script in same line.

Failing scenario:
strSQL="SELECT TABLENAME,COUNT(*) FROM BASETABLES WHERE TABLENAME IN('TABLE1','TABLE2',
'table4',...........................................................
'table60'...................................................'Table100')

wat is happening above code is sql querry comes down to second line here it is starting with " ' " symobol.bcz of that macro considering it as comments.

Any one can please help me on this....

[haripopuri]

You can split long lines in VBA using underscore followed by space
strsql = "SELECT TABLENAME,COUNT(*) FROM BASETABLES WHERE TABLENAME IN('TABLE1','TABLE2'," & _
"'Table3',...................."

[toravietl]

Hi Hari,

Thx for the reply.Little bit confusing for me.Can you please give some example script for some tables.

[toravietl]

Any one can help on this issue...

[haripopuri]

Or consider using Chr(39) in place of apostrophe

[haripopuri]

Sorry, I don't have any sample codes. I just used your code to come in 2 lines. See below. Paste it in your code and see, only for 3 tables.

strsql = "SELECT TABLENAME,COUNT(*) FROM BASETABLES WHERE TABLENAME IN('TABLE1','TABLE2'," & _
"'Table3'"