J
jhillman
I want to get this code to pull off the data from access using just the
performance ID number and not the name from the Master Name itself. I
want to specify a number say 2 in excel and then I want it to return
the proper returns using the ID number I give it. Right now it goes
into the master name table then looks at the ID number that equals the
name and then goes to the Performance Table and then finds the ID
number and returns the proper performance. It can only pull off the
data if the name in Excel is exactly written correctly as its name says
in the mastername table. The Part I think is wrong is the SELECT part
and is what needs to be changed.
Sub ImportingReturns()
Application.ScreenUpdating = False
For Each Cell In Selection
With Sheets("Pertrac").Range("B2").QueryTable
.Connection = Array(Array( _
"ODBC;DBQ=M:\Analytics\Traditional\Proposal Development
Department(03132003).mdb;DefaultDir=M:\;Driver={Driver do Microsoft
Access (*.mdb)};DriverId=25;Exclusive=" _
), Array( _
"0;FIL=MS
Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"
_
))
.CommandText = Array( _
"SELECT Performance.Date,Performance.Return" & Chr(13) & "" &
Chr(10) & "From Mastername Mastername, Performance Performance" &
Chr(13) & "" & Chr(10) & "WHERE Performance.ID = Mastername.ID AND", _
" ((Mastername.Mastername='" & Cell.Value & "') AND
(Performance.Date>={ts '2001-01-31 00:00:00'}))" _
)
.Refresh BackgroundQuery:=False
End With
Sheets("Pertrac").Select
Columns("B:C").Sort Key1:=Range("B1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Managers").Select
Cell.Range("A3:A23").FormulaR1C1 =
"=if(isna(VLOOKUP(RC1,Pertrac!R2C1:R100C5,5,FALSE)),""N/A"",VLOOKUP(RC1,Pertrac!R2C1:R100C5,5,FALSE))"
Cell.Range("A3:A23").Copy
Cell.Range("A3:A23").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Next Cell
Application.ScreenUpdating = True
End Sub
performance ID number and not the name from the Master Name itself. I
want to specify a number say 2 in excel and then I want it to return
the proper returns using the ID number I give it. Right now it goes
into the master name table then looks at the ID number that equals the
name and then goes to the Performance Table and then finds the ID
number and returns the proper performance. It can only pull off the
data if the name in Excel is exactly written correctly as its name says
in the mastername table. The Part I think is wrong is the SELECT part
and is what needs to be changed.
Sub ImportingReturns()
Application.ScreenUpdating = False
For Each Cell In Selection
With Sheets("Pertrac").Range("B2").QueryTable
.Connection = Array(Array( _
"ODBC;DBQ=M:\Analytics\Traditional\Proposal Development
Department(03132003).mdb;DefaultDir=M:\;Driver={Driver do Microsoft
Access (*.mdb)};DriverId=25;Exclusive=" _
), Array( _
"0;FIL=MS
Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"
_
))
.CommandText = Array( _
"SELECT Performance.Date,Performance.Return" & Chr(13) & "" &
Chr(10) & "From Mastername Mastername, Performance Performance" &
Chr(13) & "" & Chr(10) & "WHERE Performance.ID = Mastername.ID AND", _
" ((Mastername.Mastername='" & Cell.Value & "') AND
(Performance.Date>={ts '2001-01-31 00:00:00'}))" _
)
.Refresh BackgroundQuery:=False
End With
Sheets("Pertrac").Select
Columns("B:C").Sort Key1:=Range("B1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Managers").Select
Cell.Range("A3:A23").FormulaR1C1 =
"=if(isna(VLOOKUP(RC1,Pertrac!R2C1:R100C5,5,FALSE)),""N/A"",VLOOKUP(RC1,Pertrac!R2C1:R100C5,5,FALSE))"
Cell.Range("A3:A23").Copy
Cell.Range("A3:A23").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Next Cell
Application.ScreenUpdating = True
End Sub