PureBluff
Board Regular
- Joined
- Apr 4, 2014
- Messages
- 174
- Office Version
- 2016
- Platform
- Windows
- Mobile
Bit of a strange one here, when I run this macro, the loop at the end doesn't take place or when it does, b46,b47,b48 are correct - but then the next formula in b49 is corrupted
If it's corrupted B49, the formula in there should be:
however it's always
Can anyone shed some light why this corruption may be happening?
Thanks
Code:
Sub Macro2()
order1 = Sheets("Customer Order").Range("c9").Value
order2 = Sheets("Customer Order").Range("c10").Value
If Sheets("customer order").Range("f7").Value = "" Then
MsgBox ("No date specified in cell F7, please fix!")
Exit Sub
End If
'#order 1 blank
If order1 = "" Then
MsgBox ("Order not speficied!")
Exit Sub
End If
'#end order 1 blank
'#2 order syntax
'# -- order lines --
If order1 <> "" And order2 <> "" Then
With ActiveWorkbook.Connections("Query from FOPS").ODBCConnection
.BackgroundQuery = True
.CommandText = Array( _
"SELECT *" & Chr(13) & "" & Chr(10) & "FROM FOPS6.ORDER_LINES ORDER_LINES" & Chr(13) & "" & Chr(10) & "WHERE (ORDER_LINES.Order_number=" & order1 & ") AND (ORDER_LINES.Boxes_despatched>0) OR (ORDER_LINES.Order_number=" & order2 & ") AND (ORDER_LINES.Boxes_despatched>0)" & Chr(13) & "" & Chr(10) & "ORDER BY ORDER_LINES.Product_Code asc")
.CommandType = xlCmdSql
.Connection = Array(Array( _
"ODBC;DSN=FOPS;ServerName=UKHUNSPVP21.1583;ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP;DBQ=FOPS6;OpenMode=0;ClientVersion=14.1" _
), Array( _
"1.011.000;CodePageConvert=1252;PvClientEncoding=CP1252;PvServerEncoding=CP1252;" _
))
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("Query from FOPS")
.Name = "Query from FOPS"
.Description = ""
End With
ActiveWorkbook.Connections("Query from FOPS").Refresh
' -- end order lines
' -- order header
With ActiveWorkbook.Connections("Query from FOPS1").ODBCConnection
.BackgroundQuery = True
.CommandText = Array( _
"SELECT *" & Chr(13) & "" & Chr(10) & "FROM FOPS6.ORDER_HEADER ORDER_HEADER" & Chr(13) & "" & Chr(10) & "WHERE (ORDER_HEADER.Order_number=" & order1 & ") OR (ORDER_HEADER.Order_number=" & order2 & ")")
.CommandType = xlCmdSql
.Connection = Array(Array( _
"ODBC;DSN=FOPS;ServerName=UKHUNSPVP21.1583;ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP;DBQ=FOPS6;OpenMode=0;ClientVersion=14.1" _
), Array( _
"1.011.000;CodePageConvert=1252;PvClientEncoding=CP1252;PvServerEncoding=CP1252;" _
))
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("Query from FOPS1")
.Name = "Query from FOPS1"
.Description = ""
End With
ActiveWorkbook.Connections("Query from FOPS1").Refresh
' -- end order header
End If
'#end of 2 order syntax
'#single order syntax
' -- order lines --
If order1 <> "" And order2 = "" Then
With ActiveWorkbook.Connections("Query from FOPS").ODBCConnection
.BackgroundQuery = True
.CommandText = Array( _
"SELECT *" & Chr(13) & "" & Chr(10) & "FROM FOPS6.ORDER_LINES ORDER_LINES" & Chr(13) & "" & Chr(10) & "WHERE (ORDER_LINES.Order_number=" & order1 & ") AND (ORDER_LINES.Boxes_despatched>0)" & Chr(13) & "" & Chr(10) & "ORDER BY ORDER_LINES.Product_Code asc")
.CommandType = xlCmdSql
.Connection = Array(Array( _
"ODBC;DSN=FOPS;ServerName=UKHUNSPVP21.1583;ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP;DBQ=FOPS6;OpenMode=0;ClientVersion=14.1" _
), Array( _
"1.011.000;CodePageConvert=1252;PvClientEncoding=CP1252;PvServerEncoding=CP1252;" _
))
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("Query from FOPS")
.Name = "Query from FOPS"
.Description = ""
End With
ActiveWorkbook.Connections("Query from FOPS").Refresh
' -- end order lines --
' -- order header
With ActiveWorkbook.Connections("Query from FOPS1").ODBCConnection
.BackgroundQuery = True
.CommandText = Array( _
"SELECT *" & Chr(13) & "" & Chr(10) & "FROM FOPS6.ORDER_HEADER ORDER_HEADER" & Chr(13) & "" & Chr(10) & "WHERE (ORDER_HEADER.Order_number=" & order1 & ")")
.CommandType = xlCmdSql
.Connection = Array(Array( _
"ODBC;DSN=FOPS;ServerName=UKHUNSPVP21.1583;ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP;DBQ=FOPS6;OpenMode=0;ClientVersion=14.1" _
), Array( _
"1.011.000;CodePageConvert=1252;PvClientEncoding=CP1252;PvServerEncoding=CP1252;" _
))
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("Query from FOPS1")
.Name = "Query from FOPS1"
.Description = ""
End With
ActiveWorkbook.Connections("Query from FOPS1").Refresh
' -- end order header
End If
'#end of single order syntax
Sheets("customer order").Activate
Range("b46:b144").ClearContents
Dim i, j As Long
j = 2
For i = 46 To 144
ActiveSheet.Range("b" & i).Formula = _
"=IF(ISERROR(TRIM('Order Lines'!C" & j & ")),"""",(TRIM('Order Lines'!C" & j & ")))"
j = j + 1
Next I
''
'Call SaveFile
end Sub
If it's corrupted B49, the formula in there should be:
Code:
=IF(ISERROR(TRIM('Order Lines'!C5)),"",(TRIM('Order Lines'!C5)))
however it's always
Code:
=IF(ISERROR(TRIM('Order Lines'!C49)),"",(TRIM('Order Lines'!C49)))
Can anyone shed some light why this corruption may be happening?
Thanks