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(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" _
)
.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" 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" wrote in message
. ..
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" 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