PDA

View Full Version : export data from ERP database



helai
09-13-2005, 12:20 AM
I want to export data from the database of ERP,but I am not familiary with it,so I record the macros when I try to export data from it,and I want the filter can be changed ,in this module,the ((left(ITR_Reason,4)='115') 115 can be replaced by 116,or anythingelse,the sentences listed below

you are appreciated if you can help me to rewrite these sentences to standard vba code,and when I run it,the parameters of filter also can be exchanged as I like
by the way,if I don't apply filter,then the excel can't store so larger data,and that is the reason when I don't simply export data from erp to excel separatly,and using vba to analyse it
Thanks in advance


With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=S-IERP65;Description=Intuitive ERP Datasource;UID=123;APP=Microsoft Office XP;WSID=HP-9;DATABASE=IERP65" _
), Array(";Network=DBMSSOCN;UseProcForPrepare=0;QuotedId=No")), Destination:= _
Range("D1"))
.CommandText = Array( _
"SELECT ITR.ITR_ItemID, IMA.IMA_ItemName , IMA.IMA_ItemName, IMA.IMA_LeadTimeCode, IMA.IMA_MakeOnAssyFlag, ITR.ITR_TransQty, ITR.ITR_TransType , ITR.ITR_Reason FROM IERP65.dbo.IMA IMA, IERP65.dbo.IMC IMC, IERP65.dbo.ITR ITR WHERE I" _
, _
" MC.IMC_ItemID = IMA.IMA_ItemID AND ITR.ITR_ItemID = IMC.IMC_ItemID AND ((left(ITR_Reason,4)='115') AND ( ITR.ITR_TransType='Issue'))" _
)
.Name = "查询来自 S-IERP65"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Documents and Settings\Z\Application Data\Microsoft\Queries\查询来自 S-IERP65.dqy"
.refresh BackgroundQuery:=False
End With
End Sub
_________________

Ebrow
05-24-2007, 02:40 PM
Hi. I am not fully sure what you were looking for. But if you want to add flexible variables to your sql you may want to try adding the code you created into a function. Adding the '"& myVariable &"' between each variable where myVariable is your variable name then pass that variable through the function.

Hope that made sense. :doh:Anywayz here is the code.


Sub example()
Call myFilter(115, "Issue")
End Sub

Function myFilter(myITR_Reason As Long, myITR_TransType As String)
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=S-IERP65;Description=Intuitive ERP Datasource;UID=123;APP=Microsoft Office XP;WSID=HP-9;DATABASE=IERP65" _
), Array(";Network=DBMSSOCN;UseProcForPrepare=0;QuotedId=No")), Destination:= _
Range("D1"))
.CommandText = Array( _
"SELECT ITR.ITR_ItemID, IMA.IMA_ItemName , IMA.IMA_ItemName, IMA.IMA_LeadTimeCode, IMA.IMA_MakeOnAssyFlag, ITR.ITR_TransQty, ITR.ITR_TransType , ITR.ITR_Reason FROM IERP65.dbo.IMA IMA, IERP65.dbo.IMC IMC, IERP65.dbo.ITR ITR WHERE I" _
, _
" MC.IMC_ItemID = IMA.IMA_ItemID AND ITR.ITR_ItemID = IMC.IMC_ItemID AND ((left(ITR_Reason,4)='" & myITR_Reason & "') AND ( ITR.ITR_TransType='" & myITR_TransType & "'))")

.Name = "???? S-IERP65"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Documents and Settings\Z\Application Data\Microsoft\Queries\???? S-IERP65.dqy"
.Refresh BackgroundQuery:=False
End With

End Function