Hello All,
I am facing issues to run Query on SAP through excel files . As accordding to some colleagues, they belivery the main issue is my PC is 64 bits.
Do you know if it is a really problem ?
Here you go the code that I am using, if is a matter. Is there any other way to run ?
*********************************************************************************************
Dim R3 As Object
Sub TESTE()
Dim x As String
x = PR_report2("NOME DA QUERY ", "NOME DO GRUPO", "VARIANTE", True, "ABA DO EXCEL")
x = PR_report2("XXX", "XX", "XXX", True, "DTbase")
End Sub
Function PR_report2(Q As String, U As String, d As String, man As Boolean, vTabela As String) As String
'Dim sap As New SAPFunctions
Dim QUERY As Object, SELECTION_TABLE As Object, result
Dim LDATA As Object, LISTDESC As Object, FPAIRS As Object, Row As Object
Dim iRow As Long, iStartStringRow As Long, DataIndex As Long, NumberofRecords As Long, RecordLength As Long
Dim NumberofFields As Integer, FieldLength As Integer, iRec As Long, iField As Integer
Dim DATALINE As String, ControlPosition As String
Dim SAPRecords() As Record
Dim a As String, x As Integer, van As Boolean
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim R3 As Object
'Set db = CurrentDb
Set R3 = CreateObject("SAP.Functions")
Set R3 = CreateObject("SAP.Functions")
R3.Connection.Client = "200"
R3.Connection.User = "XXX"
R3.Connection.Language = "EN"
R3.Connection.Password = "XXX"
R3.Connection.SystemNumber = "00"
R3.Connection.System = "PRO"
R3.Connection.GroupName = "BRIDGE"
R3.Connection.MessageServer = "SIte"
If R3.Connection.Logon(0, True) <> True Then
If R3.Connection.Logon(0, False) <> True Then
Exit Function
End If
End If
TBL = vTabela
Set QUERY = R3.Add("RSAQ_REMOTE_QUERY_CALL")
Set SELECTION_TABLE = QUERY.tables("SELECTION_TABLE")
QUERY.exports("WORKSPACE") = ""
QUERY.exports("QUERY") = Q
QUERY.exports("USERGROUP") = U
QUERY.exports("VARIANT") = d
QUERY.exports("SKIP_SELSCREEN") = "X"
QUERY.exports("DATA_TO_MEMORY") = "X"
QUERY.exports("EXTERNAL_PRESENTATION") = ""
result = QUERY.Call
If result = True Then
If man = False Then
R3.Connection.Logoff
End If
Set LDATA = QUERY.tables("LDATA")
Set LISTDESC = QUERY.tables("LISTDESC")
Set FPAIRS = QUERY.tables("FPAIRS")
R3.Connection.Logoff
For iRow = iStartStringRow + 1 To (LDATA.RowCount)
DATALINE = DATALINE & LDATA(iRow, 1)
Next iRow
DataIndex = 1
NumberofRecords = 0
RecordLength = 0
Do Until DataIndex + 1 >= Len(DATALINE)
NumberofRecords = NumberofRecords + 1
NumberofFields = 0
FieldLength = 0
ControlPosition = ""
Do Until ControlPosition = ";"
FieldLength = CInt(Mid(DATALINE, DataIndex, 3)) 'conversion of the string to an integer
NumberofFields = NumberofFields + 1
ReDim Preserve SAPRecords(NumberofRecords)
ReDim Preserve SAPRecords(NumberofRecords).RecordField(NumberofFields)
SAPRecords(NumberofRecords).RecordField(NumberofFields).FieldLgth = FieldLength
SAPRecords(NumberofRecords).RecordField(NumberofFields).FieldContents = Mid(DATALINE, DataIndex + 4, FieldLength)
ControlPosition = Mid(DATALINE, DataIndex + 4 + FieldLength, 1)
DataIndex = DataIndex + FieldLength + 5
Loop
Loop
Sheets(vTabela).Select
'Application.Cells.Select
Range("A2:AO2").Select 'SELECIONA O QUE QUER
Range(Selection, Selection.End(xlDown)).Select 'VAI PARA A ULTIMA LINHA DE DADOS SELECIONADO
Selection.Clear
For iRec = 2 To NumberofRecords
For iField = 1 To LISTDESC.RowCount
Cells(iRec, iField).Value = SAPRecords(iRec).RecordField(iField).FieldContents ' mezoertekek kiirasa
Next iField
'rs.Update
Next iRec
'ConexaoSQLServer cn
'rs.Open "SELECT * FROM " & TBL & "", cn, adOpenDynamic, adLockOptimistic
' For iRec = 1 To NumberofRecords
' rs.AddNew
' For iField = 1 To LISTDESC.RowCount
' rs.Fields(iField - 1).Value = SAPRecords(iRec).RecordField(iField).FieldContents ' mezoertekek kiirasa
' Next iField
' rs.Update
' Next iRec
'rs.Close
'cn.Close
PR_report1 = "OK"
Else
If man = False Then
R3.Connection.Logoff
End If
PR_report2 = QUERY.exception
End If
End Function