First question... WOO WOO

Tranquil_Ape

New Member
Joined
Jun 20, 2008
Messages
23
Ok i mentioned a week or so ago i would start the flood... lol so here is my first obstacle... I'll start witha snippet that works fine and then ask the question...



here we have a query: (not sure why the guy before me built the string this way, maybe for readability? anyway


Code:
[INDENT]strQuery = "SELECT ProjectName, ProjectID"
strQuery = strQuery + " FROM project"
strQuery = strQuery + " ORDER BY ProjectName"
 
'Execute the SQL Query and return the results to the temp sheet
    With Worksheets("projectNameData").QueryTables.Add(Connection:=strConnection, _
            Destination:=Range("A1"), Sql:=strQuery)
'Query does not execute until the refresh command is called
            .Refresh
 
[/INDENT]
This puts two columns of data into my sheet... what i want to do is create a function where i can supply arguments that determine what data gets pulled from which db... god i hope that makes sense..hah! problem is in this example we had two columns of data projectID and projectName but that number could change all the time from 2 or 3 data items to maybe 9 or 10 if not more....

hope im explaining myself clearly enough... if not shoot me a reply and ill try to articulate myself better..
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
ok, i continued looking for info and came across this

<TABLE><TBODY><TR><TD>ParamArray</TD><TD>Optional. Used only as the last argument in arglist to indicate that the final argument is an Optional array of Variant elements. The ParamArray keyword allows you to provide an arbitrary number of arguments. ParamArray can't be used with ByVal, ByRef, or Optional.</TD></TR></TBODY></TABLE>
on the surface this sounds like what i want... i can still have my one require arg and anything beyond that is gravy... need to read up on it... any info is still greatly appreciated
 
Upvote 0
OK so i figured out what i want to do... the function works up until the very last line...

once i hit '.Refresh' i get Run Time error 1004
application defined or object defined error..

here is the code im working with and the call

Code:
Call makeQuery("ProjectNameData", "Project", "Project Name", "ProjectID")
 
 
 
Private Sub makeQuery(dest As String, source As String, table1 As String, ParamArray tableX() As Variant)
 
'The first part of the query string
strQuery = "SELECT " & table1 & ", "
 
If Not (UBound(tableX) < LBound(tableX)) Then               'this determins if the paramater array was passed the the function or not
    For iArrayLength = LBound(tableX) To UBound(tableX)     'if it was passed we need to create a string compiled of all the array
        strQuery = strQuery + (tableX(iArrayLength) & " ")  'elements
    Next iArrayLength
End If
'the Query string is completed here
strQuery = strQuery + "FROM " & source
strQuery = strQuery + " ORDER by " & table1
Debug.Print strQuery
 
'Execute the SQL Query and return the results to the desired ;sheet
    With Worksheets("projectNameData").QueryTables.Add(Connection:=strConnection, _
            Destination:=Range("A1"), Sql:=strQuery)
'Query does not execute until the refresh command is called
            .Refresh
    End With
End Sub

thanks for any help...
 
Upvote 0
ok, because i dont wanna make myself look really bad i won't tell you what the problem was but it seems to be working as intended... thanks for all the help :eek:
 
Upvote 0
Tranquil_Ape... a Welcome to the Board is probably in order. Sharing to help others in similiar situations is, I'm sure, also quite Welcome. I would suggest more conscisely naming your needs in the thread title if you really want help. As you can tell, I "never" read threads that don't create some XL interest for me in the title (that is of course, unless you use WOO WOO)...there's just too many of them on too many forums. HTH. Dave
 
Upvote 0
sound advice, i'll keep that in mind. I re-read my above post and realized it may have come off as being very sarcastic... that was not my intention... again thanks for the advice...
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top