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
_________________
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
_________________