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" wrote in message
.. .
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(Arra y( _
"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" 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" wrote in message
.. .
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"
wrote:
http://www.erlandsendata.no/english/...php?t=envbadac
--
Regards,
Tom Ogilvy
"Harry" wrote in message
.. .
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