Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code or Excel macro to run Query/Import on Access file
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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(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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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(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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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(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 15:40:23 -0500, "Tom Ogilvy" 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message .. . 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(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 15:40:23 -0500, "Tom Ogilvy" 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code or Excel macro to run Query/Import on Access file
On Sat, 19 Feb 2005 11:32:20 -0500, "Tom Ogilvy" 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(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 "WHERE (Customers.`Applicant Last Name`=" & UserRange & ")") Put spaces before and after you ampersands. -- Regards, Tom Ogilvy "Harry" wrote in message . .. 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 ) |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message .. . On Sat, 19 Feb 2005 11:32:20 -0500, "Tom Ogilvy" 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(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 "WHERE (Customers.`Applicant Last Name`=" & UserRange & ")") Put spaces before and after you ampersands. -- Regards, Tom Ogilvy "Harry" wrote in message . .. 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 ) |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" 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" wrote in message . .. On Sat, 19 Feb 2005 11:32:20 -0500, "Tom Ogilvy" 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(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 "WHERE (Customers.`Applicant Last Name`=" & UserRange & ")") Put spaces before and after you ampersands. -- Regards, Tom Ogilvy "Harry" wrote in message . .. 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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" 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" wrote in message . .. On Sat, 19 Feb 2005 11:32:20 -0500, "Tom Ogilvy" 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(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 "WHERE (Customers.`Applicant Last Name`=" & UserRange & ")") Put spaces before and after you ampersands. -- Regards, Tom Ogilvy "Harry" wrote in message . .. 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" 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" wrote in message .. . 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" 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" wrote in message . .. On Sat, 19 Feb 2005 11:32:20 -0500, "Tom Ogilvy" 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(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 "WHERE (Customers.`Applicant Last Name`=" & UserRange & ")") Put spaces before and after you ampersands. -- Regards, Tom Ogilvy "Harry" wrote in message . .. 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import Access query to Excel 2007 | Excel Discussion (Misc queries) | |||
Database query to import from Access to Excel | Excel Discussion (Misc queries) | |||
Vlookup problem with Access Query import into Excel 2000 | New Users to Excel | |||
access query to import to excel | Excel Worksheet Functions | |||
Import query from access to excel, link to template, email on | Links and Linking in Excel |