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

Prev: Fileformat XLText
Next: Excel was unable to load a necessary component

From: Tom Ogilvy on 18 Feb 2005 11:54

---

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" <harry-fine(a)rogers.com> wrote in message
news:42161153.13134515(a)nntp.broadband.rogers.com...
> 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" <twogilvy(a)msn.com> 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" <harry-fine(a)rogers.com> wrote in message
> >news:4215fcd3.7885968(a)nntp.broadband.rogers.com...
> >> 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" <twogilvy(a)msn.com>
wrote:
> >>
> >> >http://www.erlandsendata.no/english/index.php?t=envbadac
> >> >
> >> >--
> >> >Regards,
> >> >Tom Ogilvy
> >> >
> >> >
> >> >"Harry" <harry-fine(a)rogers.com> wrote in message
> >> >news:4215ed18.3859625(a)nntp.broadband.rogers.com...
> >> >>
> >> >> 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
> >> >>
> >> >>
> >> >
> >> >
> >>
> >
> >
>

From: Harry on 18 Feb 2005 13:48

---

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" <twogilvy(a)msn.com> 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" <harry-fine(a)rogers.com> wrote in message
>news:42161153.13134515(a)nntp.broadband.rogers.com...
>> 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" <twogilvy(a)msn.com> 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" <harry-fine(a)rogers.com> wrote in message
>> >news:4215fcd3.7885968(a)nntp.broadband.rogers.com...
>> >> 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" <twogilvy(a)msn.com>
>wrote:
>> >>
>> >> >http://www.erlandsendata.no/english/index.php?t=envbadac
>> >> >
>> >> >--
>> >> >Regards,
>> >> >Tom Ogilvy
>> >> >
>> >> >
>> >> >"Harry" <harry-fine(a)rogers.com> wrote in message
>> >> >news:4215ed18.3859625(a)nntp.broadband.rogers.com...
>> >> >>
>> >> >> 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
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >
>> >
>>
>
>

From: Harry on 18 Feb 2005 14:50

---

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" <twogilvy(a)msn.com> 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" <harry-fine(a)rogers.com> wrote in message
>news:4215fcd3.7885968(a)nntp.broadband.rogers.com...
>> 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" <twogilvy(a)msn.com> wrote:
>>
>> >http://www.erlandsendata.no/english/index.php?t=envbadac
>> >
>> >--
>> >Regards,
>> >Tom Ogilvy
>> >
>> >
>> >"Harry" <harry-fine(a)rogers.com> wrote in message
>> >news:4215ed18.3859625(a)nntp.broadband.rogers.com...
>> >>
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>
>

From: Tom Ogilvy on 18 Feb 2005 15:40

---

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" <harry-fine(a)rogers.com> wrote in message
news:4216465b.26709984(a)nntp.broadband.rogers.com...
> 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" <twogilvy(a)msn.com> 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" <harry-fine(a)rogers.com> wrote in message
> >news:4215fcd3.7885968(a)nntp.broadband.rogers.com...
> >> 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" <twogilvy(a)msn.com>
wrote:
> >>
> >> >http://www.erlandsendata.no/english/index.php?t=envbadac
> >> >
> >> >--
> >> >Regards,
> >> >Tom Ogilvy
> >> >
> >> >
> >> >"Harry" <harry-fine(a)rogers.com> wrote in message
> >> >news:4215ed18.3859625(a)nntp.broadband.rogers.com...
> >> >>
> >> >> 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
> >> >>
> >> >>
> >> >
> >> >
> >>
> >
> >
>

From: Harry on 18 Feb 2005 17:10

---

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" <twogilvy(a)msn.com> 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
>

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