Prev: Fileformat XLText
Next: Excel was unable to load a necessary component
From: Harry on 22 Feb 2005 14:27
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" <twogilvy(a)msn.com> 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" <harry-fine(a)rogers.com> wrote in message
>news:421b43c9.8994703(a)nntp.broadband.rogers.com...
>> 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" <twogilvy(a)msn.com> 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" <harry-fine(a)rogers.com> wrote in message
>> >news:42178c77.23089750(a)nntp.broadband.rogers.com...
>> >> On Sat, 19 Feb 2005 11:32:20 -0500, "Tom Ogilvy" <twogilvy(a)msn.com>
>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" <harry-fine(a)rogers.com> wrote in message
>> >> >news:42166726.35105125(a)nntp.broadband.rogers.com...
>> >> >> 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 )
>> >> >>
>> >> >>
>> >>
>> >
>> >
>>
>
>

First  |  Prev  | 
Pages: 1 2 3 4
Prev: Fileformat XLText
Next: Excel was unable to load a necessary component