VB Code or Excel macro to run Query/Import on Access file
in [Programming]
|
From: Tom Ogilvy on 19 Feb 2005 11:32 "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 ) > > > > 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 > > >
From: Harry on 19 Feb 2005 14:02 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 ) >> >>
From: Tom Ogilvy on 21 Feb 2005 13:05 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 ) > >> > >> >
From: Harry on 22 Feb 2005 09:58 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 ) >> >> >> >> >> > >
From: Tom Ogilvy on 22 Feb 2005 10:23 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
|
Next
|
Last
Pages: 1 2 3 4 Prev: Fileformat XLText Next: Excel was unable to load a necessary component |