PDA

View Full Version : query results to a specific cell



mercmannick
07-09-2006, 10:10 AM
Sub update_data()
Dim Cn As ADODB.Connection, Rs As ADODB.Recordset, r As Long
'additional declarations to suit new syntax
Dim MyConn
Dim sSQL As String
Dim i As Integer

sSQL = "SELECT MRP_CODE.CELL, Count(IMFINTERNAL.[Short Mat]) AS [CountOfShort Mat] " _
& "FROM IMFINTERNAL INNER JOIN MRP_CODE ON IMFINTERNAL.[Short MRP] = MRP_CODE.MRP_CODE " _
& "WHERE (((IMFINTERNAL.[Days Late]) > 0)) " _
& "GROUP BY MRP_CODE.CELL;"
' connect to the Access database
' and axx of IMFINTERNAL_BY_CELL qry

Set Cn = New ADODB.Connection
MyConn = "C:\db1.mdb"

With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
.Execute sSQL
End With

Sheets("Data").Activate
spe = Cells(Rows.Count, 3).End(xlUp).Row ' the next row in the worksheet for speed cell
E = Cells(Rows.Count, 7).End(xlUp).Row ' the next row in the worksheet for cell e
F = Cells(Rows.Count, 11).End(xlUp).Row ' the next row in the worksheet for cell f
G = Cells(Rows.Count, 15).End(xlUp).Row ' the next row in the worksheet for cell g
W = Cells(Rows.Count, 19).End(xlUp).Row ' the next row in the worksheet for cell w
S15 = Cells(Rows.Count, 23).End(xlUp).Row ' the next row in the worksheet for s15
S70 = Cells(Rows.Count, 27).End(xlUp).Row ' the next row in the worksheet for s70
S17 = Cells(Rows.Count, 31).End(xlUp).Row ' the next row in the worksheet for s17


End Sub


'THIS IS WHERE I NEED HELP THE QUERY ABOVE OUTPUTS LIKE THIS
'Cell F 98
'Cell G 6
'S70 28
'Speed 20
'IS THERE A WAY OF FROM QUERY ABOVE TO PUT THE VALUES ie
'CELL F SHOULD GOTO F = Cells(Rows.Count, 11).End(xlUp).Row
'Cell G SHOULD GOTO G = Cells(Rows.Count, 15).End(xlUp).Row
'ETC...........BUT IF THERE IS NO OUTPUT ON A SPECIFIC CELL PUT A 0 IN SO NEXT TIME RUN all the rows match in line
'THEY WILL ALL BE ON SAME ROWS


also posted on mr excel http://www.mrexcel.com/board2/viewtopic.php?t=221276 (http://http//www.mrexcel.com/board2/viewtopic.php?t=221276)
thanks

Merc

mdmackillop
07-09-2006, 11:24 AM
I don't know how your data is presented. Something like this could be used to analyse results and set values. Also, how is your Null result returned? With the initial string?


Select Case Split(data, " ")(0)
Case "Cell"
Select Case Split(data, " ")(1)
Case "E"
E.Value = Split(data, " ")(2)
Case "F"
F.Value = Split(data, " ")(2)
Case "G"
G.Value = Split(data, " ")(2)
Case "W"
W.Value = Split(data, " ")(2)
End Select
Case "S15"
S15.Value = Split(data, " ")(1)
Case "S17"
S17.Value = Split(data, " ")(1)
Case "S70"
S70.Value = Split(data, " ")(1)
Case "Speed"
spe.Value = Split(data, " ")(1)
End Select

mercmannick
07-09-2006, 11:37 AM
if there is no value on null strings it dosent give any output on query, so if it isnt in output of query then put a 0 in col

Thanks
enclosed is sample with the query results
Merc

mdmackillop
07-09-2006, 12:11 PM
A bit simpler with the layout.

see below

mercmannick
07-09-2006, 12:21 PM
mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87)

that is cool puts values in the right cells, how do i incorporate this with my query

Many Thanks

Merc

mdmackillop
07-09-2006, 12:56 PM
Hi Merc,
I'm having problems with the database connection on this line

Dim Cn As ADODB.Connection
What references are you using.
Regards
MD

mercmannick
07-09-2006, 01:12 PM
mdmackillop

M$ active x data objects


Dim Cn As ADODB.Connection, Rs As ADODB.Recordset, r As Long
'additional declarations to suit new syntax
Dim MyConn
Dim sSQL As String
Dim i As Integer

sSQL = "SELECT MRP_CODE.CELL, Count(IMFINTERNAL.[Short Mat]) AS [Xlodbc.xlaCountOfShort Mat] " _
& "FROM IMFINTERNAL INNER JOIN MRP_CODE ON IMFINTERNAL.[Short MRP] = MRP_CODE.MRP_CODE " _
& "WHERE (((IMFINTERNAL.[Days Late]) > 0)) " _
& "GROUP BY MRP_CODE.CELL;"
' connect to the Access database
' and axx of IMFINTERNAL_BY_CELL qry

Set Cn = New ADODB.Connection
MyConn = "C:\db1.mdb"


With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
.Execute sSQL
End With

this works cool , just trying to capture the values from query and pass onto your code now

Thanks

Merc

mdmackillop
07-09-2006, 02:03 PM
I've not done much on Excel/Access interaction, so a bit of a learning experience. Give this a try


Option Explicit
Sub Update_Data()
Dim Cn As ADODB.Connection, Rs As ADODB.Recordset, r As Long, c As Long
Dim MyField
'additional declarations to suit new syntax
Dim MyConn
Dim sSQL As String
Dim i As Integer
Application.ScreenUpdating = False
sSQL = "SELECT MRP_CODE.CELL, Count(IMFINTERNAL.[Short Mat]) AS [Xlodbc.xlaCountOfShort Mat] " _
& "FROM IMFINTERNAL INNER JOIN MRP_CODE ON IMFINTERNAL.[Short MRP] = MRP_CODE.MRP_CODE " _
& "WHERE (((IMFINTERNAL.[Days Late]) > 0)) " _
& "GROUP BY MRP_CODE.CELL;"
' connect to the Access database
' and axx of IMFINTERNAL_BY_CELL qry


Set Cn = New ADODB.Connection
MyConn = "C:\db1.mdb"

With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
Set Rs = .Execute(sSQL)
End With

'Write to results area
r = 20
c = 3
Do Until Rs.EOF
For Each MyField In Rs.Fields
Cells(r, c) = MyField
c = c + 1
Next MyField
Rs.MoveNext
r = r + 1
c = 3
Loop

'Get results and write to table
SetCells
Application.ScreenUpdating = True
End Sub


Sub SetCells()
Dim DataRow As Long, Cel As Range, Cols, c
'Get next row to fill
DataRow = [C3].End(xlDown).Row + 1
'Fill data
For Each Cel In Range([C20], [C20].End(xlDown))
Select Case Cel
Case "Speed"
Cells(DataRow, "C") = Cel.Offset(, 1)
Case "Cell E"
Cells(DataRow, "G") = Cel.Offset(, 1)
Case "Cell F"
Cells(DataRow, "K") = Cel.Offset(, 1)
Case "Cell G"
Cells(DataRow, "O") = Cel.Offset(, 1)
Case "Cell W"
Cells(DataRow, "S") = Cel.Offset(, 1)
Case "S15"
Cells(DataRow, "W") = Cel.Offset(, 1)
Case "S70"
Cells(DataRow, "AA") = Cel.Offset(, 1)
Case "S17"
Cells(DataRow, "AE") = Cel.Offset(, 1)
End Select
Next
'Check and fill blanks
Cols = Array("C", "G", "K", "O", "S", "W", "AA", "AE")
For Each c In Cols
If Cells(DataRow, c) = "" Then Cells(DataRow, c) = 0
Next

End Sub

mercmannick
07-09-2006, 02:20 PM
mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87)

Superb thank you so much, christ if you only just learnt that then i need some serious tuiton lol

Many Many thanks

Merc


:bow::bow::bow::bow::bow::bow: