Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newcomer to VBA; trouble with strings?
Hello,
I am a total newcomer to programming, and I am having trouble with a macro I am trying to adapt. All I want to do is use strBM to filter the import from MSAccess. It adds the correct name from strBM for the added sheet, but the import doesn't bring any results in. I've tried importing manually and that works fine. Its the syntax for "WHERE (qryInflationIndicesBudgetsWithout.FiledUserName=s trBM)" thats at fault I know, but I've tried reading the Help files, my "Learn Excel Programming in 24 Hours" and scoured the net but I am still at a loss. I am sure someone can spot straight away what I have done wrong, can someone correct my lousy syntax?! Sub Macro1() ' ' Macro1 Macro ' Macro recorded 22/09/2003 by Luke Brunning ' ' Dim strBM As String strBM = InputBox("Enter a Budget Manager: ") Sheets.Add.Name = strBM With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=MS Access Database;DBQ=H:\ms_access\current.mdb;DefaultDir=H :\ms_access;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTi" _ ), Array("meout=5;")), Destination:=Range("A1")) .CommandText = Array( _ "SELECT qryInflationIndicesBudgetsWithout.FiledUserName, qryInflationIndicesBudgetsWithout.CostCentre, qryInflationIndicesBudgetsWithout.Description, qryInflationIndicesBudgetsWithout.FLCode, qryInflat" _ , _ "ionIndicesBudgetsWithout.`Inflation Index`, qryInflationIndicesBudgetsWithout.Budget" & Chr(13) & "" & Chr(10) & "FROM `H:\ms_access\current`.qryInflationIndicesBudgetsW ithout qryInflationIndicesBudgetsWithout" & Chr(13) & "" & Chr(10) & "WHERE (qryInflati" _ , _ "onIndicesBudgetsWithout.FiledUserName=strBM)" & Chr(13) & "" & Chr(10) & "ORDER BY qryInflationIndicesBudgetsWithout.FLCode" _ ) .Name = "Query from MS Access Database" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newcomer to VBA; trouble with strings?
Hi,
Your strBM is a variable but you gave it as text. Make this modification: "onIndicesBudgetsWithout.FiledUserName=" & strBM & ")" -- JP http://www.solutionsvba.com "reclusive monkey" wrote in message om... Hello, I am a total newcomer to programming, and I am having trouble with a macro I am trying to adapt. All I want to do is use strBM to filter the import from MSAccess. It adds the correct name from strBM for the added sheet, but the import doesn't bring any results in. I've tried importing manually and that works fine. Its the syntax for "WHERE (qryInflationIndicesBudgetsWithout.FiledUserName=s trBM)" thats at fault I know, but I've tried reading the Help files, my "Learn Excel Programming in 24 Hours" and scoured the net but I am still at a loss. I am sure someone can spot straight away what I have done wrong, can someone correct my lousy syntax?! Sub Macro1() ' ' Macro1 Macro ' Macro recorded 22/09/2003 by Luke Brunning ' ' Dim strBM As String strBM = InputBox("Enter a Budget Manager: ") Sheets.Add.Name = strBM With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=MS Access Database;DBQ=H:\ms_access\current.mdb;DefaultDir=H :\ms_access;DriverId=25;FI L=MS Access;MaxBufferSize=2048;PageTi" _ ), Array("meout=5;")), Destination:=Range("A1")) .CommandText = Array( _ "SELECT qryInflationIndicesBudgetsWithout.FiledUserName, qryInflationIndicesBudgetsWithout.CostCentre, qryInflationIndicesBudgetsWithout.Description, qryInflationIndicesBudgetsWithout.FLCode, qryInflat" _ , _ "ionIndicesBudgetsWithout.`Inflation Index`, qryInflationIndicesBudgetsWithout.Budget" & Chr(13) & "" & Chr(10) & "FROM `H:\ms_access\current`.qryInflationIndicesBudgetsW ithout qryInflationIndicesBudgetsWithout" & Chr(13) & "" & Chr(10) & "WHERE (qryInflati" _ , _ "onIndicesBudgetsWithout.FiledUserName=strBM)" & Chr(13) & "" & Chr(10) & "ORDER BY qryInflationIndicesBudgetsWithout.FLCode" _ ) .Name = "Query from MS Access Database" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newcomer to VBA; trouble with strings?
Thanks for the reply. I tried what you suggested and got the error
message:- Run-time error '1004': SQL Syntax Error Strangely enough though the VB editor highlights the line .Refresh BackgroundQuery:=False rather than the one with my problematic variable in. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace numeric strings in larger text strings | Excel Worksheet Functions | |||
Calculating birthday - yet another newcomer!! | Excel Worksheet Functions | |||
How to find number of pairs of strings from list of strings? | Excel Worksheet Functions | |||
last name, first name strings | New Users to Excel | |||
How can I count strings within strings | Excel Worksheet Functions |