+ Reply to Thread
Results 1 to 16 of 16

[SOLVED] VB Code or Excel macro to run Query/Import on Access file

  1. #1
    Harry
    Guest

    [SOLVED] VB Code or Excel macro to run Query/Import on Access file


    Hello

    I'd like to be able to press a button on an Excel sheet and start the process of
    running the MSQUERY function. I tried just recording keystrokes as a macro,
    clicking the following while in record mode:

    Data/Import External Data/New Database Query/MS Access Database/Clicked on
    Database Name

    But nothing got recorded, it didn't work. I'm trying to save myself all those
    keystrokes by automating the task right up to the point in the query where I
    select which record I to import from the Access file.

    I know enough about macros and VB to create a button and launch the macro, but
    I'm really new at this. Does anyone have any code that can do a query from
    Excel in an access file, pulling out a record of their choice?

    Thanks

    Harry



  2. #2
    Tom Ogilvy
    Guest

    Re: VB Code or Excel macro to run Query/Import on Access file

    http://www.erlandsendata.no/english/...php?t=envbadac

    --
    Regards,
    Tom Ogilvy


    "Harry" <[email protected]> wrote in message
    news:[email protected]...
    >
    > Hello
    >
    > I'd like to be able to press a button on an Excel sheet and start the

    process of
    > running the MSQUERY function. I tried just recording keystrokes as a

    macro,
    > clicking the following while in record mode:
    >
    > Data/Import External Data/New Database Query/MS Access Database/Clicked on
    > Database Name
    >
    > But nothing got recorded, it didn't work. I'm trying to save myself all

    those
    > keystrokes by automating the task right up to the point in the query where

    I
    > select which record I to import from the Access file.
    >
    > I know enough about macros and VB to create a button and launch the macro,

    but
    > I'm really new at this. Does anyone have any code that can do a query

    from
    > Excel in an access file, pulling out a record of their choice?
    >
    > Thanks
    >
    > Harry
    >
    >




  3. #3
    Harry
    Guest

    Re: VB Code or Excel macro to run Query/Import on Access file

    Tom, it may be over my head. As I read about this, it seems I need to have
    ADO or DAO running (I don't know what they are, nor the difference) and perhaps
    a database, Oracle or SQL. Is there a faq that takes me through ADO or DAO, so
    I can get the big picture of what has to be happening?

    Harry


    On Fri, 18 Feb 2005 08:44:41 -0500, "Tom Ogilvy" <[email protected]> wrote:

    >http://www.erlandsendata.no/english/...php?t=envbadac
    >
    >--
    >Regards,
    >Tom Ogilvy
    >
    >
    >"Harry" <[email protected]> wrote in message
    >news:[email protected]...
    >>
    >> Hello
    >>
    >> I'd like to be able to press a button on an Excel sheet and start the

    >process of
    >> running the MSQUERY function. I tried just recording keystrokes as a

    >macro,
    >> clicking the following while in record mode:
    >>
    >> Data/Import External Data/New Database Query/MS Access Database/Clicked on
    >> Database Name
    >>
    >> But nothing got recorded, it didn't work. I'm trying to save myself all

    >those
    >> keystrokes by automating the task right up to the point in the query where

    >I
    >> select which record I to import from the Access file.
    >>
    >> I know enough about macros and VB to create a button and launch the macro,

    >but
    >> I'm really new at this. Does anyone have any code that can do a query

    >from
    >> Excel in an access file, pulling out a record of their choice?
    >>
    >> Thanks
    >>
    >> Harry
    >>
    >>

    >
    >



  4. #4
    Tom Ogilvy
    Guest

    Re: VB Code or Excel macro to run Query/Import on Access file

    You can start here. Look on the left side.

    http://msdn.microsoft.com/data/Default.aspx

    However, you may just want to go back to using MSQuery. Turn on the macro
    recorder while you do the Data=>Get External Data (or import external data)
    in the menus and pull down your data. Then turn off the macro recorder and
    look at what has been recorded. You can make the database a variable in the
    code recorded, then just add an input prompt for what database.

    --
    Regards,
    Tom Ogilvy

    "Harry" <[email protected]> wrote in message
    news:[email protected]...
    > Tom, it may be over my head. As I read about this, it seems I need to

    have
    > ADO or DAO running (I don't know what they are, nor the difference) and

    perhaps
    > a database, Oracle or SQL. Is there a faq that takes me through ADO or

    DAO, so
    > I can get the big picture of what has to be happening?
    >
    > Harry
    >
    >
    > On Fri, 18 Feb 2005 08:44:41 -0500, "Tom Ogilvy" <[email protected]> wrote:
    >
    > >http://www.erlandsendata.no/english/...php?t=envbadac
    > >
    > >--
    > >Regards,
    > >Tom Ogilvy
    > >
    > >
    > >"Harry" <[email protected]> wrote in message
    > >news:[email protected]...
    > >>
    > >> Hello
    > >>
    > >> I'd like to be able to press a button on an Excel sheet and start the

    > >process of
    > >> running the MSQUERY function. I tried just recording keystrokes as a

    > >macro,
    > >> clicking the following while in record mode:
    > >>
    > >> Data/Import External Data/New Database Query/MS Access Database/Clicked

    on
    > >> Database Name
    > >>
    > >> But nothing got recorded, it didn't work. I'm trying to save myself

    all
    > >those
    > >> keystrokes by automating the task right up to the point in the query

    where
    > >I
    > >> select which record I to import from the Access file.
    > >>
    > >> I know enough about macros and VB to create a button and launch the

    macro,
    > >but
    > >> I'm really new at this. Does anyone have any code that can do a query

    > >from
    > >> Excel in an access file, pulling out a record of their choice?
    > >>
    > >> Thanks
    > >>
    > >> Harry
    > >>
    > >>

    > >
    > >

    >




  5. #5
    Harry
    Guest

    Re: VB Code or Excel macro to run Query/Import on Access file

    Tom,

    As I mentioned in my first post, the Record Macro route doesn't record anything.
    I don't understand it. I START recording, go half-way through the process of
    importing a record from an Access file, I STOP recording the macro, and here's
    what it shows me in the macro editor:


    Sub Test()
    '
    ' Test Macro
    ' Macro recorded 18/02/2005 by Harry Fine
    '

    '
    End Sub


    That's it. Where is the macro?

    Harry



    On Fri, 18 Feb 2005 10:44:03 -0500, "Tom Ogilvy" <[email protected]> wrote:

    >You can start here. Look on the left side.
    >
    >http://msdn.microsoft.com/data/Default.aspx
    >
    >However, you may just want to go back to using MSQuery. Turn on the macro
    >recorder while you do the Data=>Get External Data (or import external data)
    >in the menus and pull down your data. Then turn off the macro recorder and
    >look at what has been recorded. You can make the database a variable in the
    >code recorded, then just add an input prompt for what database.
    >
    >--
    >Regards,
    >Tom Ogilvy
    >
    >"Harry" <[email protected]> wrote in message
    >news:[email protected]...
    >> Tom, it may be over my head. As I read about this, it seems I need to

    >have
    >> ADO or DAO running (I don't know what they are, nor the difference) and

    >perhaps
    >> a database, Oracle or SQL. Is there a faq that takes me through ADO or

    >DAO, so
    >> I can get the big picture of what has to be happening?
    >>
    >> Harry
    >>
    >>
    >> On Fri, 18 Feb 2005 08:44:41 -0500, "Tom Ogilvy" <[email protected]> wrote:
    >>
    >> >http://www.erlandsendata.no/english/...php?t=envbadac
    >> >
    >> >--
    >> >Regards,
    >> >Tom Ogilvy
    >> >
    >> >
    >> >"Harry" <[email protected]> wrote in message
    >> >news:[email protected]...
    >> >>
    >> >> Hello
    >> >>
    >> >> I'd like to be able to press a button on an Excel sheet and start the
    >> >process of
    >> >> running the MSQUERY function. I tried just recording keystrokes as a
    >> >macro,
    >> >> clicking the following while in record mode:
    >> >>
    >> >> Data/Import External Data/New Database Query/MS Access Database/Clicked

    >on
    >> >> Database Name
    >> >>
    >> >> But nothing got recorded, it didn't work. I'm trying to save myself

    >all
    >> >those
    >> >> keystrokes by automating the task right up to the point in the query

    >where
    >> >I
    >> >> select which record I to import from the Access file.
    >> >>
    >> >> I know enough about macros and VB to create a button and launch the

    >macro,
    >> >but
    >> >> I'm really new at this. Does anyone have any code that can do a query
    >> >from
    >> >> Excel in an access file, pulling out a record of their choice?
    >> >>
    >> >> Thanks
    >> >>
    >> >> Harry
    >> >>
    >> >>
    >> >
    >> >

    >>

    >
    >



  6. #6
    Tom Ogilvy
    Guest

    Re: VB Code or Excel macro to run Query/Import on Access file

    You can't record half a command. You have to go ahead and import the data.
    Then turn off the macro recorder and generalize the recorded code to allow
    you to specify the database name.

    --
    Regards,
    Tom Ogilvy

    "Harry" <[email protected]> wrote in message
    news:[email protected]...
    > Tom,
    >
    > As I mentioned in my first post, the Record Macro route doesn't record

    anything.
    > I don't understand it. I START recording, go half-way through the process

    of
    > importing a record from an Access file, I STOP recording the macro, and

    here's
    > what it shows me in the macro editor:
    >
    >
    > Sub Test()
    > '
    > ' Test Macro
    > ' Macro recorded 18/02/2005 by Harry Fine
    > '
    >
    > '
    > End Sub
    >
    >
    > That's it. Where is the macro?
    >
    > Harry
    >
    >
    >
    > On Fri, 18 Feb 2005 10:44:03 -0500, "Tom Ogilvy" <[email protected]> wrote:
    >
    > >You can start here. Look on the left side.
    > >
    > >http://msdn.microsoft.com/data/Default.aspx
    > >
    > >However, you may just want to go back to using MSQuery. Turn on the

    macro
    > >recorder while you do the Data=>Get External Data (or import external

    data)
    > >in the menus and pull down your data. Then turn off the macro recorder

    and
    > >look at what has been recorded. You can make the database a variable in

    the
    > >code recorded, then just add an input prompt for what database.
    > >
    > >--
    > >Regards,
    > >Tom Ogilvy
    > >
    > >"Harry" <[email protected]> wrote in message
    > >news:[email protected]...
    > >> Tom, it may be over my head. As I read about this, it seems I need

    to
    > >have
    > >> ADO or DAO running (I don't know what they are, nor the difference) and

    > >perhaps
    > >> a database, Oracle or SQL. Is there a faq that takes me through ADO or

    > >DAO, so
    > >> I can get the big picture of what has to be happening?
    > >>
    > >> Harry
    > >>
    > >>
    > >> On Fri, 18 Feb 2005 08:44:41 -0500, "Tom Ogilvy" <[email protected]>

    wrote:
    > >>
    > >> >http://www.erlandsendata.no/english/...php?t=envbadac
    > >> >
    > >> >--
    > >> >Regards,
    > >> >Tom Ogilvy
    > >> >
    > >> >
    > >> >"Harry" <[email protected]> wrote in message
    > >> >news:[email protected]...
    > >> >>
    > >> >> Hello
    > >> >>
    > >> >> I'd like to be able to press a button on an Excel sheet and start

    the
    > >> >process of
    > >> >> running the MSQUERY function. I tried just recording keystrokes as

    a
    > >> >macro,
    > >> >> clicking the following while in record mode:
    > >> >>
    > >> >> Data/Import External Data/New Database Query/MS Access

    Database/Clicked
    > >on
    > >> >> Database Name
    > >> >>
    > >> >> But nothing got recorded, it didn't work. I'm trying to save myself

    > >all
    > >> >those
    > >> >> keystrokes by automating the task right up to the point in the query

    > >where
    > >> >I
    > >> >> select which record I to import from the Access file.
    > >> >>
    > >> >> I know enough about macros and VB to create a button and launch the

    > >macro,
    > >> >but
    > >> >> I'm really new at this. Does anyone have any code that can do a

    query
    > >> >from
    > >> >> Excel in an access file, pulling out a record of their choice?
    > >> >>
    > >> >> Thanks
    > >> >>
    > >> >> Harry
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>

    > >
    > >

    >




  7. #7
    Harry
    Guest

    Re: VB Code or Excel macro to run Query/Import on Access file

    I feel pretty stupid Tom. I've recorded teh macro, as follows:

    Sub Test()
    '
    ' Test Macro
    ' Macro recorded 18/02/2005 by Harry Fine
    '

    '
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
    "ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\Harry\My Documents\Landlord\Access\Clients.mdb;DefaultDir=C:\Documents and" _
    ), Array( _
    " Settings\Harry\My Documents\Landlord\Access;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
    )), Destination:=Range("A1"))
    .CommandText = Array( _
    "SELECT Customers.`Applicant FirstName`, Customers.`Applicant Initital`, Customers.`Applicant Last Name`, Customers.`Applicant Gender`, Customers.`Day Phone`, Customers.`Evening Phone`, Customers.`Stre" _
    , _
    "et Address`, Customers.`Unit #`, Customers.City, Customers.Province, Customers.`Postal Code`, Customers.FaxNumber, Customers.EmailAddress, Customers.`Second Applicant First Name`, Customers.`Second Ap" _
    , _
    "plicant Initial`, Customers.`Second Applicant Last Name`, Customers.`Second Applicant Gender`" & Chr(13) & "" & Chr(10) & "FROM Customers Customers" _
    )
    .Name = "Query from MS Access Database"
    .FieldNames = False
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
    End With
    End Sub



    Problem is, I will want to pick out a different record each time, based on the last name of the client I need. How would I get the macro to STOP at the point where it asks me to Filter Data based on Last Name, for example, and
    let me finish from that point, or even better, resume after I've done the filtering. Sorry for the wide columns. I tried to not have the code break too much for you.


    Harry





    On Fri, 18 Feb 2005 11:54:34 -0500, "Tom Ogilvy" <[email protected]> wrote:

    >You can't record half a command. You have to go ahead and import the data.
    >Then turn off the macro recorder and generalize the recorded code to allow
    >you to specify the database name.
    >
    >--
    >Regards,
    >Tom Ogilvy
    >
    >"Harry" <[email protected]> wrote in message
    >news:[email protected]...
    >> Tom,
    >>
    >> As I mentioned in my first post, the Record Macro route doesn't record

    >anything.
    >> I don't understand it. I START recording, go half-way through the process

    >of
    >> importing a record from an Access file, I STOP recording the macro, and

    >here's
    >> what it shows me in the macro editor:
    >>
    >>
    >> Sub Test()
    >> '
    >> ' Test Macro
    >> ' Macro recorded 18/02/2005 by Harry Fine
    >> '
    >>
    >> '
    >> End Sub
    >>
    >>
    >> That's it. Where is the macro?
    >>
    >> Harry
    >>
    >>
    >>
    >> On Fri, 18 Feb 2005 10:44:03 -0500, "Tom Ogilvy" <[email protected]> wrote:
    >>
    >> >You can start here. Look on the left side.
    >> >
    >> >http://msdn.microsoft.com/data/Default.aspx
    >> >
    >> >However, you may just want to go back to using MSQuery. Turn on the

    >macro
    >> >recorder while you do the Data=>Get External Data (or import external

    >data)
    >> >in the menus and pull down your data. Then turn off the macro recorder

    >and
    >> >look at what has been recorded. You can make the database a variable in

    >the
    >> >code recorded, then just add an input prompt for what database.
    >> >
    >> >--
    >> >Regards,
    >> >Tom Ogilvy
    >> >
    >> >"Harry" <[email protected]> wrote in message
    >> >news:[email protected]...
    >> >> Tom, it may be over my head. As I read about this, it seems I need

    >to
    >> >have
    >> >> ADO or DAO running (I don't know what they are, nor the difference) and
    >> >perhaps
    >> >> a database, Oracle or SQL. Is there a faq that takes me through ADO or
    >> >DAO, so
    >> >> I can get the big picture of what has to be happening?
    >> >>
    >> >> Harry
    >> >>
    >> >>
    >> >> On Fri, 18 Feb 2005 08:44:41 -0500, "Tom Ogilvy" <[email protected]>

    >wrote:
    >> >>
    >> >> >http://www.erlandsendata.no/english/...php?t=envbadac
    >> >> >
    >> >> >--
    >> >> >Regards,
    >> >> >Tom Ogilvy
    >> >> >
    >> >> >
    >> >> >"Harry" <[email protected]> wrote in message
    >> >> >news:[email protected]...
    >> >> >>
    >> >> >> Hello
    >> >> >>
    >> >> >> I'd like to be able to press a button on an Excel sheet and start

    >the
    >> >> >process of
    >> >> >> running the MSQUERY function. I tried just recording keystrokes as

    >a
    >> >> >macro,
    >> >> >> clicking the following while in record mode:
    >> >> >>
    >> >> >> Data/Import External Data/New Database Query/MS Access

    >Database/Clicked
    >> >on
    >> >> >> Database Name
    >> >> >>
    >> >> >> But nothing got recorded, it didn't work. I'm trying to save myself
    >> >all
    >> >> >those
    >> >> >> keystrokes by automating the task right up to the point in the query
    >> >where
    >> >> >I
    >> >> >> select which record I to import from the Access file.
    >> >> >>
    >> >> >> I know enough about macros and VB to create a button and launch the
    >> >macro,
    >> >> >but
    >> >> >> I'm really new at this. Does anyone have any code that can do a

    >query
    >> >> >from
    >> >> >> Excel in an access file, pulling out a record of their choice?
    >> >> >>
    >> >> >> Thanks
    >> >> >>
    >> >> >> Harry
    >> >> >>
    >> >> >>
    >> >> >
    >> >> >
    >> >>
    >> >
    >> >

    >>

    >
    >



  8. #8
    Harry
    Guest

    Re: VB Code or Excel macro to run Query/Import on Access file

    Tom, I've taken a macro I found online to prompt for a value, edited it a bit, and merged it with the macro I recorded, substituting the variable UserRange for the actual last name. The idea is, the macro could ask for the client
    last name, and then substitute the value into the macro doing the query into the Access file. It asks me for a name, but then it just says "Cancelled".

    Any things you can point me to? The entire macro is below.



    ' Test Macro
    ' Macro recorded 18/02/2005 by Harry Fine
    '
    Sub GetUserRange()
    Dim UserRange As Range

    Prompt = "Select Last Name."
    Title = "Select Last Name"

    ' Display the Input Box
    On Error Resume Next
    Set UserRange = Application.InputBox( _
    Prompt:=Prompt, _
    Title:=Title, _
    Type:=2) 'Range Selection

    ' Was the Input Box canceled?
    If UserRange Is Nothing Then
    MsgBox "Canceled."
    Else
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
    "ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\Harry\My Documents\Landlord\Access\Clients.mdb;DefaultDir=C:\Documents and" _
    ), Array( _
    " Settings\Harry\My Documents\Landlord\Access;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
    )), Destination:=Range("A1"))
    .CommandText = Array( _
    "SELECT Customers.`Applicant FirstName`, Customers.`Applicant Initital`, Customers.`Applicant Last Name`, Customers.`Applicant Gender`, Customers.`Day Phone`, Customers.`Evening Phone`, Customers.`Stre" _
    , _
    "et Address`, Customers.`Unit #`, Customers.City, Customers.Province, Customers.`Postal Code`, Customers.FaxNumber, Customers.EmailAddress, Customers.`Second Applicant First Name`, Customers.`Second Ap" _
    , _
    "plicant Initial`, Customers.`Second Applicant Last Name`, Customers.`Second Applicant Gender`" & Chr(13) & "" & Chr(10) & "FROM Customers Customers" & Chr(13) & "" & Chr(10) & "WHERE (Customers.`Applicant Last
    Name`=UserRange)" _
    )
    .Name = "Query from MS Access Database"
    .FieldNames = False
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
    End With
    End If
    End Sub


    Harry



    On Fri, 18 Feb 2005 10:44:03 -0500, "Tom Ogilvy" <[email protected]> wrote:

    >You can start here. Look on the left side.
    >
    >http://msdn.microsoft.com/data/Default.aspx
    >
    >However, you may just want to go back to using MSQuery. Turn on the macro
    >recorder while you do the Data=>Get External Data (or import external data)
    >in the menus and pull down your data. Then turn off the macro recorder and
    >look at what has been recorded. You can make the database a variable in the
    >code recorded, then just add an input prompt for what database.
    >
    >--
    >Regards,
    >Tom Ogilvy
    >
    >"Harry" <[email protected]> wrote in message
    >news:[email protected]...
    >> Tom, it may be over my head. As I read about this, it seems I need to

    >have
    >> ADO or DAO running (I don't know what they are, nor the difference) and

    >perhaps
    >> a database, Oracle or SQL. Is there a faq that takes me through ADO or

    >DAO, so
    >> I can get the big picture of what has to be happening?
    >>
    >> Harry
    >>
    >>
    >> On Fri, 18 Feb 2005 08:44:41 -0500, "Tom Ogilvy" <[email protected]> wrote:
    >>
    >> >http://www.erlandsendata.no/english/...php?t=envbadac
    >> >
    >> >--
    >> >Regards,
    >> >Tom Ogilvy
    >> >
    >> >
    >> >"Harry" <[email protected]> wrote in message
    >> >news:[email protected]...
    >> >>
    >> >> Hello
    >> >>
    >> >> I'd like to be able to press a button on an Excel sheet and start the
    >> >process of
    >> >> running the MSQUERY function. I tried just recording keystrokes as a
    >> >macro,
    >> >> clicking the following while in record mode:
    >> >>
    >> >> Data/Import External Data/New Database Query/MS Access Database/Clicked

    >on
    >> >> Database Name
    >> >>
    >> >> But nothing got recorded, it didn't work. I'm trying to save myself

    >all
    >> >those
    >> >> keystrokes by automating the task right up to the point in the query

    >where
    >> >I
    >> >> select which record I to import from the Access file.
    >> >>
    >> >> I know enough about macros and VB to create a button and launch the

    >macro,
    >> >but
    >> >> I'm really new at this. Does anyone have any code that can do a query
    >> >from
    >> >> Excel in an access file, pulling out a record of their choice?
    >> >>
    >> >> Thanks
    >> >>
    >> >> Harry
    >> >>
    >> >>
    >> >
    >> >

    >>

    >
    >



  9. #9
    Tom Ogilvy
    Guest

    Re: VB Code or Excel macro to run Query/Import on Access file

    Unless the last name is listed in a cell, you wouldn't dimension UserRange
    as a range

    Sub GetUserRange()
    Dim UserRange As String

    Prompt = "Select Last Name."
    Title = "Select Last Name"

    ' Display the Input Box
    On Error Resume Next
    UserRange = Application.InputBox( _
    Prompt:=Prompt, _
    Title:=Title)


    ' Was the Input Box canceled?
    If UserRange = "" Then
    MsgBox "Canceled."
    Else



    and

    "WHERE (Customers.`Applicant Last Name`=" & _
    UserRange & ")")

    --
    Regards,
    Tom Ogilvy



    "Harry" <[email protected]> wrote in message
    news:[email protected]...
    > Tom, I've taken a macro I found online to prompt for a value, edited it a

    bit, and merged it with the macro I recorded, substituting the variable
    UserRange for the actual last name. The idea is, the macro could ask for
    the client
    > last name, and then substitute the value into the macro doing the query

    into the Access file. It asks me for a name, but then it just says
    "Cancelled".
    >
    > Any things you can point me to? The entire macro is below.
    >
    >
    >
    > ' Test Macro
    > ' Macro recorded 18/02/2005 by Harry Fine
    > '
    > Sub GetUserRange()
    > Dim UserRange As Range
    >
    > Prompt = "Select Last Name."
    > Title = "Select Last Name"
    >
    > ' Display the Input Box
    > On Error Resume Next
    > Set UserRange = Application.InputBox( _
    > Prompt:=Prompt, _
    > Title:=Title, _
    > Type:=2) 'Range Selection
    >
    > ' Was the Input Box canceled?
    > If UserRange Is Nothing Then
    > MsgBox "Canceled."
    > Else
    > With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
    > "ODBC;DSN=MS Access Database;DBQ=C:\Documents and

    Settings\Harry\My
    Documents\Landlord\Access\Clients.mdb;DefaultDir=C:\Documents and" _
    > ), Array( _
    > " Settings\Harry\My Documents\Landlord\Access;DriverId=25;FIL=MS

    Access;MaxBufferSize=2048;PageTimeout=5;" _
    > )), Destination:=Range("A1"))
    > .CommandText = Array( _
    > "SELECT Customers.`Applicant FirstName`, Customers.`Applicant

    Initital`, Customers.`Applicant Last Name`, Customers.`Applicant Gender`,
    Customers.`Day Phone`, Customers.`Evening Phone`, Customers.`Stre" _
    > , _
    > "et Address`, Customers.`Unit #`, Customers.City,

    Customers.Province, Customers.`Postal Code`, Customers.FaxNumber,
    Customers.EmailAddress, Customers.`Second Applicant First Name`,
    Customers.`Second Ap" _
    > , _
    > "plicant Initial`, Customers.`Second Applicant Last Name`,

    Customers.`Second Applicant Gender`" & Chr(13) & "" & Chr(10) & "FROM
    Customers Customers" & Chr(13) & "" & Chr(10) & "WHERE (Customers.`Applicant
    Last
    > Name`=UserRange)" _
    > )
    > .Name = "Query from MS Access Database"
    > .FieldNames = False
    > .RowNumbers = False
    > .FillAdjacentFormulas = False
    > .PreserveFormatting = True
    > .RefreshOnFileOpen = False
    > .BackgroundQuery = True
    > .RefreshStyle = xlOverwriteCells
    > .SavePassword = False
    > .SaveData = True
    > .AdjustColumnWidth = False
    > .RefreshPeriod = 0
    > .PreserveColumnInfo = True
    > .Refresh BackgroundQuery:=False
    > End With
    > End If
    > End Sub
    >
    >
    > Harry
    >
    >
    >
    > On Fri, 18 Feb 2005 10:44:03 -0500, "Tom Ogilvy" <[email protected]> wrote:
    >
    > >You can start here. Look on the left side.
    > >
    > >http://msdn.microsoft.com/data/Default.aspx
    > >
    > >However, you may just want to go back to using MSQuery. Turn on the

    macro
    > >recorder while you do the Data=>Get External Data (or import external

    data)
    > >in the menus and pull down your data. Then turn off the macro recorder

    and
    > >look at what has been recorded. You can make the database a variable in

    the
    > >code recorded, then just add an input prompt for what database.
    > >
    > >--
    > >Regards,
    > >Tom Ogilvy
    > >
    > >"Harry" <[email protected]> wrote in message
    > >news:[email protected]...
    > >> Tom, it may be over my head. As I read about this, it seems I need

    to
    > >have
    > >> ADO or DAO running (I don't know what they are, nor the difference) and

    > >perhaps
    > >> a database, Oracle or SQL. Is there a faq that takes me through ADO or

    > >DAO, so
    > >> I can get the big picture of what has to be happening?
    > >>
    > >> Harry
    > >>
    > >>
    > >> On Fri, 18 Feb 2005 08:44:41 -0500, "Tom Ogilvy" <[email protected]>

    wrote:
    > >>
    > >> >http://www.erlandsendata.no/english/...php?t=envbadac
    > >> >
    > >> >--
    > >> >Regards,
    > >> >Tom Ogilvy
    > >> >
    > >> >
    > >> >"Harry" <[email protected]> wrote in message
    > >> >news:[email protected]...
    > >> >>
    > >> >> Hello
    > >> >>
    > >> >> I'd like to be able to press a button on an Excel sheet and start

    the
    > >> >process of
    > >> >> running the MSQUERY function. I tried just recording keystrokes as

    a
    > >> >macro,
    > >> >> clicking the following while in record mode:
    > >> >>
    > >> >> Data/Import External Data/New Database Query/MS Access

    Database/Clicked
    > >on
    > >> >> Database Name
    > >> >>
    > >> >> But nothing got recorded, it didn't work. I'm trying to save myself

    > >all
    > >> >those
    > >> >> keystrokes by automating the task right up to the point in the query

    > >where
    > >> >I
    > >> >> select which record I to import from the Access file.
    > >> >>
    > >> >> I know enough about macros and VB to create a button and launch the

    > >macro,
    > >> >but
    > >> >> I'm really new at this. Does anyone have any code that can do a

    query
    > >> >from
    > >> >> Excel in an access file, pulling out a record of their choice?
    > >> >>
    > >> >> Thanks
    > >> >>
    > >> >> Harry
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>

    > >
    > >

    >




  10. #10
    Harry
    Guest

    Re: VB Code or Excel macro to run Query/Import on Access file

    I've cropped some of this thread off. Is this better? Damn line wraps are awful. These underbars I see everywhere, are they to continue lines? WHen I put in this:


    "WHERE (Customers.`Applicant Last Name`="&UserRange&")")

    I get an error. Expected: List separator or )



    Sub GetUserRange()
    '
    ' Test Macro
    ' Macro recorded by Harry Fine
    '
    Dim UserRange As String
    Prompt = "Select Last Name."
    Title = "Select Last Name"

    ' Display the Input Box
    On Error Resume Next
    UserRange = Application.InputBox( _
    Prompt:=Prompt, _
    Title:=Title)

    ' Was the Input Box canceled?
    If UserRange = "" Then
    MsgBox "Canceled."
    Else

    '"WHERE (Customers.`Applicant Last Name`=" & _
    UserRange & ")")

    '
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
    "ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\Harry\My Documents\Landlord\Access\Clients.mdb;DefaultDir=C:\Documents and" _
    ), Array( _
    " Settings\Harry\My Documents\Landlord\Access;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
    )), Destination:=Range("A1"))
    .CommandText = Array( _
    "SELECT Customers.`Applicant FirstName`, Customers.`Applicant Initital`, Customers.`Applicant Last Name`, Customers.`Applicant Gender`, Customers.`Day Phone`, Customers.`Evening Phone`, Customers.`Stre" _
    , _
    "et Address`, Customers.`Unit #`, Customers.City, Customers.Province, Customers.`Postal Code`, Customers.FaxNumber, Customers.EmailAddress, Customers.`Second Applicant First Name`, Customers.`Second Ap" _
    , _
    "plicant Initial`, Customers.`Second Applicant Last Name`, Customers.`Second Applicant Gender`" & Chr(13) & "" & Chr(10) & "FROM Customers Customers" & Chr(13) & "" & Chr(10) & "WHERE (Customers.`Applicant Last
    Name`="&UserRange&")")
    .Name = "Query from MS Access Database"
    .FieldNames = False
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
    End With
    End If
    End Sub


    Harry


    On Fri, 18 Feb 2005 15:40:23 -0500, "Tom Ogilvy" <[email protected]> wrote:

    >Unless the last name is listed in a cell, you wouldn't dimension UserRange
    >as a range
    >
    >Sub GetUserRange()
    > Dim UserRange As String
    >
    > Prompt = "Select Last Name."
    > Title = "Select Last Name"
    >
    >' Display the Input Box
    > On Error Resume Next
    > UserRange = Application.InputBox( _
    > Prompt:=Prompt, _
    > Title:=Title)
    >
    >
    >' Was the Input Box canceled?
    > If UserRange = "" Then
    > MsgBox "Canceled."
    > Else
    >
    >
    >
    >and
    >
    >"WHERE (Customers.`Applicant Last Name`=" & _
    > UserRange & ")")
    >
    >--
    >Regards,
    >Tom Ogilvy
    >



  11. #11
    Tom Ogilvy
    Guest

    Re: VB Code or Excel macro to run Query/Import on Access file

    "WHERE (Customers.`Applicant Last Name`=" & UserRange & ")")

    Put spaces before and after you ampersands.

    --
    Regards,
    Tom Ogilvy


    "Harry" <[email protected]> wrote in message
    news:[email protected]...
    > I've cropped some of this thread off. Is this better? Damn line wraps

    are awful. These underbars I see everywhere, are they to continue lines?
    WHen I put in this:
    >
    >
    > "WHERE (Customers.`Applicant Last Name`="&UserRange&")")
    >
    > I get an error. Expected: List separator or )
    >
    >
    >
    > Sub GetUserRange()
    > '
    > ' Test Macro
    > ' Macro recorded by Harry Fine
    > '
    > Dim UserRange As String
    > Prompt = "Select Last Name."
    > Title = "Select Last Name"
    >
    > ' Display the Input Box
    > On Error Resume Next
    > UserRange = Application.InputBox( _
    > Prompt:=Prompt, _
    > Title:=Title)
    >
    > ' Was the Input Box canceled?
    > If UserRange = "" Then
    > MsgBox "Canceled."
    > Else
    >
    > '"WHERE (Customers.`Applicant Last Name`=" & _
    > UserRange & ")")
    >
    > '
    > With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
    > "ODBC;DSN=MS Access Database;DBQ=C:\Documents and

    Settings\Harry\My
    Documents\Landlord\Access\Clients.mdb;DefaultDir=C:\Documents and" _
    > ), Array( _
    > " Settings\Harry\My Documents\Landlord\Access;DriverId=25;FIL=MS

    Access;MaxBufferSize=2048;PageTimeout=5;" _
    > )), Destination:=Range("A1"))
    > .CommandText = Array( _
    > "SELECT Customers.`Applicant FirstName`, Customers.`Applicant

    Initital`, Customers.`Applicant Last Name`, Customers.`Applicant Gender`,
    Customers.`Day Phone`, Customers.`Evening Phone`, Customers.`Stre" _
    > , _
    > "et Address`, Customers.`Unit #`, Customers.City,

    Customers.Province, Customers.`Postal Code`, Customers.FaxNumber,
    Customers.EmailAddress, Customers.`Second Applicant First Name`,
    Customers.`Second Ap" _
    > , _
    > "plicant Initial`, Customers.`Second Applicant Last Name`,

    Customers.`Second Applicant Gender`" & Chr(13) & "" & Chr(10) & "FROM
    Customers Customers" & Chr(13) & "" & Chr(10) & "WHERE (Customers.`Applicant
    Last
    > Name`="&UserRange&")")
    > .Name = "Query from MS Access Database"
    > .FieldNames = False
    > .RowNumbers = False
    > .FillAdjacentFormulas = False
    > .PreserveFormatting = True
    > .RefreshOnFileOpen = False
    > .BackgroundQuery = True
    > .RefreshStyle = xlOverwriteCells
    > .SavePassword = False
    > .SaveData = True
    > .AdjustColumnWidth = False
    > .RefreshPeriod = 0
    > .PreserveColumnInfo = True
    > .Refresh BackgroundQuery:=False
    > End With
    > End If
    > End Sub
    >
    >
    > Harry
    >
    >
    > On Fri, 18 Feb 2005 15:40:23 -0500, "Tom Ogilvy" <[email protected]> wrote:
    >
    > >Unless the last name is listed in a cell, you wouldn't dimension

    UserRange
    > >as a range
    > >
    > >Sub GetUserRange()
    > > Dim UserRange As String
    > >
    > > Prompt = "Select Last Name."
    > > Title = "Select Last Name"
    > >
    > >' Display the Input Box
    > > On Error Resume Next
    > > UserRange = Application.InputBox( _
    > > Prompt:=Prompt, _
    > > Title:=Title)
    > >
    > >
    > >' Was the Input Box canceled?
    > > If UserRange = "" Then
    > > MsgBox "Canceled."
    > > Else
    > >
    > >
    > >
    > >and
    > >
    > >"WHERE (Customers.`Applicant Last Name`=" & _
    > > UserRange & ")")
    > >
    > >--
    > >Regards,
    > >Tom Ogilvy
    > >

    >




  12. #12
    Harry
    Guest

    Re: VB Code or Excel macro to run Query/Import on Access file

    On Sat, 19 Feb 2005 11:32:20 -0500, "Tom Ogilvy" <[email protected]> wrote:

    I'm missing something Tom. It now compiles OK, and when I run it, but clicking Tools/Macro/Macros/Run GetUserRange, it prompts for the Last Name as expected, but then when I enter the last name FINE, which I've double checked is
    in the Access database, it doesn't seem to run the lower part of the script. Nothing appears on the screen. No data is returned from Access.

    Here's the whole script again, with the spaces around the & as you suggested.

    Thank you Tom.

    Harry


    Sub GetUserRange()
    '
    ' Test Macro
    ' Macro recorded by Harry Fine
    '
    Dim UserRange As String
    Prompt = "Select Last Name."
    Title = "Select Last Name"

    ' Display the Input Box
    On Error Resume Next
    UserRange = Application.InputBox( _
    Prompt:=Prompt, _
    Title:=Title)

    ' Was the Input Box canceled?
    If UserRange = "" Then
    MsgBox "Canceled."
    Else

    '
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
    "ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\Harry\My Documents\Landlord\Access\Clients.mdb;DefaultDir=C:\Documents and" _
    ), Array( _
    " Settings\Harry\My Documents\Landlord\Access;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
    )), Destination:=Range("A1"))
    .CommandText = Array( _
    "SELECT Customers.`Applicant FirstName`, Customers.`Applicant Initital`, Customers.`Applicant Last Name`, Customers.`Applicant Gender`, Customers.`Day Phone`, Customers.`Evening Phone`, Customers.`Stre" _
    , _
    "et Address`, Customers.`Unit #`, Customers.City, Customers.Province, Customers.`Postal Code`, Customers.FaxNumber, Customers.EmailAddress, Customers.`Second Applicant First Name`, Customers.`Second Ap" _
    , _
    "plicant Initial`, Customers.`Second Applicant Last Name`, Customers.`Second Applicant Gender`" & Chr(13) & "" & Chr(10) & "FROM Customers Customers" & Chr(13) & "" & Chr(10) & "WHERE (Customers.`Applicant Last Name`=" &
    UserRange & ")")
    .Name = "Query from MS Access Database"
    .FieldNames = False
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
    End With
    End If
    End Sub




    >"WHERE (Customers.`Applicant Last Name`=" & UserRange & ")")
    >
    >Put spaces before and after you ampersands.
    >
    >--
    >Regards,
    >Tom Ogilvy
    >
    >
    >"Harry" <[email protected]> wrote in message
    >news:[email protected]...
    >> I've cropped some of this thread off. Is this better? Damn line wraps

    >are awful. These underbars I see everywhere, are they to continue lines?
    >WHen I put in this:
    >>
    >>
    >> "WHERE (Customers.`Applicant Last Name`="&UserRange&")")
    >>
    >> I get an error. Expected: List separator or )
    >>
    >>



  13. #13
    Tom Ogilvy
    Guest

    Re: VB Code or Excel macro to run Query/Import on Access file

    Before you doctored the code, did it look like

    "WHERE (Customers.`Applicant Last Name`=Smith)"
    or did it look like

    "WHERE (Customers.`Applicant Last Name`=""Smith"")"

    or perhaps with single quotes

    "WHERE (Customers.`Applicant Last Name`='Smith')"

    Whatever it looked like, all you want to do is replace the Smith part.
    However, if double quotes are involved you will have to check to make sure
    you have a legitimate string.

    --
    Regards,
    Tom Ogilvy


    "Harry" <[email protected]> wrote in message
    news:[email protected]...
    > On Sat, 19 Feb 2005 11:32:20 -0500, "Tom Ogilvy" <[email protected]> wrote:
    >
    > I'm missing something Tom. It now compiles OK, and when I run it, but

    clicking Tools/Macro/Macros/Run GetUserRange, it prompts for the Last Name
    as expected, but then when I enter the last name FINE, which I've double
    checked is
    > in the Access database, it doesn't seem to run the lower part of the

    script. Nothing appears on the screen. No data is returned from Access.
    >
    > Here's the whole script again, with the spaces around the & as you

    suggested.
    >
    > Thank you Tom.
    >
    > Harry
    >
    >
    > Sub GetUserRange()
    > '
    > ' Test Macro
    > ' Macro recorded by Harry Fine
    > '
    > Dim UserRange As String
    > Prompt = "Select Last Name."
    > Title = "Select Last Name"
    >
    > ' Display the Input Box
    > On Error Resume Next
    > UserRange = Application.InputBox( _
    > Prompt:=Prompt, _
    > Title:=Title)
    >
    > ' Was the Input Box canceled?
    > If UserRange = "" Then
    > MsgBox "Canceled."
    > Else
    >
    > '
    > With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
    > "ODBC;DSN=MS Access Database;DBQ=C:\Documents and

    Settings\Harry\My
    Documents\Landlord\Access\Clients.mdb;DefaultDir=C:\Documents and" _
    > ), Array( _
    > " Settings\Harry\My Documents\Landlord\Access;DriverId=25;FIL=MS

    Access;MaxBufferSize=2048;PageTimeout=5;" _
    > )), Destination:=Range("A1"))
    > .CommandText = Array( _
    > "SELECT Customers.`Applicant FirstName`, Customers.`Applicant

    Initital`, Customers.`Applicant Last Name`, Customers.`Applicant Gender`,
    Customers.`Day Phone`, Customers.`Evening Phone`, Customers.`Stre" _
    > , _
    > "et Address`, Customers.`Unit #`, Customers.City,

    Customers.Province, Customers.`Postal Code`, Customers.FaxNumber,
    Customers.EmailAddress, Customers.`Second Applicant First Name`,
    Customers.`Second Ap" _
    > , _
    > "plicant Initial`, Customers.`Second Applicant Last Name`,

    Customers.`Second Applicant Gender`" & Chr(13) & "" & Chr(10) & "FROM
    Customers Customers" & Chr(13) & "" & Chr(10) & "WHERE (Customers.`Applicant
    Last Name`=" &
    > UserRange & ")")
    > .Name = "Query from MS Access Database"
    > .FieldNames = False
    > .RowNumbers = False
    > .FillAdjacentFormulas = False
    > .PreserveFormatting = True
    > .RefreshOnFileOpen = False
    > .BackgroundQuery = True
    > .RefreshStyle = xlOverwriteCells
    > .SavePassword = False
    > .SaveData = True
    > .AdjustColumnWidth = False
    > .RefreshPeriod = 0
    > .PreserveColumnInfo = True
    > .Refresh BackgroundQuery:=False
    > End With
    > End If
    > End Sub
    >
    >
    >
    >
    > >"WHERE (Customers.`Applicant Last Name`=" & UserRange & ")")
    > >
    > >Put spaces before and after you ampersands.
    > >
    > >--
    > >Regards,
    > >Tom Ogilvy
    > >
    > >
    > >"Harry" <[email protected]> wrote in message
    > >news:[email protected]...
    > >> I've cropped some of this thread off. Is this better? Damn line wraps

    > >are awful. These underbars I see everywhere, are they to continue lines?
    > >WHen I put in this:
    > >>
    > >>
    > >> "WHERE (Customers.`Applicant Last Name`="&UserRange&")")
    > >>
    > >> I get an error. Expected: List separator or )
    > >>
    > >>

    >




  14. #14
    Harry
    Guest

    Re: VB Code or Excel macro to run Query/Import on Access file

    Tom

    Before doctoring, the code generated by the macro recorder looked like this:

    1. "WHERE (Customers.`Applicant Last Name`='Fine')")

    so I changed it to:

    2. "WHERE (Customers.`Applicant Last Name`=(" & UserRange & ")")

    but I've also tried it as you gave it to me without the extra opening bracket::

    3. "WHERE (Customers.`Applicant Last Name`=" & UserRange & ")")


    In example number 1, it returns the proper record from the Access database, no matter what name I respond to the prompt with. That's as it should be, as the value of Applicant Last Name is set to "Fine"

    In example number 2, it prompts me for the last name, but then doesn't retieve any data.

    In example 3, it prompts me for the last name, but then doesn't retieve any data.


    In #3, the one you suggested, you've used the ampersands to ensure it is seen as a variable, not a literal, and for some reason you've added a closing bracket at the end of the line, even though it isn't matched by an opening
    bracket.

    That's why I tried number 2, adding an opening bracket before the variable, but it didn't make a difference, it still doesn't return the data from Excel. So...to answer your question, before I doctored it, it looked like your
    third example below.

    I'm stuck.

    Harry


    On Mon, 21 Feb 2005 13:05:28 -0500, "Tom Ogilvy" <[email protected]> wrote:

    >Before you doctored the code, did it look like
    >
    >"WHERE (Customers.`Applicant Last Name`=Smith)"
    >or did it look like
    >
    >"WHERE (Customers.`Applicant Last Name`=""Smith"")"
    >
    >or perhaps with single quotes
    >
    >"WHERE (Customers.`Applicant Last Name`='Smith')"
    >
    >Whatever it looked like, all you want to do is replace the Smith part.
    >However, if double quotes are involved you will have to check to make sure
    >you have a legitimate string.
    >
    >--
    >Regards,
    >Tom Ogilvy
    >
    >
    >"Harry" <[email protected]> wrote in message
    >news:[email protected]...
    >> On Sat, 19 Feb 2005 11:32:20 -0500, "Tom Ogilvy" <[email protected]> wrote:
    >>
    >> I'm missing something Tom. It now compiles OK, and when I run it, but

    >clicking Tools/Macro/Macros/Run GetUserRange, it prompts for the Last Name
    >as expected, but then when I enter the last name FINE, which I've double
    >checked is
    >> in the Access database, it doesn't seem to run the lower part of the

    >script. Nothing appears on the screen. No data is returned from Access.
    >>
    >> Here's the whole script again, with the spaces around the & as you

    >suggested.
    >>
    >> Thank you Tom.
    >>
    >> Harry
    >>
    >>
    >> Sub GetUserRange()
    >> '
    >> ' Test Macro
    >> ' Macro recorded by Harry Fine
    >> '
    >> Dim UserRange As String
    >> Prompt = "Select Last Name."
    >> Title = "Select Last Name"
    >>
    >> ' Display the Input Box
    >> On Error Resume Next
    >> UserRange = Application.InputBox( _
    >> Prompt:=Prompt, _
    >> Title:=Title)
    >>
    >> ' Was the Input Box canceled?
    >> If UserRange = "" Then
    >> MsgBox "Canceled."
    >> Else
    >>
    >> '
    >> With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
    >> "ODBC;DSN=MS Access Database;DBQ=C:\Documents and

    >Settings\Harry\My
    >Documents\Landlord\Access\Clients.mdb;DefaultDir=C:\Documents and" _
    >> ), Array( _
    >> " Settings\Harry\My Documents\Landlord\Access;DriverId=25;FIL=MS

    >Access;MaxBufferSize=2048;PageTimeout=5;" _
    >> )), Destination:=Range("A1"))
    >> .CommandText = Array( _
    >> "SELECT Customers.`Applicant FirstName`, Customers.`Applicant

    >Initital`, Customers.`Applicant Last Name`, Customers.`Applicant Gender`,
    >Customers.`Day Phone`, Customers.`Evening Phone`, Customers.`Stre" _
    >> , _
    >> "et Address`, Customers.`Unit #`, Customers.City,

    >Customers.Province, Customers.`Postal Code`, Customers.FaxNumber,
    >Customers.EmailAddress, Customers.`Second Applicant First Name`,
    >Customers.`Second Ap" _
    >> , _
    >> "plicant Initial`, Customers.`Second Applicant Last Name`,

    >Customers.`Second Applicant Gender`" & Chr(13) & "" & Chr(10) & "FROM
    >Customers Customers" & Chr(13) & "" & Chr(10) & "WHERE (Customers.`Applicant
    >Last Name`=" &
    >> UserRange & ")")
    >> .Name = "Query from MS Access Database"
    >> .FieldNames = False
    >> .RowNumbers = False
    >> .FillAdjacentFormulas = False
    >> .PreserveFormatting = True
    >> .RefreshOnFileOpen = False
    >> .BackgroundQuery = True
    >> .RefreshStyle = xlOverwriteCells
    >> .SavePassword = False
    >> .SaveData = True
    >> .AdjustColumnWidth = False
    >> .RefreshPeriod = 0
    >> .PreserveColumnInfo = True
    >> .Refresh BackgroundQuery:=False
    >> End With
    >> End If
    >> End Sub
    >>
    >>
    >>
    >>
    >> >"WHERE (Customers.`Applicant Last Name`=" & UserRange & ")")
    >> >
    >> >Put spaces before and after you ampersands.
    >> >
    >> >--
    >> >Regards,
    >> >Tom Ogilvy
    >> >
    >> >
    >> >"Harry" <[email protected]> wrote in message
    >> >news:[email protected]...
    >> >> I've cropped some of this thread off. Is this better? Damn line wraps
    >> >are awful. These underbars I see everywhere, are they to continue lines?
    >> >WHen I put in this:
    >> >>
    >> >>
    >> >> "WHERE (Customers.`Applicant Last Name`="&UserRange&")")
    >> >>
    >> >> I get an error. Expected: List separator or )
    >> >>
    >> >>

    >>

    >
    >



  15. #15
    Tom Ogilvy
    Guest

    Re: VB Code or Excel macro to run Query/Import on Access file

    Here is the key. From looking at the original, it appears it wants the name
    in single quotes:


    "WHERE (Customers.`Applicant Last Name`='" & UserRange & "')")

    The match for the last bracket was way back toward the start. If it isn't
    needed, you will get an error and you can remove it.

    --
    Regards,
    Tom Ogilvy

    "Harry" <[email protected]> wrote in message
    news:[email protected]...
    > Tom
    >
    > Before doctoring, the code generated by the macro recorder looked like

    this:
    >
    > 1. "WHERE (Customers.`Applicant Last Name`='Fine')")
    >
    > so I changed it to:
    >
    > 2. "WHERE (Customers.`Applicant Last Name`=(" & UserRange & ")")
    >
    > but I've also tried it as you gave it to me without the extra opening

    bracket::
    >
    > 3. "WHERE (Customers.`Applicant Last Name`=" & UserRange & ")")
    >
    >
    > In example number 1, it returns the proper record from the Access

    database, no matter what name I respond to the prompt with. That's as it
    should be, as the value of Applicant Last Name is set to "Fine"
    >
    > In example number 2, it prompts me for the last name, but then doesn't

    retieve any data.
    >
    > In example 3, it prompts me for the last name, but then doesn't retieve

    any data.
    >
    >
    > In #3, the one you suggested, you've used the ampersands to ensure it is

    seen as a variable, not a literal, and for some reason you've added a
    closing bracket at the end of the line, even though it isn't matched by an
    opening
    > bracket.
    >
    > That's why I tried number 2, adding an opening bracket before the

    variable, but it didn't make a difference, it still doesn't return the data
    from Excel. So...to answer your question, before I doctored it, it looked
    like your
    > third example below.
    >
    > I'm stuck.
    >
    > Harry
    >
    >
    > On Mon, 21 Feb 2005 13:05:28 -0500, "Tom Ogilvy" <[email protected]> wrote:
    >
    > >Before you doctored the code, did it look like
    > >
    > >"WHERE (Customers.`Applicant Last Name`=Smith)"
    > >or did it look like
    > >
    > >"WHERE (Customers.`Applicant Last Name`=""Smith"")"
    > >
    > >or perhaps with single quotes
    > >
    > >"WHERE (Customers.`Applicant Last Name`='Smith')"
    > >
    > >Whatever it looked like, all you want to do is replace the Smith part.
    > >However, if double quotes are involved you will have to check to make

    sure
    > >you have a legitimate string.
    > >
    > >--
    > >Regards,
    > >Tom Ogilvy
    > >
    > >
    > >"Harry" <[email protected]> wrote in message
    > >news:[email protected]...
    > >> On Sat, 19 Feb 2005 11:32:20 -0500, "Tom Ogilvy" <[email protected]>

    wrote:
    > >>
    > >> I'm missing something Tom. It now compiles OK, and when I run it, but

    > >clicking Tools/Macro/Macros/Run GetUserRange, it prompts for the Last

    Name
    > >as expected, but then when I enter the last name FINE, which I've double
    > >checked is
    > >> in the Access database, it doesn't seem to run the lower part of the

    > >script. Nothing appears on the screen. No data is returned from Access.
    > >>
    > >> Here's the whole script again, with the spaces around the & as you

    > >suggested.
    > >>
    > >> Thank you Tom.
    > >>
    > >> Harry
    > >>
    > >>
    > >> Sub GetUserRange()
    > >> '
    > >> ' Test Macro
    > >> ' Macro recorded by Harry Fine
    > >> '
    > >> Dim UserRange As String
    > >> Prompt = "Select Last Name."
    > >> Title = "Select Last Name"
    > >>
    > >> ' Display the Input Box
    > >> On Error Resume Next
    > >> UserRange = Application.InputBox( _
    > >> Prompt:=Prompt, _
    > >> Title:=Title)
    > >>
    > >> ' Was the Input Box canceled?
    > >> If UserRange = "" Then
    > >> MsgBox "Canceled."
    > >> Else
    > >>
    > >> '
    > >> With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
    > >> "ODBC;DSN=MS Access Database;DBQ=C:\Documents and

    > >Settings\Harry\My
    > >Documents\Landlord\Access\Clients.mdb;DefaultDir=C:\Documents and" _
    > >> ), Array( _
    > >> " Settings\Harry\My

    Documents\Landlord\Access;DriverId=25;FIL=MS
    > >Access;MaxBufferSize=2048;PageTimeout=5;" _
    > >> )), Destination:=Range("A1"))
    > >> .CommandText = Array( _
    > >> "SELECT Customers.`Applicant FirstName`, Customers.`Applicant

    > >Initital`, Customers.`Applicant Last Name`, Customers.`Applicant Gender`,
    > >Customers.`Day Phone`, Customers.`Evening Phone`, Customers.`Stre" _
    > >> , _
    > >> "et Address`, Customers.`Unit #`, Customers.City,

    > >Customers.Province, Customers.`Postal Code`, Customers.FaxNumber,
    > >Customers.EmailAddress, Customers.`Second Applicant First Name`,
    > >Customers.`Second Ap" _
    > >> , _
    > >> "plicant Initial`, Customers.`Second Applicant Last Name`,

    > >Customers.`Second Applicant Gender`" & Chr(13) & "" & Chr(10) & "FROM
    > >Customers Customers" & Chr(13) & "" & Chr(10) & "WHERE

    (Customers.`Applicant
    > >Last Name`=" &
    > >> UserRange & ")")
    > >> .Name = "Query from MS Access Database"
    > >> .FieldNames = False
    > >> .RowNumbers = False
    > >> .FillAdjacentFormulas = False
    > >> .PreserveFormatting = True
    > >> .RefreshOnFileOpen = False
    > >> .BackgroundQuery = True
    > >> .RefreshStyle = xlOverwriteCells
    > >> .SavePassword = False
    > >> .SaveData = True
    > >> .AdjustColumnWidth = False
    > >> .RefreshPeriod = 0
    > >> .PreserveColumnInfo = True
    > >> .Refresh BackgroundQuery:=False
    > >> End With
    > >> End If
    > >> End Sub
    > >>
    > >>
    > >>
    > >>
    > >> >"WHERE (Customers.`Applicant Last Name`=" & UserRange & ")")
    > >> >
    > >> >Put spaces before and after you ampersands.
    > >> >
    > >> >--
    > >> >Regards,
    > >> >Tom Ogilvy
    > >> >
    > >> >
    > >> >"Harry" <[email protected]> wrote in message
    > >> >news:[email protected]...
    > >> >> I've cropped some of this thread off. Is this better? Damn line

    wraps
    > >> >are awful. These underbars I see everywhere, are they to continue

    lines?
    > >> >WHen I put in this:
    > >> >>
    > >> >>
    > >> >> "WHERE (Customers.`Applicant Last Name`="&UserRange&")")
    > >> >>
    > >> >> I get an error. Expected: List separator or )
    > >> >>
    > >> >>
    > >>

    > >
    > >

    >




  16. #16
    Harry
    Guest

    Re: VB Code or Excel macro to run Query/Import on Access file

    Tom, you won't believe it, but I'm done. It looks up the correct Access record, brings it into the spreadsheet in the right place. This is going to be a big time-saver for me. I've got hundreds of people in the Access database,
    and unless they happen to have the same last name, this will work perfectly.

    Thank you for eveything. By the way, I've seen your name here often helping out. Are you a moderator, or employee of Microsoft or something?

    Harry


    On Tue, 22 Feb 2005 10:23:08 -0500, "Tom Ogilvy" <[email protected]> wrote:

    >Here is the key. From looking at the original, it appears it wants the name
    >in single quotes:
    >
    >
    > "WHERE (Customers.`Applicant Last Name`='" & UserRange & "')")
    >
    >The match for the last bracket was way back toward the start. If it isn't
    >needed, you will get an error and you can remove it.
    >
    >--
    >Regards,
    >Tom Ogilvy
    >
    >"Harry" <[email protected]> wrote in message
    >news:[email protected]...
    >> Tom
    >>
    >> Before doctoring, the code generated by the macro recorder looked like

    >this:
    >>
    >> 1. "WHERE (Customers.`Applicant Last Name`='Fine')")
    >>
    >> so I changed it to:
    >>
    >> 2. "WHERE (Customers.`Applicant Last Name`=(" & UserRange & ")")
    >>
    >> but I've also tried it as you gave it to me without the extra opening

    >bracket::
    >>
    >> 3. "WHERE (Customers.`Applicant Last Name`=" & UserRange & ")")
    >>
    >>
    >> In example number 1, it returns the proper record from the Access

    >database, no matter what name I respond to the prompt with. That's as it
    >should be, as the value of Applicant Last Name is set to "Fine"
    >>
    >> In example number 2, it prompts me for the last name, but then doesn't

    >retieve any data.
    >>
    >> In example 3, it prompts me for the last name, but then doesn't retieve

    >any data.
    >>
    >>
    >> In #3, the one you suggested, you've used the ampersands to ensure it is

    >seen as a variable, not a literal, and for some reason you've added a
    >closing bracket at the end of the line, even though it isn't matched by an
    >opening
    >> bracket.
    >>
    >> That's why I tried number 2, adding an opening bracket before the

    >variable, but it didn't make a difference, it still doesn't return the data
    >from Excel. So...to answer your question, before I doctored it, it looked
    >like your
    >> third example below.
    >>
    >> I'm stuck.
    >>
    >> Harry
    >>
    >>
    >> On Mon, 21 Feb 2005 13:05:28 -0500, "Tom Ogilvy" <[email protected]> wrote:
    >>
    >> >Before you doctored the code, did it look like
    >> >
    >> >"WHERE (Customers.`Applicant Last Name`=Smith)"
    >> >or did it look like
    >> >
    >> >"WHERE (Customers.`Applicant Last Name`=""Smith"")"
    >> >
    >> >or perhaps with single quotes
    >> >
    >> >"WHERE (Customers.`Applicant Last Name`='Smith')"
    >> >
    >> >Whatever it looked like, all you want to do is replace the Smith part.
    >> >However, if double quotes are involved you will have to check to make

    >sure
    >> >you have a legitimate string.
    >> >
    >> >--
    >> >Regards,
    >> >Tom Ogilvy
    >> >
    >> >
    >> >"Harry" <[email protected]> wrote in message
    >> >news:[email protected]...
    >> >> On Sat, 19 Feb 2005 11:32:20 -0500, "Tom Ogilvy" <[email protected]>

    >wrote:
    >> >>
    >> >> I'm missing something Tom. It now compiles OK, and when I run it, but
    >> >clicking Tools/Macro/Macros/Run GetUserRange, it prompts for the Last

    >Name
    >> >as expected, but then when I enter the last name FINE, which I've double
    >> >checked is
    >> >> in the Access database, it doesn't seem to run the lower part of the
    >> >script. Nothing appears on the screen. No data is returned from Access.
    >> >>
    >> >> Here's the whole script again, with the spaces around the & as you
    >> >suggested.
    >> >>
    >> >> Thank you Tom.
    >> >>
    >> >> Harry
    >> >>
    >> >>
    >> >> Sub GetUserRange()
    >> >> '
    >> >> ' Test Macro
    >> >> ' Macro recorded by Harry Fine
    >> >> '
    >> >> Dim UserRange As String
    >> >> Prompt = "Select Last Name."
    >> >> Title = "Select Last Name"
    >> >>
    >> >> ' Display the Input Box
    >> >> On Error Resume Next
    >> >> UserRange = Application.InputBox( _
    >> >> Prompt:=Prompt, _
    >> >> Title:=Title)
    >> >>
    >> >> ' Was the Input Box canceled?
    >> >> If UserRange = "" Then
    >> >> MsgBox "Canceled."
    >> >> Else
    >> >>
    >> >> '
    >> >> With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
    >> >> "ODBC;DSN=MS Access Database;DBQ=C:\Documents and
    >> >Settings\Harry\My
    >> >Documents\Landlord\Access\Clients.mdb;DefaultDir=C:\Documents and" _
    >> >> ), Array( _
    >> >> " Settings\Harry\My

    >Documents\Landlord\Access;DriverId=25;FIL=MS
    >> >Access;MaxBufferSize=2048;PageTimeout=5;" _
    >> >> )), Destination:=Range("A1"))
    >> >> .CommandText = Array( _
    >> >> "SELECT Customers.`Applicant FirstName`, Customers.`Applicant
    >> >Initital`, Customers.`Applicant Last Name`, Customers.`Applicant Gender`,
    >> >Customers.`Day Phone`, Customers.`Evening Phone`, Customers.`Stre" _
    >> >> , _
    >> >> "et Address`, Customers.`Unit #`, Customers.City,
    >> >Customers.Province, Customers.`Postal Code`, Customers.FaxNumber,
    >> >Customers.EmailAddress, Customers.`Second Applicant First Name`,
    >> >Customers.`Second Ap" _
    >> >> , _
    >> >> "plicant Initial`, Customers.`Second Applicant Last Name`,
    >> >Customers.`Second Applicant Gender`" & Chr(13) & "" & Chr(10) & "FROM
    >> >Customers Customers" & Chr(13) & "" & Chr(10) & "WHERE

    >(Customers.`Applicant
    >> >Last Name`=" &
    >> >> UserRange & ")")
    >> >> .Name = "Query from MS Access Database"
    >> >> .FieldNames = False
    >> >> .RowNumbers = False
    >> >> .FillAdjacentFormulas = False
    >> >> .PreserveFormatting = True
    >> >> .RefreshOnFileOpen = False
    >> >> .BackgroundQuery = True
    >> >> .RefreshStyle = xlOverwriteCells
    >> >> .SavePassword = False
    >> >> .SaveData = True
    >> >> .AdjustColumnWidth = False
    >> >> .RefreshPeriod = 0
    >> >> .PreserveColumnInfo = True
    >> >> .Refresh BackgroundQuery:=False
    >> >> End With
    >> >> End If
    >> >> End Sub
    >> >>
    >> >>
    >> >>
    >> >>
    >> >> >"WHERE (Customers.`Applicant Last Name`=" & UserRange & ")")
    >> >> >
    >> >> >Put spaces before and after you ampersands.
    >> >> >
    >> >> >--
    >> >> >Regards,
    >> >> >Tom Ogilvy
    >> >> >
    >> >> >
    >> >> >"Harry" <[email protected]> wrote in message
    >> >> >news:[email protected]...
    >> >> >> I've cropped some of this thread off. Is this better? Damn line

    >wraps
    >> >> >are awful. These underbars I see everywhere, are they to continue

    >lines?
    >> >> >WHen I put in this:
    >> >> >>
    >> >> >>
    >> >> >> "WHERE (Customers.`Applicant Last Name`="&UserRange&")")
    >> >> >>
    >> >> >> I get an error. Expected: List separator or )
    >> >> >>
    >> >> >>
    >> >>
    >> >
    >> >

    >>

    >
    >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1