Results 1 to 4 of 4
  1. #1
    vignes10 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    2

    Passing List of Parameter

    Hi All,



    I have list (server names) in excel file (200 rows), I would need to query the Model of these from in MS access, How do I pass these list in excel to the query.. I can't be running the query 200 times nor I could type them one by one. Appreciate any response
    Thanks

  2. #2
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    You can build a list by using something like this:

    Code:
    Dim objXL As Object
    Dim xlWS As Object
    Dim strCriteria As String
    Dim i As Integer
    Dim qdf As DAO.QueryDef
     
    Set objXL = CreateObject("Excel.Application")
     
    objXL.Workbooks.Open("FilePathAndFileNamewithExtensionHere")
     
    With objXL
       Set xlWS = .ActiveWorkbook.Worksheets(1)
            Do Until xlWS.Range("A" & i).Value = vbNullString
                 strCriteria = strCriteria & xlWS.Range("A" & i).Value & ","
            Loop
     
    .ActiveWorkbook.Close False
    .Quit
    End With
     
    strCriteria = Left(strCriteria, Len(strCriteria) - 1) 
    strCriteria = "[FieldNameHere] In(" & strCriteria & ")"
     
     
    Set qdf = CurrentDb.QueryDefs("QueryNameHere")
     
    qdf.SQL = ReplaceWhereClause(qdf.SQL, strCriteria)
     
    qdf.Close
     
    Set qdf = Nothing
    Set objXL = Nothing
    You would need to download the SQL tools module from MVP Armen Stein which has some cool and useful things like ReplaceWhereClause.
    Last edited by boblarson; 09-14-2011 at 01:17 PM. Reason: add stripping of final comma

  3. #3
    vignes10 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    2
    Thanks for the reply Bob. I guess I have to start learning VBA as there will lot more of such query coming in my way. Any books / links you suggest/recommend would be a good start ..

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If you don't want to use the VB code bob gave you can you not just link your excel file into an Access DB, then use that linked table as the source for your query?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Passing a Picture frame as a parameter
    By SacredFootballLB in forum Forms
    Replies: 1
    Last Post: 09-30-2010, 11:40 AM
  2. Macro passing a parameter
    By SlowPoke in forum Access
    Replies: 1
    Last Post: 09-26-2010, 09:57 PM
  3. bypassing passing parameter when null
    By cowboy in forum Queries
    Replies: 11
    Last Post: 04-14-2010, 09:59 PM
  4. Parameter Query: Select From A List?
    By catbob in forum Queries
    Replies: 4
    Last Post: 02-08-2010, 08:24 PM
  5. Pass list as parameter to in operator
    By bliever in forum Queries
    Replies: 5
    Last Post: 11-11-2009, 03:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums