|
|
Is there a way to use tables from two different data sources. Specifically
I want to query to contain a table of info from a separate Excel file (named
range) and join it to a table that is on a SQL Server (existing ODBC setup).
The reason is I want to have a query where a user can put a list of skus
into a column, then have that represent the criteria for filtering another
query. Cant seem to do that directly with MSQuery (limited to number of
criteria or to a single cell). Wanted to join the sku list in Excel file
and let the join do the filter.
Any ideas would be greatly appreciated.
c-
|
|
eOn Fri, 16 Mar 2007 12:29:41 -0500, "Chris Rettke"
<c_rettke[ at ]_remove_hotmail.com> wrote:
[Quoted Text] >Is there a way to use tables from two different data sources. Specifically
>I want to query to contain a table of info from a separate Excel file (named
>range) and join it to a table that is on a SQL Server (existing ODBC setup).
>
>The reason is I want to have a query where a user can put a list of skus
>into a column, then have that represent the criteria for filtering another
>query. Cant seem to do that directly with MSQuery (limited to number of
>criteria or to a single cell). Wanted to join the sku list in Excel file
>and let the join do the filter.
>
You could link both tables into an Access database, then run MSQuery off of
that. I don't know if that's the best way, but it seems possible.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0324_01C76F26.98B07510
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello
sorry for my regular english but I am begin on this.
About your question on query=B4s, you can use a list into column an add =
criteria=B4s while use it in a Do ... Loop.
For example:
Sub buscar()
Application.ScreenUpdating =3D False
Sheets(1).Select
i =3D 1
Do Until i =3D 6
Sheets(1).Select
Cells(i, 1).Select
valor =3D ActiveCell.Value
Call conexion(valor) =20
i =3D i + 1
Loop
Application.ScreenUpdating =3D True
End Sub
This is for cross your list of criteria=B4s in the range called.
Range("A1") to Range("A6")
So, we now obtained the list of criteria=B4s, just rest call some rutine =
for do it the Query:
You do need activate into references of VBA Text edit the librery:
Main / Tools / References the object:
Microsoft Active DataX Objects Library
Sub conexion(valor As Variant)
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim paso, filas
Set cnn =3D New Connection
Set rs =3D New ADODB.Recordset
cadena =3D ThisWorkbook.Path & "\" & ThisWorkbook.Name
cnn.Open "Driver=3D{Microsoft Excel Driver (*.xls)};" & "DBQ=3D" & =
cadena
rs.Open "select * from [Hoja1$A10:A100]", cnn, , , adCmdText =
'You do need change this range for yours owners =
ranges
rs.MoveFirst
Application.ScreenUpdating =3D False
Sheets("Hoja2").Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Do While Not rs.EOF
If rs(0) Like valor Then
ActiveCell.Value =3D rs(0).Value
ActiveCell.Offset(1, 0).Select
End If
rs.MoveNext
Loop
rs.Close
cnn.Close
Application.ScreenUpdating =3D True
Set rs =3D Nothing
Set cnn =3D Nothing
End Sub
This is a recursive query for cross all the range named, for more querys =
you need cross references with another query inside this code.
The numbers of critetrias can be modified.
More query=B4s can be involved wtih this.
And the range of the Do ... Loop can be modified too for another ranges.
To join more criterias with more querys you need modify the query or =
querys for doit that you tell us.
regards;
Francisco Parrilla
Monterrey NL Mexico
"Chris Rettke" <c_rettke[ at ]_remove_hotmail.com> escribi=F3 en el mensaje =
news:#LRu4C$ZHHA.4396[ at ]TK2MSFTNGP06.phx.gbl...
Is there a way to use tables from two different data sources. =
Specifically
I want to query to contain a table of info from a separate Excel file =
(named
range) and join it to a table that is on a SQL Server (existing ODBC =
setup).
The reason is I want to have a query where a user can put a list of =
skus
into a column, then have that represent the criteria for filtering =
another
query. Cant seem to do that directly with MSQuery (limited to number =
of
criteria or to a single cell). Wanted to join the sku list in Excel =
file
and let the join do the filter.
Any ideas would be greatly appreciated.
c-
------=_NextPart_000_0324_01C76F26.98B07510
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hello</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>sorry for my regular english but I =
am begin on this.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>About your question on query=B4s, you =
can use a list=20
into column an add criteria=B4s while use it in a Do =
.... Loop.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>For example:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial color=3D#0000ff><STRONG>Sub=20
buscar()<BR>Application.ScreenUpdating =3D =
False<BR>Sheets(1).Select<BR>i =3D=20
1<BR>Do Until i =3D 6<BR>Sheets(1).Select<BR>Cells(i, 1).Select<BR>valor =
=3D=20
ActiveCell.Value<BR>Call=20
conexion(valor) &nbs=
p; =
; =20
<BR>i =3D i + 1<BR>Loop<BR>Application.ScreenUpdating =3D True<BR>End=20
Sub</STRONG></FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>This is for cross your list of =
criteria=B4s in the=20
range called.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Range("A1") to =
Range("A6")</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>So, we now obtained the list of =
criteria=B4s, just=20
rest call some rutine for do it the Query:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>You do need activate into =
references of VBA=20
Text edit the librery:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Main / Tools / References the=20
object:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Microsoft Active DataX Objects =
Library</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial color=3D#0000ff><STRONG>Sub conexion(valor As=20
Variant)<BR>Dim cnn As ADODB.Connection<BR>Dim rs As =
ADODB.Recordset<BR>Dim=20
paso, filas<BR>Set cnn =3D New Connection<BR>Set rs =3D New=20
ADODB.Recordset<BR>cadena =3D ThisWorkbook.Path & "\" &=20
ThisWorkbook.Name<BR>cnn.Open "Driver=3D{Microsoft Excel Driver =
(*.xls)};" &=20
"DBQ=3D" & cadena<BR>rs.Open "select * from [<FONT=20
color=3D#ff0000>Hoja1$A10:A100</FONT>]", cnn, , ,=20
adCmdText &nbs=
p; =
; =
=20
'You do need change this range for yours owners=20
ranges<BR>rs.MoveFirst<BR>Application.ScreenUpdating =3D=20
False<BR> Sheets("Hoja2").Select<BR> =
Selection.End(xlDown).Select<BR> =20
Selection.End(xlUp).Select<BR> ActiveCell.Offset(1,=20
0).Select</STRONG></FONT></DIV>
<DIV><FONT face=3DArial =
color=3D#0000ff><STRONG></STRONG></FONT> </DIV>
<DIV><FONT face=3DArial color=3D#0000ff><STRONG>Do While Not =
rs.EOF<BR>If rs(0) Like=20
valor Then<BR>ActiveCell.Value =3D rs(0).Value<BR>ActiveCell.Offset(1,=20
0).Select<BR>End=20
If<BR>rs.MoveNext<BR>Loop<BR>rs.Close<BR>cnn.Close<BR>Application.ScreenU=
pdating=20
=3D True<BR>Set rs =3D Nothing<BR>Set cnn =3D Nothing<BR>End =
Sub</STRONG></FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>This is a recursive query for cross all =
the range=20
named, for more querys you need cross references with another query =
inside this=20
code.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>The numbers of critetrias can be=20
modified.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>More query=B4s can be involved =
wtih=20
this.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>And the range of the Do ... Loop can be =
modified=20
too for another ranges.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>To join more criterias with more querys =
you need=20
modify the query or querys for doit that you tell us.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>regards;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Francisco Parrilla</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Monterrey NL Mexico</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<BLOCKQUOTE=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Chris Rettke" <<A=20
=
href=3D"mailto:c_rettke[ at ]_remove_hotmail.com">c_rettke[ at ]_remove_hotmail.com=
</A>>=20
escribi=F3 en el mensaje <A=20
=
href=3D"news:#LRu4C$ZHHA.4396[ at ]TK2MSFTNGP06.phx.gbl">news:#LRu4C$ZHHA.4396=
[ at ]TK2MSFTNGP06.phx.gbl</A>...</DIV>Is=20
there a way to use tables from two different data sources. =20
Specifically<BR>I want to query to contain a table of info from a =
separate=20
Excel file (named<BR>range) and join it to a table that is on a SQL =
Server=20
(existing ODBC setup).<BR><BR>The reason is I want to have a query =
where a=20
user can put a list of skus<BR>into a column, then have that represent =
the=20
criteria for filtering another<BR>query. Cant seem to do that =
directly=20
with MSQuery (limited to number of<BR>criteria or to a single =
cell). =20
Wanted to join the sku list in Excel file<BR>and let the join do the=20
filter.<BR><BR>Any ideas would be greatly=20
appreciated.<BR><BR>c-<BR><BR></BLOCKQUOTE></BODY></HTML>
------=_NextPart_000_0324_01C76F26.98B07510--
|
|
|