fork(7) download
  1. 'This module contains the macros for the main control sheet
  2.  
  3. Option Base 1
  4. Option Explicit
  5.  
  6. Dim CurWrkBook As String
  7. Dim CurrentSheet As String
  8. Dim CurrentHdrSheet As String
  9. Dim Title As String
  10. Dim Message As String
  11. Dim Successful As Boolean
  12. Dim LogFileName As String
  13. Dim Skipsheets As Integer
  14. Dim SkipDlgs As Integer
  15. Dim FirstJLine As Integer
  16. Public Lang As String
  17.  
  18.  
  19. Public Sub Init()
  20. Application.ScreenUpdating = False
  21. Lang = Options("Language")
  22. CurWrkBook = ActiveWorkbook.Name
  23. GotoControlPage
  24. End Sub
  25.  
  26.  
  27.  
  28. Public Function Options(EdBox As String, Optional Text As String) As String
  29. 'Returns the option setting
  30. Options = ActiveWorkbook.DialogSheets("Options").EditBoxes(EdBox).Text
  31. End Function
  32.  
  33.  
  34.  
  35. Public Sub Save_Option(EdBox, NewText As String)
  36. 'Sets the options
  37. If ActiveWorkbook.DialogSheets("Options").EditBoxes(EdBox).Text <> NewText Then
  38. ActiveWorkbook.DialogSheets("Options").Unprotect
  39. ActiveWorkbook.DialogSheets("Options").EditBoxes(EdBox).Text = NewText
  40. ActiveWorkbook.DialogSheets("Options").Protect
  41. End If
  42. End Sub
  43.  
  44.  
  45.  
  46. Public Function Encode_XMLchar(tmp As String) As String
  47. tmp = Replace(tmp, "&", "&amp;")
  48. tmp = Replace(tmp, "<", "&lt;")
  49. tmp = Replace(tmp, ">", "&gt;")
  50. tmp = Replace(tmp, "'", "&apos;")
  51. Encode_XMLchar = Replace(tmp, """", "&quot;")
  52. End Function
  53.  
  54.  
  55.  
  56. Public Function Decode_XMLchar(tmp As String) As String
  57. tmp = Replace(tmp, "&amp;", "&")
  58. tmp = Replace(tmp, "&lt;", "<")
  59. tmp = Replace(tmp, "&gt;", ">")
  60. tmp = Replace(tmp, "&apos;", "'")
  61. Decode_XMLchar = Replace(tmp, "&quot;", """")
  62. End Function
  63.  
  64.  
  65.  
  66. Private Sub Set_Homepage_Labels()
  67. With ActiveWorkbook.Sheets(1)
  68. .SetDefaults.Caption = GetMsg(Lang, 41, 1)
  69. .UserNotes.Caption = GetMsg(Lang, 41, 2)
  70. .NewSheet.Caption = GetMsg(Lang, 41, 3)
  71. .EditSheet.Caption = GetMsg(Lang, 41, 4)
  72. .ClearSheet.Caption = GetMsg(Lang, 41, 5)
  73. .CopySheet.Caption = GetMsg(Lang, 41, 6)
  74. .ImportSelected.Caption = GetMsg(Lang, 41, 7)
  75. .WriteSelected.Caption = GetMsg(Lang, 41, 8)
  76. .GroupBoxes("GroupBox1").Text = GetMsg(Lang, 41, 12)
  77. .GroupBoxes("GroupBox2").Text = GetMsg(Lang, 41, 13)
  78. .GroupBoxes("GroupBox3").Text = GetMsg(Lang, 41, 14)
  79. End With
  80. ActiveWorkbook.Sheets(2).GoControlPage.Caption = GetMsg(Lang, 41, 9)
  81. End Sub
  82.  
  83.  
  84.  
  85. 'This macro sets information about the workbook and sheet.
  86. Sub GetSheet()
  87. CurWrkBook = ActiveWorkbook.Name
  88. CurrentSheet = ActiveSheet.Name
  89. CurrentHdrSheet = CurrentSheet & "_H"
  90. Lang = Options("Language")
  91. 'Get the user's preferred Lang
  92. 'Set Settings = UserForms("Form_Options")
  93.  
  94.  
  95. 'On Error Resume Next ' turn off error checking
  96. 'Set wbMyAddin = Workbooks(AddIns("Jrnlmcro").Name)
  97. 'LastError = Err.Number
  98. 'On Error GoTo 0 ' restore error checking
  99.  
  100. 'If LastError = 0 Then
  101. 'Jrnlmcro.xla is already loaded.
  102. ' Run ("GoToControlPage")
  103. 'Else
  104. ' 'Jrnlmcro.xla is not loaded yet. Load it from ActiveWorkbook.Path
  105. ' SavedPath = CurDir() & "\"
  106. ' WorkbookPath = ActiveWorkbook.Path & "\"
  107. ' ChDir (WorkbookPath)
  108. ' ChDrive (WorkbookPath)
  109. ' Run ("JRNLMCRO.XLA!GotoControlPage")
  110. ' ChDrive (SavedPath)
  111. ' ChDir (SavedPath)
  112. 'End If
  113.  
  114.  
  115.  
  116. 'The logfile name is stored in the message catalog
  117. LogFileName = GetMsg(Lang, 9, 2)
  118. If LogFileName = "" Then
  119. LogFileName = "jrnllog.xls"
  120. End If
  121.  
  122. 'Skipsheets = number of non-journal worksheets in workbook. In the sample shipped, _
  123. ' Skipsheets is 4 because 1 Control sheet + 1 notes sheet + 1 journal template sheet _
  124. ' + 1 journal header template sheet = 4. The Options dialogsheet doesn't count _
  125. ' because it's a dialogsheet, not a worksheet. If you add additional sheets to _
  126. ' the workbook, add that number to Skipsheets.
  127. Skipsheets = 4
  128.  
  129. 'SkipDlgs = number of dialogsheets in jrnl workbook. In the sample shipped, _
  130. ' SkipDlgs is 1 because the only dialogsheet is the Options dialogsheet. If you add _
  131. ' additional dialogsheets to the jrnl workbook, add that number to SkipDlgs.
  132. SkipDlgs = 1
  133.  
  134. End Sub
  135.  
  136.  
  137.  
  138.  
  139. Function StringBetween(SourceString As String, BeforeString As String, AfterString As String, Compare As Integer) As String
  140. Dim BeforeStringPos As Integer
  141. Dim AfterStringPos As Integer
  142. Dim StringBetweenPos As Integer
  143.  
  144. StringBetween = ""
  145.  
  146. BeforeStringPos = InStr(1, SourceString, BeforeString, Compare)
  147. If BeforeStringPos > 0 Then
  148. StringBetweenPos = BeforeStringPos + Len(BeforeString)
  149. AfterStringPos = InStr(StringBetweenPos, SourceString, AfterString, Compare)
  150. If AfterStringPos > StringBetweenPos Then
  151. StringBetween = Mid(SourceString, StringBetweenPos, AfterStringPos - StringBetweenPos)
  152. End If
  153. End If
  154. End Function
  155.  
  156.  
  157.  
  158. 'This macro will add a new journal entry sheet and header
  159. Sub JrnlSheet_New()
  160. Dim NewSheet, Title, Mesg As String
  161. Dim WK As Variant
  162. Dim iMsg As Integer
  163.  
  164. GetSheet
  165. Workbooks(CurWrkBook).Unprotect
  166.  
  167. Title = GetMsg(Lang, 41, 21)
  168. Mesg = GetMsg(Lang, 41, 29)
  169. NewSheet = Trim(InputBox(Mesg, Title))
  170. Application.ScreenUpdating = False
  171.  
  172. If NewSheet <> "" Then
  173. '1/13/05: AH ICE 733561000 Need to make sure NewSheet is less then 29 characters since the name has _H appended to it and the max characters for a worksheet name is 31
  174. If Len(NewSheet) > 29 Then
  175. 'show error
  176. iMsg = MsgBox(GetMsg(Lang, 91, 13), vbExclamation)
  177. 'exit
  178. Exit Sub
  179. End If
  180.  
  181. Workbooks(CurWrkBook).Worksheets("Template").Copy after:=Sheets("Template_H")
  182. ActiveSheet.Name = NewSheet
  183. Workbooks(CurWrkBook).Worksheets("Template_H").Copy after:=Sheets(NewSheet)
  184. ActiveSheet.Name = NewSheet & "_H"
  185. Worksheets(NewSheet).Activate
  186. Else
  187. GotoControlPage
  188. End If
  189.  
  190. Workbooks(CurWrkBook).Protect
  191. End Sub
  192.  
  193.  
  194.  
  195. '========================================
  196. ' Select a Journal Sheet and activate it
  197. '========================================
  198. Public Sub JrnlSheet_Select()
  199. Dim SheetName As String
  200. Dim SheetList() As Variant
  201. Dim i As Integer
  202.  
  203. 'Get the number of sheets
  204. GetSheet
  205. SheetName = ""
  206. If ListArray(SheetList) Then
  207. With Form_SelectSheet
  208. .Caption = GetMsg(Lang, 41, 22)
  209. .ButtonOK.Caption = GetMsg(Lang, 12, 1)
  210. .ListBox1.Clear
  211. .ListBox1.List = SheetList
  212. .Height = .Height - .Target.Height - .OnlineFrame.Height - 15
  213.  
  214. Form_SelectSheet.Show
  215.  
  216. If .ListBox1.ListIndex >= 0 Then
  217. SheetName = .ListBox1.List(.ListBox1.ListIndex)
  218. End If
  219. End With
  220.  
  221. Unload Form_SelectSheet
  222. Else
  223. 'Msg: "No journal entry sheets exist. Press New to insert a new sheet."
  224. Title = GetMsg(Lang, 90, 1)
  225. Message = GetMsg(Lang, 94, 6)
  226. i = MsgBox(Message, 64, Title)
  227. End If
  228.  
  229. If SheetName = "" Then
  230. GotoControlPage
  231. Else
  232. Workbooks(CurWrkBook).Worksheets(SheetName).Activate
  233. End If
  234. End Sub
  235.  
  236.  
  237.  
  238.  
  239. '=======================
  240. ' Delete Journal Sheets
  241. '=======================
  242. Sub JrnlSheet_Delete()
  243. Dim List1 As Variant
  244. Dim SelSheet, SelHdrSheet As String
  245. Dim SheetList() As Variant
  246. Dim i As Integer
  247.  
  248. GetSheet
  249. 'Application.ScreenUpdating = False
  250. Workbooks(CurWrkBook).Unprotect
  251.  
  252. If ListArray(SheetList) Then
  253. With Form_SelectSheet
  254. .Caption = GetMsg(Lang, 41, 23)
  255. .ButtonOK.Caption = GetMsg(Lang, 41, 5)
  256. .ButtonAll.Visible = True
  257. .ButtonNone.Visible = True
  258. .ListBox1.Clear
  259. .ListBox1.List = SheetList
  260. .Height = .Height - .Target.Height - .OnlineFrame.Height - 15
  261. End With
  262.  
  263. Form_SelectSheet.Show
  264.  
  265. Set List1 = Form_SelectSheet.ListBox1
  266. For i = 1 To List1.ListCount
  267. If List1.Selected(i - 1) Then
  268. SelSheet = List1.List(i - 1)
  269. SelHdrSheet = SelSheet & "_H"
  270. Application.DisplayAlerts = False
  271. Workbooks(CurWrkBook).Worksheets(SelSheet).Delete
  272. Workbooks(CurWrkBook).Worksheets(SelHdrSheet).Delete
  273. Application.DisplayAlerts = True
  274. End If
  275. Next
  276. Unload Form_SelectSheet
  277. Else
  278. 'Msg: "No journal entry sheets exist for deletion."
  279. Title = GetMsg(Lang, 90, 1)
  280. Message = GetMsg(Lang, 94, 7)
  281. i = MsgBox(Message, 48, Title)
  282. End If
  283.  
  284. GotoControlPage
  285. End Sub
  286.  
  287.  
  288. Sub JrnlSheet_Copy()
  289. Dim SelSheet, SelHdrSheet, NameError As String
  290. Dim NewSheet, NewHdrSheet As String
  291. Dim SheetList(), WrkSheet As Variant
  292. Dim i, SeqNum, NewSeq, NewSeq1, tSeq, jid, BeginRow, EndRow As Integer
  293. Dim R As Range
  294. Dim Firstseq As Boolean
  295.  
  296.  
  297. 'Get the number of sheets
  298. GetSheet
  299. NewSheet = ""
  300. If ListArray(SheetList) Then
  301. With Form_SelectSheet
  302. .Caption = GetMsg(Lang, 41, 24)
  303. .ButtonOK.Caption = GetMsg(Lang, 41, 6)
  304. .Label2.Visible = True
  305. .Target.Visible = True
  306. .Label2.Caption = GetMsg(Lang, 41, 29)
  307. .ListBox1.Clear
  308. .ListBox1.List = SheetList
  309. .Height = .Height - .OnlineFrame.Height - 5
  310. End With
  311.  
  312. Form_SelectSheet.Show
  313.  
  314. If Form_SelectSheet.ListBox1.ListIndex >= 0 Then
  315. SelSheet = Form_SelectSheet.ListBox1.List(Form_SelectSheet.ListBox1.ListIndex)
  316. SelHdrSheet = SelSheet & "_H"
  317. NewSheet = Form_SelectSheet.Target
  318.  
  319. NameError = "N"
  320. For Each WrkSheet In Workbooks(CurWrkBook).Worksheets
  321. If StrComp(NewSheet, WrkSheet.Name, 1) = 0 Then
  322. 'name already exists
  323. NameError = "Y"
  324. End If
  325. Next
  326. If NameError = "N" Then
  327. Application.ScreenUpdating = False
  328. Application.DisplayAlerts = False
  329. NewHdrSheet = NewSheet & "_H"
  330. Workbooks(CurWrkBook).Unprotect
  331. Workbooks(CurWrkBook).Worksheets(SelSheet).Copy after:=Workbooks(CurWrkBook).Sheets("Template_H")
  332. ActiveSheet.Name = NewSheet
  333. Workbooks(CurWrkBook).Worksheets(SelHdrSheet).Copy after:=Workbooks(CurWrkBook).Sheets(NewSheet)
  334. ActiveSheet.Name = NewHdrSheet
  335. Worksheets(NewHdrSheet).Unprotect
  336. Worksheets(NewSheet).Unprotect
  337.  
  338. 'Change System IDs
  339. Worksheets(NewHdrSheet).Activate
  340. i = 3
  341. SeqNum = Val(Cells(i, HdrCtrlCOL + 1).Value)
  342. While SeqNum > 0
  343. NewSeq = Val(Options("NextSystemID"))
  344. If NewSeq = 9999 Then
  345. Call Save_Option("NextSystemID", 1001)
  346. Else
  347. Call Save_Option("NextSystemID", NewSeq + 1)
  348. End If
  349. Cells(i, HdrCtrlCOL + 1).Value = NewSeq
  350. If Firstseq = False Then
  351. NewSeq1 = NewSeq
  352. Firstseq = True
  353. End If
  354. Cells(i, LastHdrCOL - 3).Value = "" 'DocSeqNum
  355. Cells(i, HdrCtrlCOL + 3).Value = "NEXT"
  356. Worksheets(NewSheet).Activate
  357.  
  358. With Cells(2, 2).EntireColumn
  359. Set R = .Find(SeqNum, , , xlWhole, xlByRows)
  360. If Not R Is Nothing Then
  361. 'lines exist for the header
  362. BeginRow = R.Row
  363. Do
  364. tSeq = Cells(R.Row, 2).Value
  365. If tSeq = SeqNum Then
  366. EndRow = R.Row
  367. Set R = .FindNext(R)
  368. End If
  369. jid = Cells(R.Row, 5).Value
  370.  
  371. If jid <> "" Then
  372. Cells(R.Row, 5) = "NEXT"
  373. End If
  374.  
  375. Loop While Not R Is Nothing And tSeq = SeqNum And R.Row <> BeginRow
  376.  
  377. Range(Cells(BeginRow, 2), Cells(EndRow, 2)).Value = NewSeq
  378. End If
  379. End With
  380.  
  381. Worksheets(NewHdrSheet).Activate
  382. i = i + 1
  383. SeqNum = Val(Cells(i, HdrCtrlCOL + 1).Value)
  384. Wend
  385.  
  386. Worksheets(NewSheet).Activate
  387. 'Range(Cells(4, 8), Cells(4, 12)).Value = Null
  388. Cells(4, 8).Value = NewSeq1
  389. Cells(4, 10).Value = "NEXT"
  390. Cells(4, 2).Select
  391. Worksheets(NewSheet).Protect
  392. Workbooks(CurWrkBook).Protect
  393. Application.ScreenUpdating = True
  394. Else
  395. 'Msg: "Sheet name already exists."
  396. Title = GetMsg(Lang, 90, 1)
  397. Message = GetMsg(Lang, 94, 4)
  398. i = MsgBox(Message, 48, Title)
  399. Workbooks(CurWrkBook).Worksheets("Control").Activate
  400. End If
  401. Application.DisplayAlerts = True
  402. End If
  403. Unload Form_SelectSheet
  404. Else
  405. 'Msg: "No journal entry sheets exist. Press New to insert a new sheet."
  406. Title = GetMsg(Lang, 90, 1)
  407. Message = GetMsg(Lang, 94, 6)
  408. i = MsgBox(Message, 64, Title)
  409. End If
  410.  
  411. Workbooks(CurWrkBook).Protect
  412. 'Change the Import Status automatically
  413. Auto_Change_ImportStatus
  414. End Sub
  415.  
  416.  
  417.  
  418.  
  419.  
  420. Sub FormatJournal(ByVal SelSheet)
  421. Dim BotLine As Long
  422. Dim Cel As Variant
  423. Dim LastCol As Integer
  424. Dim SkipCol As Integer
  425. Dim iRow As Integer
  426.  
  427. Workbooks(CurWrkBook).Worksheets(SelSheet).Unprotect
  428. Workbooks(CurWrkBook).Worksheets(SelSheet).Activate
  429. BotLine = Range("BottomLine").Row
  430. BotLine = BotLine - 1
  431. FirstJLine = ActiveSheet.Range("InsertLine").Row
  432. FirstJLine = FirstJLine + 1
  433. LastCol = ActiveSheet.Range("LastLineCol").Column
  434. SkipCol = ActiveSheet.Range("DescrCol").Column
  435.  
  436. ActiveSheet.Range(Cells(FirstJLine, 9), Cells(BotLine, LastCol)).Select
  437. For Each Cel In Selection.Cells
  438. If Cel.Column <> SkipCol Then
  439. ' BUG 14150575
  440. If Cel.Value <> "" Then
  441. Cel.Value = UCase(Cel.Value)
  442. End If
  443. End If
  444. If Not IsNumeric(Cel.Value) Then
  445. Cel.Value = Trim(Cel.Value)
  446. If Cel.Value = "" Then
  447. Cel.Value = Empty
  448. End If
  449. End If
  450. Next
  451.  
  452. 'For iRow = 12 To BotLine
  453. ' ActiveSheet.Cells(iRow, 9).Value = UCase(ActiveSheet.Cells(iRow, 9).Value)
  454. ' ActiveSheet.Cells(iRow, 10).Value = UCase(ActiveSheet.Cells(iRow, 10).Value)
  455. ' ActiveSheet.Cells(iRow, 14).Value = UCase(ActiveSheet.Cells(iRow, 14).Value)
  456. 'Next
  457.  
  458. ActiveSheet.Range(Cells(12, 25), Cells(BotLine, 46)).Select
  459. For Each Cel In Selection.Cells
  460. If Cel.Column <> SkipCol Then
  461. If Cel.Value <> "" Then
  462. Cel.Value = UCase(Cel.Value)
  463. End If
  464. End If
  465. Next
  466.  
  467. End Sub
  468.  
  469.  
  470.  
  471. Function ListArray(SheetList() As Variant) As Boolean
  472. Dim SheetCount As Integer
  473. Dim ArraySize As Integer
  474. Dim Counter As Integer
  475. Dim CurCount As Integer
  476.  
  477. 'Get the number of sheets
  478. SheetCount = ActiveWorkbook.Worksheets.Count
  479. If SheetCount > Skipsheets Then
  480. 'Now set the array size. The number we're interested in is the total less Skipsheets (set in GetSheet).
  481. 'Then divide this number by 2 because we don't want to include the header sheets in the listbox.
  482. ArraySize = (SheetCount - Skipsheets) / 2
  483. ReDim SheetList(ArraySize) As Variant
  484. 'Counter = number of worksheets to skip + number of dialogsheets to skip.
  485. Counter = Skipsheets + SkipDlgs
  486. CurCount = 1
  487. Do
  488. SheetList(CurCount) = Workbooks(CurWrkBook).Worksheets(Counter).Name
  489. 'skip the next sheet because it's the associated header sheet
  490. Counter = Counter + 2
  491. CurCount = CurCount + 1
  492. Loop Until Counter >= SheetCount
  493. ListArray = True
  494. Else
  495. ListArray = False
  496. End If
  497. End Function
  498.  
  499.  
  500. Public Sub Set_Options()
  501. Dim LangArray() As Variant
  502. Dim i As Integer
  503. Dim tmp As String
  504.  
  505. Application.ScreenUpdating = False
  506. GetSheet
  507.  
  508. 'Set LstBox = Dlgsheet.ListBoxes("LangPrefList")
  509. 'With Dlgsheet.ListBoxes("LangPrefList")
  510. ' .List = Array("English")
  511. ' .MultiSelect = 1
  512. 'End With
  513.  
  514. 'Build_LangArray (haha)
  515. ReDim LangArray(1)
  516. LangArray(1) = "English"
  517.  
  518. With Form_Options
  519. .Caption = GetMsg(Lang, 42, 1)
  520. .HeaderDefaults_Box.Caption = GetMsg(Lang, 42, 2)
  521. .General_Box.Caption = GetMsg(Lang, 42, 3)
  522. .MessageLog_Box.Caption = GetMsg(Lang, 42, 4)
  523. .DocSeq_Box.Caption = GetMsg(Lang, 42, 5)
  524. .ImportControl_Box.Caption = GetMsg(Lang, 42, 6)
  525. .ImportStatus_Box.Caption = GetMsg(Lang, 42, 7)
  526. .ButtonOK.Caption = GetMsg(Lang, 12, 1)
  527. .ButtonCancel.Caption = GetMsg(Lang, 12, 2)
  528.  
  529. .Label11.Caption = GetMsg(Lang, 42, 11)
  530. .Label12.Caption = GetMsg(Lang, 42, 12)
  531. .Label13.Caption = GetMsg(Lang, 42, 13)
  532. .Label14.Caption = GetMsg(Lang, 42, 14)
  533. .Label15.Caption = GetMsg(Lang, 42, 15)
  534. .MultiBook_Enable.Caption = GetMsg(Lang, 42, 16)
  535. .Def_AutoGen.Caption = GetMsg(Lang, 42, 17)
  536. .Label21.Caption = GetMsg(Lang, 42, 18)
  537. .Log_ErrorsOnly.Caption = GetMsg(Lang, 42, 19)
  538. .Log_AllMessage.Caption = GetMsg(Lang, 42, 20)
  539. .Display_Mesg.Caption = GetMsg(Lang, 42, 21)
  540. .DocSeq_Enable.Caption = GetMsg(Lang, 42, 22)
  541. .Label31.Caption = GetMsg(Lang, 42, 23)
  542. .Label41.Caption = GetMsg(Lang, 42, 24)
  543. .Label42.Caption = GetMsg(Lang, 42, 15)
  544. .ChangeStatus_YES.Caption = GetMsg(Lang, 42, 25)
  545. .ChangeStatus_NO.Caption = GetMsg(Lang, 42, 26)
  546. .Unicode.Caption = GetMsg(Lang, 42, 27)
  547.  
  548. .Def_BusUnit.Value = Options("Def_BusUnit")
  549. .Def_Date.Value = Options("Def_Date")
  550. .Def_LedGrp.Value = Options("Def_LedGrp")
  551. .Def_Source.Value = Options("Def_Source")
  552. .Def_UserID.Value = Options("Def_UserID")
  553. .MultiBook_Enable = IIf(Options("Multibook") = "Y", True, False)
  554. .Def_AutoGen = IIf(Options("Def_AutoGen") = "Y", True, False)
  555. .Unicode = IIf(Options("Unicode") = "Y", True, False)
  556. .Display_Mesg = IIf(Options("DisplayMessage") = "Y", True, False)
  557. .DocSeq_Enable = IIf(Options("DocSeq") = "Y", True, False)
  558. .Def_DocType = Options("Def_DocType")
  559. .Import_URL = Options("Import_URL")
  560. .Import_UserID = Options("Import_UserID")
  561. .DuplicateOption = IIf(Options("DuplicateOption") = "S", True, False)
  562. .ErrorOption = IIf(Options("ErrorOption") = "S", True, False)
  563. .DecPosition = IIf(Options("DecPosition") = "Y", True, False)
  564.  
  565. If Options("LogMessage") = "E" Then
  566. .Log_ErrorsOnly = True
  567. Else
  568. .Log_AllMessage = True
  569. End If
  570. If Options("ChangeStatus") = "C" Then
  571. .ChangeStatus_YES = True
  572. Else
  573. .ChangeStatus_NO = True
  574. End If
  575.  
  576. .LangList.List = LangArray
  577. .LangList.MultiSelect = fmMultiSelectSingle
  578. For i = 1 To .LangList.ListCount
  579. If .LangList.List(i - 1) = Options("Language") Then
  580. .LangList.Selected(i - 1) = True
  581. End If
  582. Next
  583.  
  584. Application.ScreenUpdating = True
  585. .Show
  586. Application.ScreenUpdating = False
  587.  
  588. If Right(.Import_URL, 1) <> "/" Then
  589. .Import_URL.Value = .Import_URL.Value & "/"
  590. End If
  591.  
  592. Call Save_Option("Def_BusUnit", .Def_BusUnit.Value)
  593. Call Save_Option("Def_Date", .Def_Date.Value)
  594. Call Save_Option("Def_LedGrp", .Def_LedGrp.Value)
  595. Call Save_Option("Def_Source", .Def_Source.Value)
  596. Call Save_Option("Def_UserID", .Def_UserID.Value)
  597. Call Save_Option("Multibook", IIf(.MultiBook_Enable, "Y", "N"))
  598. Call Save_Option("Def_AutoGen", IIf(.Def_AutoGen, "Y", "N"))
  599. Call Save_Option("Unicode", IIf(.Unicode, "Y", "N"))
  600. Call Save_Option("LogMessage", IIf(.Log_ErrorsOnly, "E", "B"))
  601. Call Save_Option("DisplayMessage", IIf(.Display_Mesg, "Y", "N"))
  602. Call Save_Option("DocSeq", IIf(.DocSeq_Enable, "Y", "N"))
  603. Call Save_Option("Def_DocType", .Def_DocType)
  604. Call Save_Option("Import_URL", .Import_URL)
  605. Call Save_Option("Import_UserID", .Import_UserID)
  606. Call Save_Option("ChangeStatus", IIf(.ChangeStatus_YES, "C", "N"))
  607. Call Save_Option("DuplicateOption", IIf(.DuplicateOption, "S", "U"))
  608. Call Save_Option("ErrorOption", IIf(.ErrorOption, "S", "A"))
  609. Call Save_Option("DecPosition", IIf(.DecPosition, "Y", "N"))
  610.  
  611. If .LangList.ListIndex >= 0 Then
  612. If Options("Language") <> .LangList.List(.LangList.ListIndex) Then
  613. Set_Homepage_Labels
  614. Call Save_Option("Language", .LangList.List(.LangList.ListIndex))
  615. End If
  616. End If
  617. End With
  618.  
  619. Unload Form_Options
  620. 'If LstBox.ListIndex <> 0 Then
  621. ' With Dlgsheet.EditBoxes("OprLangPref")
  622. ' .Text = LstBox.List(LstBox.Value)
  623. ' .Enabled = False
  624. ' End With
  625. 'End If
  626. End Sub
  627.  
  628.  
  629.  
  630.  
  631. '===============================================================================
  632. ' Entry Function for Online and Batch Journal Import
  633. ' HowMany : Indicates how many journals sheets to be processed
  634. ' (SELECTED, THIS ONE)
  635. ' Mode : Indicates Onine import (XML link) or Batch import (flat file)
  636. ' (IMPORT, FILE)
  637. '===============================================================================
  638. Public Sub Import_Journals(HowMany, mode As String)
  639. Dim SelSheet, SelList As Variant
  640. Dim List1 As Variant
  641. Dim StatusBarOptn As Boolean
  642. Dim StatusBarText As String
  643. Dim Counter2 As Integer
  644. Dim Outfile As Integer
  645. Dim IndexFile As Integer
  646. Dim SheetList() As Variant
  647. Dim i As Integer
  648. Dim UsrID, Pwd As String
  649. Dim Byte1 As Byte
  650. Dim tmpStr, BaseFile, FileExt As String
  651. Dim posDot, posSlash As Integer
  652.  
  653. GetSheet
  654. StatusBarOptn = Application.DisplayStatusBar
  655. StatusBarText = Application.StatusBar
  656.  
  657. Select Case HowMany
  658. Case "THIS ONE"
  659. Set SelSheet = ActiveSheet
  660. With Form_ImportNow
  661. .Caption = GetMsg(Lang, 41, 25)
  662. .ButtonOK.Caption = GetMsg(Lang, 12, 1)
  663. .ButtonCancel.Caption = GetMsg(Lang, 12, 2)
  664. .Import_URL.Caption = Options("Import_URL")
  665. .Import_UserID.Value = Options("Import_UserID")
  666. .Label3.Caption = GetMsg(Lang, 12, 30) 'UserID
  667. .Label4.Caption = GetMsg(Lang, 12, 31) 'Password
  668. End With
  669.  
  670. Form_ImportNow.Show
  671. Application.ScreenUpdating = False
  672.  
  673. If Form_ImportNow.Import_UserID <> "" Then
  674. Call Import_Sheet(SelSheet.Name, mode, Outfile, Form_ImportNow.Import_UserID, Form_ImportNow.Import_Pwd)
  675. End If
  676. Unload Form_ImportNow
  677. SelSheet.Activate
  678. Cells(11, 8).Select
  679.  
  680. Case "SELECTED"
  681. If ListArray(SheetList) Then
  682. With Form_SelectSheet
  683. If mode = "IMPORT" Then
  684. .Caption = GetMsg(Lang, 41, 25)
  685. .ButtonAll.Caption = GetMsg(Lang, 41, 7)
  686. .ButtonAll.Visible = True
  687. .ButtonNone.Visible = True
  688. .OnlineFrame.Visible = True
  689. .Height = .Height - .Target.Height - 10
  690. .OnlineFrame.Top = .OnlineFrame.Top - .Target.Height - 10
  691. .OnlineFrame.Caption = GetMsg(Lang, 12, 29)
  692. .Import_URL.Caption = Options("Import_URL")
  693. .Import_UserID.Value = Options("Import_UserID")
  694. .Label3.Caption = GetMsg(Lang, 12, 30) 'UserID
  695. .Label4.Caption = GetMsg(Lang, 12, 31) 'Password
  696. Else
  697. .Caption = GetMsg(Lang, 41, 26)
  698. .ButtonAll.Caption = GetMsg(Lang, 41, 8)
  699. .ButtonAll.Visible = True
  700. .ButtonNone.Visible = True
  701. .Label2.Visible = True
  702. .Target.Visible = True
  703. .WriteIndex.Visible = True
  704. .Label2.Caption = GetMsg(Lang, 41, 28)
  705. .Target = Application.DefaultFilePath & "\" & Mid(CurWrkBook, 1, InStr(1, CurWrkBook, ".", 1)) & "txt"
  706. .Height = .Height - .OnlineFrame.Height - 5
  707. End If
  708. .ListBox1.Clear
  709. .ListBox1.List = SheetList
  710. End With
  711.  
  712. Form_SelectSheet.Show
  713. Set List1 = Form_SelectSheet.ListBox1
  714. Application.ScreenUpdating = False
  715.  
  716. For i = 1 To List1.ListCount
  717. If List1.Selected(i - 1) Then
  718. If mode <> "IMPORT" Then
  719. If Form_SelectSheet.WriteIndex = True Then
  720. If IndexFile = 0 Then
  721. posDot = InStrRev(Form_SelectSheet.Target, ".")
  722. posSlash = InStrRev(Form_SelectSheet.Target, "\")
  723. If posDot > 0 And posSlash > 0 And posSlash > posDot Then
  724. posDot = 0
  725. End If
  726. If posDot = 0 Then
  727. BaseFile = Form_SelectSheet.Target & "_"
  728. FileExt = ".txt"
  729. Else
  730. BaseFile = Left(Form_SelectSheet.Target, posDot - 1) & "_"
  731. FileExt = Mid(Form_SelectSheet.Target, posDot)
  732. End If
  733.  
  734. IndexFile = FreeFile()
  735. Open (BaseFile & "index" & FileExt) For Output As IndexFile
  736. Else
  737. Close #Outfile
  738.  
  739. End If
  740. tmpStr = BaseFile & List1.List(i - 1) & FileExt
  741. Print #IndexFile, tmpStr
  742. Outfile = FreeFile()
  743.  
  744. Open tmpStr For Output As Outfile
  745. Else
  746. If Outfile = 0 Then
  747. Outfile = FreeFile()
  748.  
  749.  
  750. Open Form_SelectSheet.Target For Output As Outfile
  751. End If
  752. End If
  753. End If
  754. Call Import_Sheet(List1.List(i - 1), mode, Outfile, Form_SelectSheet.Import_UserID, Form_SelectSheet.Import_Pwd)
  755. End If
  756. Next
  757. Unload Form_SelectSheet
  758.  
  759. If mode <> "IMPORT" And Outfile <> 0 Then
  760. If IndexFile <> 0 Then
  761. Close #IndexFile
  762. End If
  763. Close #Outfile
  764.  
  765. End If
  766. Else
  767. 'Msg: "No journal sheet for import"
  768. Title = GetMsg(Lang, 95, 1)
  769. Message = GetMsg(Lang, 95, 2)
  770. i = MsgBox(Message, 64, Title)
  771. End If
  772. End Select
  773.  
  774. Application.StatusBar = StatusBarText
  775. Application.StatusBar = GetMsg(Lang, 1, 3)
  776. Application.DisplayStatusBar = StatusBarOptn
  777. If HowMany <> "THIS ONE" Then
  778. Call GotoControlPage
  779. End If
  780. End Sub
  781.  
  782.  
  783.  
  784. '=========================================
  785. ' Online import the given journal sheet
  786. '=========================================
  787. 'Private Sub Import_Sheet(LineSheet As String, mode As String, OutFile As Integer, UsrID, Pwd As String)
  788. Private Sub Import_Sheet(LineSheet As String, mode As String, Outfile As Integer, UsrID As String, Pwd As String)
  789.  
  790. Dim HdrSheet As String
  791. Dim strXML As String
  792. Dim strURL As String
  793. Dim strResponse As String
  794. Dim FirstRowNum As Integer
  795. Dim LastRowNum As Integer
  796. Dim ReturnCD As Long
  797. Dim i As Long
  798. ' ICE: 688200002: AH
  799. Dim iGenerateXML As Integer
  800.  
  801. HdrSheet = LineSheet & "_H"
  802. ActiveWorkbook.Worksheets(LineSheet).Activate
  803.  
  804. Application.DisplayStatusBar = True
  805. Application.StatusBar = GetMsg(Lang, 1, 1)
  806. FormatJournal (LineSheet)
  807.  
  808. 'CheckHdrs
  809.  
  810. 'Import the Journal sheet
  811. 'NvsError = ImportSheet(ActiveSheet)
  812.  
  813.  
  814. If mode <> "IMPORT" Then
  815. ' ICE: 688200002: AH: Adds a parameter to store status code
  816. Call Generate_XML(strXML, LineSheet, 0, iGenerateXML)
  817. ' ICE: 688200002: AH if iGenerateXML is 0, i.e. no error
  818. If iGenerateXML = 0 Then
  819. Print #Outfile, strXML
  820. End If
  821. Else
  822. strXML = "<?xml version=""1.0""?>" & vbCrLf
  823. strXML = strXML & "<Postreq>" & vbCrLf
  824.  
  825. strXML = strXML & "<PROCESS_OPTION>" & vbCrLf
  826. strXML = strXML & " <DUPLICATE_OPTION>" & Options("DuplicateOption") & "</DUPLICATE_OPTION>" & vbCrLf
  827. strXML = strXML & " <ERROR_OPTION>" & Options("ErrorOption") & "</ERROR_OPTION>" & vbCrLf
  828. strXML = strXML & " <DECIMAL_POSITION>" & Options("DecPosition") & "</DECIMAL_POSITION>" & vbCrLf
  829. strXML = strXML & " <DEFAULT_USERID>" & UsrID & "</DEFAULT_USERID>" & vbCrLf
  830. strXML = strXML & "</PROCESS_OPTION>" & vbCrLf
  831.  
  832. ' ICE: 688200002: AH: Adds a parameter to store status code
  833. Call Generate_XML(strXML, LineSheet, 2, iGenerateXML)
  834. ' ICE: 688200002: AH if iGenerateXML is 0, i.e. no error
  835.  
  836. If iGenerateXML = 0 Then
  837.  
  838. strXML = strXML & "</Postreq>" & vbCrLf
  839.  
  840.  
  841. 'http://<server>/xmllink/ps/Excel_Journal_Import?userid=<user-id>&pwd=<password>
  842. Rem AG 3323966 Posting directly so do not append service name
  843. Rem and append a & instead of ? before userid and pwd
  844.  
  845. 'strURL = Options("Import_URL") & "Excel_Journal_Import"
  846. strURL = Options("Import_URL")
  847. strURL = Replace(strURL, "xmllink", "psc")
  848. strURL = strURL & "EMPLOYEE/ERP/s/WEBLIB_GL.JOURNAL_ID.FieldFormula.Iscript_Excel_Journal/?postDataBin=y&disconnect=y"
  849. 'strURL = strURL & "&userid=" & UsrID & "&pwd=" & Pwd
  850.  
  851.  
  852.  
  853. ReturnCD = sendXML(strXML, strResponse, strURL, LineSheet, UsrID, Pwd)
  854.  
  855. Select Case ReturnCD
  856.  
  857. Case 0
  858. ' Import was successful
  859. Select Case Options("ChangeStatus")
  860. Case "N"
  861. 'do nothing
  862. ActiveSheet.Protect
  863. Case "C"
  864. 'Change Header status to IMPORTED
  865. ActiveWorkbook.Worksheets(HdrSheet).Activate
  866. ActiveSheet.Unprotect
  867. LastRowNum = ActiveSheet.Columns(2).Find("", , xlFormulas, xlWhole, xlByRows).Row - 1
  868. ActiveSheet.Range(Cells(3, 2), Cells(LastRowNum, 2)).Select
  869. Selection.Value = "U"
  870. ActiveSheet.Protect
  871.  
  872. 'Change Line status to IMPORTED
  873. ActiveWorkbook.Worksheets(LineSheet).Activate
  874. ActiveSheet.Unprotect
  875. FirstRowNum = ActiveSheet.Range("InsertLine").Row + 1
  876. LastRowNum = ActiveSheet.Range("BottomLine").Row - 1
  877. ActiveSheet.Range(Cells(FirstRowNum, 3), Cells(LastRowNum, 3)).Select
  878. Selection.Value = "U"
  879. ActiveSheet.Protect
  880. End Select
  881. Case -101
  882. 'Unknow generic error
  883.  
  884. Case -102
  885. 'Error loading the source XML document
  886. Case -103
  887. 'Error loading response XML document
  888. Case -104
  889. 'Application Error message found
  890.  
  891. Case Else
  892. 'Import error: unable to import journal headers associated with sheet
  893. Title = GetMsg(Lang, 95, 1)
  894. Message = GetMsg(Lang, 95, 4)
  895. Message = Message & " '" & LineSheet
  896. If Options("DisplayMessage") = "E" Then
  897. i = MsgBox(Message, 16, Title)
  898. End If
  899. End Select
  900.  
  901. If ReturnCD = 0 Then
  902. If Options("LogMessage") = "B" Then
  903. Call AddLogEntry(LogFileName, LineSheet, "Success", strResponse)
  904. End If
  905. If Options("DisplayMessage") = "Y" Then
  906. i = MsgBox(strResponse, vbOKOnly, "Import OK - Sheet " & LineSheet)
  907. End If
  908. Else
  909. Call AddLogEntry(LogFileName, LineSheet, "Import failed", strResponse)
  910. If Options("DisplayMessage") = "Y" Then
  911. i = MsgBox(strResponse, vbExclamation + vbOKOnly, "Import Failed - Sheet " & LineSheet)
  912. End If
  913. End If
  914. ' ICE: 688200002: AH
  915. End If
  916. End If
  917.  
  918. End Sub
  919.  
  920.  
  921.  
  922. '===============================================
  923. ' Create XML elements from given rage of data
  924. ' ICE: 688200002: Add checking on OPEN_ITEM_KEY to make sure there is no space in the middle of the value
  925. '===============================================
  926. Private Function Row2XML(Sel As Range, indent As Integer, Optional HdrSheet As String) As String
  927. Dim Cel As Range
  928. Dim FldValue As String
  929. Dim DecChar As String
  930. Dim i As Integer
  931. Dim XML As String
  932. Dim sColName As String
  933. Dim iMsg As Integer
  934.  
  935.  
  936.  
  937. DecChar = Application.International(xlDecimalSeparator)
  938. XML = ""
  939. For Each Cel In Sel
  940. If IsNumeric(Cel.Value) And Cel.NumberFormat <> "@" Then
  941. If Cel.Value = 0 Then
  942. FldValue = ""
  943. Else
  944. Rem Bug13784334-UNEXPECTED WARNING MESSAGE WHEN CHECKING THE DECIMALS IN JRNL1-XLS-WINDOW
  945. FldValue = Cel.Value
  946. Rem FldValue = CDec(Cel.Value)
  947. Rem If DecChar <> "." And InStr(FldValue, DecChar) > 0 Then
  948. Rem Mid(FldValue, InStr(FldValue, DecChar), 1) = "."
  949. Rem End If
  950. End If
  951. Else
  952. If IsDate(Cel.Value) And Cel.NumberFormat <> "@" Then
  953. FldValue = Format$(Cel.Value, "yyyymmdd")
  954. Else
  955. FldValue = Encode_XMLchar(Trim$(Cel.Value))
  956. End If
  957. End If
  958.  
  959. ' ICE: 688200002: ah BEGIN
  960. sColName = UCase$(Cells(1, Cel.Column).Value)
  961. If sColName = "OPEN_ITEM_KEY" And Len(FldValue) > 0 Then
  962. 'Make sure FldValue does not have space in the middle
  963. If ImbeddedSpace(FldValue) Then
  964. iMsg = MsgBox(GetMsg(Lang, 91, 12), vbExclamation, HdrSheet)
  965. Err.Raise (-999)
  966. End If
  967. End If
  968. ' ICE: 688200002: ah END
  969.  
  970. If Len(FldValue) > 0 Then
  971. XML = XML & Space$(indent) & "<" & UCase$(Cells(1, Cel.Column).Value) & ">"
  972. XML = XML & FldValue
  973. XML = XML & "</" & UCase$(Cells(1, Cel.Column).Value) & ">" & vbCrLf
  974. End If
  975. Next
  976.  
  977. Row2XML = XML
  978. End Function
  979.  
  980.  
  981.  
  982. '=============================================
  983. ' Generate XML string for the current sheet
  984. '=============================================
  985. 'ICE: 688200002 Add iStatus for status indicator
  986. Private Sub Generate_XML(XMLtext As String, LineSheet As String, indent As Integer, iStatus As Integer)
  987. Dim DataRange As Range
  988. Dim i As Integer
  989. Dim HdrSeqNum As Integer
  990. Dim FirstRowNum As Integer
  991. Dim ThisRow As Range
  992. Dim HdrSheet As String
  993. Dim HeaderArray() As Variant
  994. Dim tempXML As String 'use smaller chunk of XML string for performance
  995. ' ICE: 688200002: ah BEGIN set iStatus = 0 to indicate there is no error first
  996. iStatus = 0
  997. On Error GoTo doError
  998. ' ICE: 688200002: ah END
  999.  
  1000. HdrSheet = LineSheet & "_H"
  1001. ActiveWorkbook.Worksheets(HdrSheet).Activate
  1002. If Build_HdrArray(HdrSheet, HeaderArray) Then
  1003. For i = LBound(HeaderArray) To UBound(HeaderArray)
  1004. If Cells(i + 2, 2) = "C" Then
  1005. Set DataRange = ActiveSheet.Range(Cells(i + 2, HdrCtrlCOL + 1), Cells(i + 2, LastHdrCOL))
  1006. XMLtext = XMLtext & Space(indent) & "<JRNL_HDR_IMP>" & vbCrLf
  1007. XMLtext = XMLtext & Row2XML(DataRange, indent + 2)
  1008.  
  1009. HdrSeqNum = Cells(i + 2, 3).Value
  1010. ActiveWorkbook.Worksheets(LineSheet).Activate
  1011. With Range(Cells(2, 2), Cells(Range("BottomLine").Row, 2))
  1012. Set ThisRow = .Find(HdrSeqNum, , , xlWhole, xlByRows)
  1013. If Not ThisRow Is Nothing Then
  1014. FirstRowNum = ThisRow.Row
  1015. tempXML = ""
  1016. Do
  1017. If Cells(ThisRow.Row, 3) = "C" Then
  1018. Set DataRange = Range(Cells(ThisRow.Row, 8), Cells(ThisRow.Row, Range("LastLineCol").Column))
  1019. tempXML = tempXML & Space(indent + 2) & "<JRNL_LN_IMP>" & vbCrLf
  1020. ' ICE: 688200002:
  1021. tempXML = tempXML & Row2XML(DataRange, indent + 4, "Sheet: " & LineSheet)
  1022. tempXML = tempXML & Space(indent + 2) & "</JRNL_LN_IMP>" & vbCrLf
  1023.  
  1024. If Len(tempXML) > 10000 Then
  1025. XMLtext = XMLtext & tempXML
  1026. tempXML = ""
  1027. End If
  1028. End If
  1029. Set ThisRow = .FindNext(ThisRow)
  1030. Loop While Not ThisRow Is Nothing And ThisRow.Row <> FirstRowNum
  1031. XMLtext = XMLtext & tempXML
  1032. tempXML = ""
  1033. End If
  1034. End With
  1035. XMLtext = XMLtext & Space(indent) & "</JRNL_HDR_IMP>" & vbCrLf
  1036. ActiveWorkbook.Worksheets(HdrSheet).Activate
  1037. End If
  1038. Next i
  1039. End If
  1040. ' ICE: 688200002: ah BEGIN
  1041. doError:
  1042. If Err.Number <> 0 Then
  1043. iStatus = Err.Number
  1044. End If
  1045. ' ICE: 688200002: ah END
  1046. End Sub
  1047.  
  1048.  
  1049.  
  1050.  
  1051. '======================================
  1052. ' Post and Send journal over the web
  1053. '======================================
  1054.  
  1055. 'Private Function sendXML(strXML, strResponse, strURL, LineSheet, UsrID, Pwd As String) As Long
  1056. Private Function sendXML(strXML, strResponse, strURL As String, LineSheet, UsrID As String, Pwd As String) As Long
  1057.  
  1058. Const SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS = 13056
  1059. 'Dim xDoc As MSXML2.DOMDocument60
  1060. 'Dim xHTTP As XMLHTTP
  1061. 'Const WinHttpRequestOption_EnableHttp1_1 = 17
  1062. 'Dim xHTTP As MSXML2.ServerXMLHTTP60
  1063. Dim xDoc As MSXML.DOMDocument
  1064. Dim xHTTP As MSXML.XMLHTTPRequest
  1065.  
  1066. 'Dim xError, xImported, xUpdated As MSXML.IXMLDOMElement
  1067. 'Added an additional element xWarning to handle the warnings
  1068.  
  1069. 'Dim xError, xWarning, xImported, xUpdated As IXMLDOMNodeList
  1070. 'Dim tmp, tmp2, tmp3 As String
  1071. 'Dim StatusNum As Long
  1072. 'Dim i, k, RowNum As Integer
  1073. 'Dim Y, M, D, Dsep, Jdate, SysID, jid As String
  1074. 'Dim Dorder As Integer
  1075.  
  1076. Dim xError, xImported, xUpdated As MSXML.IXMLDOMNodeList
  1077. Dim tmp As String
  1078. Dim StatusNum As Long
  1079. Dim i, k As Integer
  1080.  
  1081. On Error GoTo doError
  1082. strResponse = ""
  1083. 'Set xDoc = New MSXML2.DOMDocument60
  1084. Set xDoc = New MSXML.DOMDocument
  1085. 'Set xDoc = CreateObject("MSXML.DOMDocument")
  1086.  
  1087. xDoc.async = False
  1088. If xDoc.loadXML(strXML) Then
  1089. 'Set xHTTP = New XMLHTTP
  1090.  
  1091. 'Set xHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")
  1092. Set xHTTP = New MSXML.XMLHTTPRequest
  1093. 'Set xHTTP = CreateObject("MSXML.DOMDocument")
  1094. 'xHTTP.setTimeouts 0, 0, 0, 0
  1095.  
  1096. 'xHTTP.Open "POST", strURL, False, UsrID, Pwd
  1097.  
  1098.  
  1099. 'xHTTP.setRequestHeader "content-type", "application/x-www-form-urlencoded"
  1100. 'xHTTP.setRequestHeader "accept", "text/xml/html"
  1101. 'xHTTP.setRequestHeader "accept-charset", "utf-8, iso_8859-1"
  1102.  
  1103. 'xHTTP.setRequestHeader "userid", UsrID
  1104. 'xHTTP.setRequestHeader "pwd", Pwd
  1105. 'xHTTP.setOption 2, SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS
  1106.  
  1107. xHTTP.Open "POST", strURL, False
  1108.  
  1109. xHTTP.setRequestHeader "content-type", "application/x-www-form-urlencoded"
  1110. xHTTP.setRequestHeader "accept", "text/xml/html"
  1111. xHTTP.setRequestHeader "accept-charset", "utf-8, iso_8859-1"
  1112. Rem AG 3323966 Adding the Username and Password to the header
  1113. xHTTP.setRequestHeader "userid", UsrID
  1114. xHTTP.setRequestHeader "pwd", Pwd
  1115.  
  1116. xHTTP.send xDoc.XML
  1117.  
  1118. strResponse = xHTTP.responseText
  1119.  
  1120. If xDoc.loadXML(strResponse) Then
  1121. Dsep = Application.International(xlDateSeparator)
  1122. Dorder = Application.International(xlDateOrder)
  1123. 'xlDateOrder (0=MDY, 1=DMY, 2=YMD)
  1124. Set xError = xDoc.getElementsByTagName("error")
  1125. strResponse = ""
  1126.  
  1127. Set xImported = xDoc.getElementsByTagName("imported")
  1128. tmp = ""
  1129. k = 0
  1130. ActiveWorkbook.Worksheets(LineSheet).Activate
  1131. ActiveSheet.Unprotect
  1132. ActiveWorkbook.Worksheets(LineSheet & "_H").Activate
  1133. ActiveSheet.Unprotect
  1134.  
  1135. For i = 1 To xImported.Length
  1136. tmp2 = Decode_XMLchar(xImported.Item(i - 1).Text)
  1137. tmp3 = Mid(tmp2, InStr(tmp2, ")") - 10, 10)
  1138. Y = Left(tmp3, 4)
  1139. M = Mid(tmp3, 6, 2)
  1140. D = Right(tmp3, 2)
  1141. If Dorder = 0 Then
  1142. Jdate = M & Dsep & D & Dsep & Y
  1143. ElseIf Dorder = 1 Then
  1144. Jdate = D & Dsep & M & Dsep & Y
  1145. Else
  1146. Jdate = Y & Dsep & M & Dsep & D
  1147. End If
  1148. tmp = tmp & vbLf & " " & Replace(tmp2, tmp3, Jdate)
  1149. k = k + 1
  1150.  
  1151. SysID = RTrim(Left(tmp2, InStr(tmp2, "(") - 1))
  1152. tmp3 = LTrim(Mid(tmp2, InStr(tmp2, ",") + 1))
  1153. jid = RTrim(Left(tmp3, InStr(tmp3, ",") - 1))
  1154. RowNum = ActiveSheet.Columns(3).Find(SysID, , xlFormulas, xlWhole, xlByRows).Row
  1155. If Cells(RowNum, 5) = "NEXT" Then
  1156. Cells(RowNum, 5) = jid
  1157.  
  1158. ActiveWorkbook.Worksheets(LineSheet).Activate
  1159. If Cells(4, 8) = Val(SysID) And Cells(4, 10) = "NEXT" Then
  1160. Cells(4, 10) = jid
  1161. End If
  1162. RowNum = ActiveSheet.Columns(2).Find(SysID, , xlFormulas, xlWhole, xlByRows).Row
  1163. While Cells(RowNum, 2) = Val(SysID)
  1164. If Cells(RowNum, 5) = "NEXT" Then
  1165. Cells(RowNum, 5) = jid
  1166. End If
  1167. RowNum = RowNum + 1
  1168. Wend
  1169. ActiveWorkbook.Worksheets(LineSheet & "_H").Activate
  1170. End If
  1171. Next
  1172. strResponse = GetMsg(Lang, 80, 2, str(k)) & tmp
  1173. ActiveWorkbook.Worksheets(LineSheet & "_H").Activate
  1174. ActiveSheet.Protect
  1175. ActiveWorkbook.Worksheets(LineSheet).Activate
  1176. ActiveSheet.Protect
  1177.  
  1178. Set xUpdated = xDoc.getElementsByTagName("updated")
  1179. tmp = ""
  1180. k = 0
  1181. For i = 1 To xUpdated.Length
  1182. tmp2 = Decode_XMLchar(xUpdated.Item(i - 1).Text)
  1183. tmp3 = Mid(tmp2, InStr(tmp2, ")") - 10, 10)
  1184. Y = Left(tmp3, 4)
  1185. M = Mid(tmp3, 6, 2)
  1186. D = Right(tmp3, 2)
  1187. If Dorder = 0 Then
  1188. Jdate = M & Dsep & D & Dsep & Y
  1189. ElseIf Dorder = 1 Then
  1190. Jdate = D & Dsep & M & Dsep & Y
  1191. Else
  1192. Jdate = Y & Dsep & M & Dsep & D
  1193. End If
  1194. tmp = tmp & vbLf & " " & Replace(tmp2, tmp3, Jdate)
  1195. k = k + 1
  1196. Next
  1197. If k > 0 Then
  1198. strResponse = strResponse & vbLf & GetMsg(Lang, 80, 3, str(k)) & tmp
  1199. End If
  1200. 'Warnings are processed in this loop
  1201. Set xWarning = xDoc.getElementsByTagName("warning")
  1202. i = 0
  1203. tmp = ""
  1204. For i = 1 To xWarning.Length
  1205. If i > 1 Then
  1206. tmp = tmp & vbLf
  1207. End If
  1208. tmp = tmp & Decode_XMLchar(xWarning.Item(i - 1).Text)
  1209. Next
  1210.  
  1211. strResponse = strResponse & vbLf & tmp
  1212. sendXML = 0
  1213. If xError.Length > 0 Then
  1214. 'Application Error message found
  1215. If Options("ErrorOption") <> "S" Then
  1216. sendXML = -104
  1217. End If
  1218.  
  1219. i = 0
  1220. tmp = ""
  1221. For i = 1 To xError.Length
  1222. If i > 1 Then
  1223. tmp = tmp & vbLf
  1224. End If
  1225. tmp = tmp & Decode_XMLchar(xError.Item(i - 1).Text)
  1226. Next
  1227.  
  1228. strResponse = strResponse & vbLf & tmp
  1229. End If
  1230. Else
  1231. 'Error loading response XML document
  1232. sendXML = -103
  1233. End If
  1234. Else
  1235. 'Error loading the source XML document
  1236. sendXML = -102
  1237. strResponse = xDoc.parseError.reason & vbCrLf & strXML
  1238. End If
  1239. Exit Function
  1240. doError:
  1241. If Err.Number <> 0 Then
  1242. sendXML = Err.Number
  1243. strResponse = Err.Description & Options("Import_URL")
  1244. Else
  1245. 'Unknown error
  1246. sendXML = -101
  1247. End If
  1248. End Function
  1249.  
  1250.  
  1251. Public Sub CustomizeCF()
  1252. Form_CustomizeCF.Show
  1253. End Sub
  1254.  
  1255.  
  1256.  
  1257.  
  1258. Private Sub genericErrorHandler(objErr As ErrObject, strProcName As String)
  1259. MsgBox prompt:="The following error occurred in procedure " & strProcName _
  1260. & ": " & vbCrLf & objErr.Description & vbCrLf & "Error #: " & objErr.Number, _
  1261. Title:="ERROR"
  1262. End Sub
  1263.  
  1264. '11/16/04: AH Created this for ICE 622590000
  1265. 'The purpose of this function is to encode special characters contained in UID/password ! @ # $ % ^ & * ( ) - _ = + \ |[ ] {} ; : / ? . > <
  1266. Public Function URL_Encode(ByVal sOriginal As String) As String
  1267. Dim i_Len, i_Counter As Integer
  1268. Dim s_Encoded, s_Parsed As String
  1269. 'figure out length of passed in string
  1270. i_Len = Len(sOriginal)
  1271.  
  1272. s_Encoded = ""
  1273. s_Parsed = ""
  1274. 'parse each character and replace it with encoded value if it's one of the special character
  1275. For i_Counter = 1 To i_Len
  1276. 'get each character out of the sOriginal string
  1277. s_Parsed = Mid(sOriginal, i_Counter, 1)
  1278. Select Case s_Parsed
  1279. Case "!"
  1280. s_Encoded = s_Encoded + "%21"
  1281. Case "@"
  1282. s_Encoded = s_Encoded + "%40"
  1283. Case "#"
  1284. s_Encoded = s_Encoded + "%23"
  1285. Case "$"
  1286. s_Encoded = s_Encoded + "%24"
  1287. Case "%"
  1288. s_Encoded = s_Encoded + "%25"
  1289. Case "^"
  1290. s_Encoded = s_Encoded + "%5E"
  1291. Case "&"
  1292. s_Encoded = s_Encoded + "%26"
  1293. Case "*"
  1294. s_Encoded = s_Encoded + "%2A"
  1295. Case "("
  1296. s_Encoded = s_Encoded + "%28"
  1297. Case ")"
  1298. s_Encoded = s_Encoded + "%29"
  1299. Case "-"
  1300. s_Encoded = s_Encoded + "%2D"
  1301. Case "_"
  1302. s_Encoded = s_Encoded + "%5F"
  1303. Case "="
  1304. s_Encoded = s_Encoded + "%3D"
  1305. Case "+"
  1306. s_Encoded = s_Encoded + "%2B"
  1307. Case "\"
  1308. s_Encoded = s_Encoded + "%5C"
  1309. Case "|"
  1310. s_Encoded = s_Encoded + "%7C"
  1311. Case "["
  1312. s_Encoded = s_Encoded + "%5B"
  1313. Case "]"
  1314. s_Encoded = s_Encoded + "%5D"
  1315. Case "{"
  1316. s_Encoded = s_Encoded + "%7B"
  1317. Case "}"
  1318. s_Encoded = s_Encoded + "%7D"
  1319. Case ";"
  1320. s_Encoded = s_Encoded + "%3B"
  1321. Case ":"
  1322. s_Encoded = s_Encoded + "%3A"
  1323. Case "/"
  1324. s_Encoded = s_Encoded + "%2F"
  1325. Case "?"
  1326. s_Encoded = s_Encoded + "%3F"
  1327. Case "."
  1328. s_Encoded = s_Encoded + "%2E"
  1329. Case ">"
  1330. s_Encoded = s_Encoded + "%3E"
  1331. Case "<"
  1332. s_Encoded = s_Encoded + "%3C"
  1333. Case Else
  1334. s_Encoded = s_Encoded + s_Parsed
  1335. End Select
  1336. Next
  1337.  
  1338. URL_Encode = s_Encoded
  1339.  
  1340. End Function
  1341.  
  1342.  
  1343.  
  1344.  
  1345.  
Compilation error #stdin compilation error #stdout 0s 0KB
stdin
Standard input is empty
compilation info
Visual Basic.Net Compiler version 0.0.0.5943 (Mono 3.8 - tarball)
Copyright (C) 2004-2010 Rolf Bjarne Kvinge. All rights reserved.

/home/dJYvhu/prog.vb (3,12) : error VBNC30206: 'Option' must be followed by 'Compare', 'Explicit', or 'Strict'.
/home/dJYvhu/prog.vb (4,7): Compiler error around this location, the compiler hasn't implemented the error message, nor error recovery, so the compiler will probably crash soon.
   at vbnc.Helper.ErrorRecoveryNotImplemented(Span Location)
   at vbnc.Parser.ParseAssemblyDeclaration(System.String RootNamespace, vbnc.AssemblyDeclaration assembly)
   at vbnc.Parser.Parse(System.String RootNamespace, vbnc.AssemblyDeclaration assembly)
   at vbnc.Compiler.Compile_Parse()
   at vbnc.Compiler.Compile()
   at vbnc.Compiler.Compile(System.String[] CommandLine)
   at vbnc.Main.Main(System.String[] CmdArgs)
/home/dJYvhu/prog.vb (6,4) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (7,4) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (8,4) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (9,4) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (10,4) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (11,4) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (12,4) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (13,4) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (14,4) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (15,4) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (16,12) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (19,11) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (20,16) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (21,9) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (22,15) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (23,20) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (24,8) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (28,16) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (30,12) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (31,13) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (35,11) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (37,7) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (38,23) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (39,23) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (40,23) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (41,11) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (42,8) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (46,16) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (47,8) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (48,8) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (49,8) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (50,8) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (51,19) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (52,13) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (56,16) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (57,8) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (58,8) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (59,8) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (60,8) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (61,19) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (62,13) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (66,12) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (67,9) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (68,10) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (69,10) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (70,10) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (71,10) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (72,10) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (73,10) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (74,10) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (75,10) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (76,10) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (77,10) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (78,10) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (79,13) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (80,19) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (81,8) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (86,4) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (87,15) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (88,17) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (89,20) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (90,9) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (117,16) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (118,7) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (119,20) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (120,11) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (127,15) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (132,13) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (134,8) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (139,9) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (140,4) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (141,4) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (142,4) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (144,18) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (146,20) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (147,7) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (148,25) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (149,23) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (150,11) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (151,26) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (152,15) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (153,11) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (154,13) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (159,4) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (160,4) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (161,4) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (162,4) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (164,13) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (165,14) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (167,10) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (168,9) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (169,13) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (170,16) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (172,7) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (174,11) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (176,16) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (178,16) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (179,15) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (181,18) : error VBNC30203: Identifier expected.
/home/dJYvhu/prog.vb (182,20) : error VBNC30203: Identifier expected.
vbnc : Command line : error VBNC30041: Too many errors.
stdout
Standard output is empty