'This module contains the macros for the main control sheet
Option Base 1
Option Explicit
Dim CurWrkBook As String
Dim CurrentSheet As String
Dim CurrentHdrSheet As String
Dim Title As String
Dim Message As String
Dim Successful As Boolean
Dim LogFileName As String
Dim Skipsheets As Integer
Dim SkipDlgs As Integer
Dim FirstJLine As Integer
Public Lang As String
Public Sub Init()
Application.ScreenUpdating = False
Lang = Options("Language")
CurWrkBook = ActiveWorkbook.Name
GotoControlPage
End Sub
Public Function Options(EdBox As String, Optional Text As String) As String
'Returns the option setting
Options = ActiveWorkbook.DialogSheets("Options").EditBoxes(EdBox).Text
End Function
Public Sub Save_Option(EdBox, NewText As String)
'Sets the options
If ActiveWorkbook.DialogSheets("Options").EditBoxes(EdBox).Text <> NewText Then
ActiveWorkbook.DialogSheets("Options").Unprotect
ActiveWorkbook.DialogSheets("Options").EditBoxes(EdBox).Text = NewText
ActiveWorkbook.DialogSheets("Options").Protect
End If
End Sub
Public Function Encode_XMLchar(tmp As String) As String
tmp = Replace(tmp, "&", "&")
tmp = Replace(tmp, "<", "<")
tmp = Replace(tmp, ">", ">")
tmp = Replace(tmp, "'", "'")
Encode_XMLchar = Replace(tmp, """", """)
End Function
Public Function Decode_XMLchar(tmp As String) As String
tmp = Replace(tmp, "&", "&")
tmp = Replace(tmp, "<", "<")
tmp = Replace(tmp, ">", ">")
tmp = Replace(tmp, "'", "'")
Decode_XMLchar = Replace(tmp, """, """")
End Function
Private Sub Set_Homepage_Labels()
With ActiveWorkbook.Sheets(1)
.SetDefaults.Caption = GetMsg(Lang, 41, 1)
.UserNotes.Caption = GetMsg(Lang, 41, 2)
.NewSheet.Caption = GetMsg(Lang, 41, 3)
.EditSheet.Caption = GetMsg(Lang, 41, 4)
.ClearSheet.Caption = GetMsg(Lang, 41, 5)
.CopySheet.Caption = GetMsg(Lang, 41, 6)
.ImportSelected.Caption = GetMsg(Lang, 41, 7)
.WriteSelected.Caption = GetMsg(Lang, 41, 8)
.GroupBoxes("GroupBox1").Text = GetMsg(Lang, 41, 12)
.GroupBoxes("GroupBox2").Text = GetMsg(Lang, 41, 13)
.GroupBoxes("GroupBox3").Text = GetMsg(Lang, 41, 14)
End With
ActiveWorkbook.Sheets(2).GoControlPage.Caption = GetMsg(Lang, 41, 9)
End Sub
'This macro sets information about the workbook and sheet.
Sub GetSheet()
CurWrkBook = ActiveWorkbook.Name
CurrentSheet = ActiveSheet.Name
CurrentHdrSheet = CurrentSheet & "_H"
Lang = Options("Language")
'Get the user's preferred Lang
'Set Settings = UserForms("Form_Options")
'On Error Resume Next ' turn off error checking
'Set wbMyAddin = Workbooks(AddIns("Jrnlmcro").Name)
'LastError = Err.Number
'On Error GoTo 0 ' restore error checking
'If LastError = 0 Then
'Jrnlmcro.xla is already loaded.
' Run ("GoToControlPage")
'Else
' 'Jrnlmcro.xla is not loaded yet. Load it from ActiveWorkbook.Path
' SavedPath = CurDir() & "\"
' WorkbookPath = ActiveWorkbook.Path & "\"
' ChDir (WorkbookPath)
' ChDrive (WorkbookPath)
' Run ("JRNLMCRO.XLA!GotoControlPage")
' ChDrive (SavedPath)
' ChDir (SavedPath)
'End If
'The logfile name is stored in the message catalog
LogFileName = GetMsg(Lang, 9, 2)
If LogFileName = "" Then
LogFileName = "jrnllog.xls"
End If
'Skipsheets = number of non-journal worksheets in workbook. In the sample shipped, _
' Skipsheets is 4 because 1 Control sheet + 1 notes sheet + 1 journal template sheet _
' + 1 journal header template sheet = 4. The Options dialogsheet doesn't count _
' because it's a dialogsheet, not a worksheet. If you add additional sheets to _
' the workbook, add that number to Skipsheets.
Skipsheets = 4
'SkipDlgs = number of dialogsheets in jrnl workbook. In the sample shipped, _
' SkipDlgs is 1 because the only dialogsheet is the Options dialogsheet. If you add _
' additional dialogsheets to the jrnl workbook, add that number to SkipDlgs.
SkipDlgs = 1
End Sub
Function StringBetween(SourceString As String, BeforeString As String, AfterString As String, Compare As Integer) As String
Dim BeforeStringPos As Integer
Dim AfterStringPos As Integer
Dim StringBetweenPos As Integer
StringBetween = ""
BeforeStringPos = InStr(1, SourceString, BeforeString, Compare)
If BeforeStringPos > 0 Then
StringBetweenPos = BeforeStringPos + Len(BeforeString)
AfterStringPos = InStr(StringBetweenPos, SourceString, AfterString, Compare)
If AfterStringPos > StringBetweenPos Then
StringBetween = Mid(SourceString, StringBetweenPos, AfterStringPos - StringBetweenPos)
End If
End If
End Function
'This macro will add a new journal entry sheet and header
Sub JrnlSheet_New()
Dim NewSheet, Title, Mesg As String
Dim WK As Variant
Dim iMsg As Integer
GetSheet
Workbooks(CurWrkBook).Unprotect
Title = GetMsg(Lang, 41, 21)
Mesg = GetMsg(Lang, 41, 29)
NewSheet = Trim(InputBox(Mesg, Title))
Application.ScreenUpdating = False
If NewSheet <> "" Then
'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
If Len(NewSheet) > 29 Then
'show error
iMsg = MsgBox(GetMsg(Lang, 91, 13), vbExclamation)
'exit
Exit Sub
End If
Workbooks(CurWrkBook).Worksheets("Template").Copy after:=Sheets("Template_H")
ActiveSheet.Name = NewSheet
Workbooks(CurWrkBook).Worksheets("Template_H").Copy after:=Sheets(NewSheet)
ActiveSheet.Name = NewSheet & "_H"
Worksheets(NewSheet).Activate
Else
GotoControlPage
End If
Workbooks(CurWrkBook).Protect
End Sub
'========================================
' Select a Journal Sheet and activate it
'========================================
Public Sub JrnlSheet_Select()
Dim SheetName As String
Dim SheetList() As Variant
Dim i As Integer
'Get the number of sheets
GetSheet
SheetName = ""
If ListArray(SheetList) Then
With Form_SelectSheet
.Caption = GetMsg(Lang, 41, 22)
.ButtonOK.Caption = GetMsg(Lang, 12, 1)
.ListBox1.Clear
.ListBox1.List = SheetList
.Height = .Height - .Target.Height - .OnlineFrame.Height - 15
Form_SelectSheet.Show
If .ListBox1.ListIndex >= 0 Then
SheetName = .ListBox1.List(.ListBox1.ListIndex)
End If
End With
Unload Form_SelectSheet
Else
'Msg: "No journal entry sheets exist. Press New to insert a new sheet."
Title = GetMsg(Lang, 90, 1)
Message = GetMsg(Lang, 94, 6)
i = MsgBox(Message, 64, Title)
End If
If SheetName = "" Then
GotoControlPage
Else
Workbooks(CurWrkBook).Worksheets(SheetName).Activate
End If
End Sub
'=======================
' Delete Journal Sheets
'=======================
Sub JrnlSheet_Delete()
Dim List1 As Variant
Dim SelSheet, SelHdrSheet As String
Dim SheetList() As Variant
Dim i As Integer
GetSheet
'Application.ScreenUpdating = False
Workbooks(CurWrkBook).Unprotect
If ListArray(SheetList) Then
With Form_SelectSheet
.Caption = GetMsg(Lang, 41, 23)
.ButtonOK.Caption = GetMsg(Lang, 41, 5)
.ButtonAll.Visible = True
.ButtonNone.Visible = True
.ListBox1.Clear
.ListBox1.List = SheetList
.Height = .Height - .Target.Height - .OnlineFrame.Height - 15
End With
Form_SelectSheet.Show
Set List1 = Form_SelectSheet.ListBox1
For i = 1 To List1.ListCount
If List1.Selected(i - 1) Then
SelSheet = List1.List(i - 1)
SelHdrSheet = SelSheet & "_H"
Application.DisplayAlerts = False
Workbooks(CurWrkBook).Worksheets(SelSheet).Delete
Workbooks(CurWrkBook).Worksheets(SelHdrSheet).Delete
Application.DisplayAlerts = True
End If
Next
Unload Form_SelectSheet
Else
'Msg: "No journal entry sheets exist for deletion."
Title = GetMsg(Lang, 90, 1)
Message = GetMsg(Lang, 94, 7)
i = MsgBox(Message, 48, Title)
End If
GotoControlPage
End Sub
Sub JrnlSheet_Copy()
Dim SelSheet, SelHdrSheet, NameError As String
Dim NewSheet, NewHdrSheet As String
Dim SheetList(), WrkSheet As Variant
Dim i, SeqNum, NewSeq, NewSeq1, tSeq, jid, BeginRow, EndRow As Integer
Dim R As Range
Dim Firstseq As Boolean
'Get the number of sheets
GetSheet
NewSheet = ""
If ListArray(SheetList) Then
With Form_SelectSheet
.Caption = GetMsg(Lang, 41, 24)
.ButtonOK.Caption = GetMsg(Lang, 41, 6)
.Label2.Visible = True
.Target.Visible = True
.Label2.Caption = GetMsg(Lang, 41, 29)
.ListBox1.Clear
.ListBox1.List = SheetList
.Height = .Height - .OnlineFrame.Height - 5
End With
Form_SelectSheet.Show
If Form_SelectSheet.ListBox1.ListIndex >= 0 Then
SelSheet = Form_SelectSheet.ListBox1.List(Form_SelectSheet.ListBox1.ListIndex)
SelHdrSheet = SelSheet & "_H"
NewSheet = Form_SelectSheet.Target
NameError = "N"
For Each WrkSheet In Workbooks(CurWrkBook).Worksheets
If StrComp(NewSheet, WrkSheet.Name, 1) = 0 Then
'name already exists
NameError = "Y"
End If
Next
If NameError = "N" Then
Application.ScreenUpdating = False
Application.DisplayAlerts = False
NewHdrSheet = NewSheet & "_H"
Workbooks(CurWrkBook).Unprotect
Workbooks(CurWrkBook).Worksheets(SelSheet).Copy after:=Workbooks(CurWrkBook).Sheets("Template_H")
ActiveSheet.Name = NewSheet
Workbooks(CurWrkBook).Worksheets(SelHdrSheet).Copy after:=Workbooks(CurWrkBook).Sheets(NewSheet)
ActiveSheet.Name = NewHdrSheet
Worksheets(NewHdrSheet).Unprotect
Worksheets(NewSheet).Unprotect
'Change System IDs
Worksheets(NewHdrSheet).Activate
i = 3
SeqNum = Val(Cells(i, HdrCtrlCOL + 1).Value)
While SeqNum > 0
NewSeq = Val(Options("NextSystemID"))
If NewSeq = 9999 Then
Call Save_Option("NextSystemID", 1001)
Else
Call Save_Option("NextSystemID", NewSeq + 1)
End If
Cells(i, HdrCtrlCOL + 1).Value = NewSeq
If Firstseq = False Then
NewSeq1 = NewSeq
Firstseq = True
End If
Cells(i, LastHdrCOL - 3).Value = "" 'DocSeqNum
Cells(i, HdrCtrlCOL + 3).Value = "NEXT"
Worksheets(NewSheet).Activate
With Cells(2, 2).EntireColumn
Set R = .Find(SeqNum, , , xlWhole, xlByRows)
If Not R Is Nothing Then
'lines exist for the header
BeginRow = R.Row
Do
tSeq = Cells(R.Row, 2).Value
If tSeq = SeqNum Then
EndRow = R.Row
Set R = .FindNext(R)
End If
jid = Cells(R.Row, 5).Value
If jid <> "" Then
Cells(R.Row, 5) = "NEXT"
End If
Loop While Not R Is Nothing And tSeq = SeqNum And R.Row <> BeginRow
Range(Cells(BeginRow, 2), Cells(EndRow, 2)).Value = NewSeq
End If
End With
Worksheets(NewHdrSheet).Activate
i = i + 1
SeqNum = Val(Cells(i, HdrCtrlCOL + 1).Value)
Wend
Worksheets(NewSheet).Activate
'Range(Cells(4, 8), Cells(4, 12)).Value = Null
Cells(4, 8).Value = NewSeq1
Cells(4, 10).Value = "NEXT"
Cells(4, 2).Select
Worksheets(NewSheet).Protect
Workbooks(CurWrkBook).Protect
Application.ScreenUpdating = True
Else
'Msg: "Sheet name already exists."
Title = GetMsg(Lang, 90, 1)
Message = GetMsg(Lang, 94, 4)
i = MsgBox(Message, 48, Title)
Workbooks(CurWrkBook).Worksheets("Control").Activate
End If
Application.DisplayAlerts = True
End If
Unload Form_SelectSheet
Else
'Msg: "No journal entry sheets exist. Press New to insert a new sheet."
Title = GetMsg(Lang, 90, 1)
Message = GetMsg(Lang, 94, 6)
i = MsgBox(Message, 64, Title)
End If
Workbooks(CurWrkBook).Protect
'Change the Import Status automatically
Auto_Change_ImportStatus
End Sub
Sub FormatJournal(ByVal SelSheet)
Dim BotLine As Long
Dim Cel As Variant
Dim LastCol As Integer
Dim SkipCol As Integer
Dim iRow As Integer
Workbooks(CurWrkBook).Worksheets(SelSheet).Unprotect
Workbooks(CurWrkBook).Worksheets(SelSheet).Activate
BotLine = Range("BottomLine").Row
BotLine = BotLine - 1
FirstJLine = ActiveSheet.Range("InsertLine").Row
FirstJLine = FirstJLine + 1
LastCol = ActiveSheet.Range("LastLineCol").Column
SkipCol = ActiveSheet.Range("DescrCol").Column
ActiveSheet.Range(Cells(FirstJLine, 9), Cells(BotLine, LastCol)).Select
For Each Cel In Selection.Cells
If Cel.Column <> SkipCol Then
' BUG 14150575
If Cel.Value <> "" Then
Cel.Value = UCase(Cel.Value)
End If
End If
If Not IsNumeric(Cel.Value) Then
Cel.Value = Trim(Cel.Value)
If Cel.Value = "" Then
Cel.Value = Empty
End If
End If
Next
'For iRow = 12 To BotLine
' ActiveSheet.Cells(iRow, 9).Value = UCase(ActiveSheet.Cells(iRow, 9).Value)
' ActiveSheet.Cells(iRow, 10).Value = UCase(ActiveSheet.Cells(iRow, 10).Value)
' ActiveSheet.Cells(iRow, 14).Value = UCase(ActiveSheet.Cells(iRow, 14).Value)
'Next
ActiveSheet.Range(Cells(12, 25), Cells(BotLine, 46)).Select
For Each Cel In Selection.Cells
If Cel.Column <> SkipCol Then
If Cel.Value <> "" Then
Cel.Value = UCase(Cel.Value)
End If
End If
Next
End Sub
Function ListArray(SheetList() As Variant) As Boolean
Dim SheetCount As Integer
Dim ArraySize As Integer
Dim Counter As Integer
Dim CurCount As Integer
'Get the number of sheets
SheetCount = ActiveWorkbook.Worksheets.Count
If SheetCount > Skipsheets Then
'Now set the array size. The number we're interested in is the total less Skipsheets (set in GetSheet).
'Then divide this number by 2 because we don't want to include the header sheets in the listbox.
ArraySize = (SheetCount - Skipsheets) / 2
ReDim SheetList(ArraySize) As Variant
'Counter = number of worksheets to skip + number of dialogsheets to skip.
Counter = Skipsheets + SkipDlgs
CurCount = 1
Do
SheetList(CurCount) = Workbooks(CurWrkBook).Worksheets(Counter).Name
'skip the next sheet because it's the associated header sheet
Counter = Counter + 2
CurCount = CurCount + 1
Loop Until Counter >= SheetCount
ListArray = True
Else
ListArray = False
End If
End Function
Public Sub Set_Options()
Dim LangArray() As Variant
Dim i As Integer
Dim tmp As String
Application.ScreenUpdating = False
GetSheet
'Set LstBox = Dlgsheet.ListBoxes("LangPrefList")
'With Dlgsheet.ListBoxes("LangPrefList")
' .List = Array("English")
' .MultiSelect = 1
'End With
'Build_LangArray (haha)
ReDim LangArray(1)
LangArray(1) = "English"
With Form_Options
.Caption = GetMsg(Lang, 42, 1)
.HeaderDefaults_Box.Caption = GetMsg(Lang, 42, 2)
.General_Box.Caption = GetMsg(Lang, 42, 3)
.MessageLog_Box.Caption = GetMsg(Lang, 42, 4)
.DocSeq_Box.Caption = GetMsg(Lang, 42, 5)
.ImportControl_Box.Caption = GetMsg(Lang, 42, 6)
.ImportStatus_Box.Caption = GetMsg(Lang, 42, 7)
.ButtonOK.Caption = GetMsg(Lang, 12, 1)
.ButtonCancel.Caption = GetMsg(Lang, 12, 2)
.Label11.Caption = GetMsg(Lang, 42, 11)
.Label12.Caption = GetMsg(Lang, 42, 12)
.Label13.Caption = GetMsg(Lang, 42, 13)
.Label14.Caption = GetMsg(Lang, 42, 14)
.Label15.Caption = GetMsg(Lang, 42, 15)
.MultiBook_Enable.Caption = GetMsg(Lang, 42, 16)
.Def_AutoGen.Caption = GetMsg(Lang, 42, 17)
.Label21.Caption = GetMsg(Lang, 42, 18)
.Log_ErrorsOnly.Caption = GetMsg(Lang, 42, 19)
.Log_AllMessage.Caption = GetMsg(Lang, 42, 20)
.Display_Mesg.Caption = GetMsg(Lang, 42, 21)
.DocSeq_Enable.Caption = GetMsg(Lang, 42, 22)
.Label31.Caption = GetMsg(Lang, 42, 23)
.Label41.Caption = GetMsg(Lang, 42, 24)
.Label42.Caption = GetMsg(Lang, 42, 15)
.ChangeStatus_YES.Caption = GetMsg(Lang, 42, 25)
.ChangeStatus_NO.Caption = GetMsg(Lang, 42, 26)
.Unicode.Caption = GetMsg(Lang, 42, 27)
.Def_BusUnit.Value = Options("Def_BusUnit")
.Def_Date.Value = Options("Def_Date")
.Def_LedGrp.Value = Options("Def_LedGrp")
.Def_Source.Value = Options("Def_Source")
.Def_UserID.Value = Options("Def_UserID")
.MultiBook_Enable = IIf(Options("Multibook") = "Y", True, False)
.Def_AutoGen = IIf(Options("Def_AutoGen") = "Y", True, False)
.Unicode = IIf(Options("Unicode") = "Y", True, False)
.Display_Mesg = IIf(Options("DisplayMessage") = "Y", True, False)
.DocSeq_Enable = IIf(Options("DocSeq") = "Y", True, False)
.Def_DocType = Options("Def_DocType")
.Import_URL = Options("Import_URL")
.Import_UserID = Options("Import_UserID")
.DuplicateOption = IIf(Options("DuplicateOption") = "S", True, False)
.ErrorOption = IIf(Options("ErrorOption") = "S", True, False)
.DecPosition = IIf(Options("DecPosition") = "Y", True, False)
If Options("LogMessage") = "E" Then
.Log_ErrorsOnly = True
Else
.Log_AllMessage = True
End If
If Options("ChangeStatus") = "C" Then
.ChangeStatus_YES = True
Else
.ChangeStatus_NO = True
End If
.LangList.List = LangArray
.LangList.MultiSelect = fmMultiSelectSingle
For i = 1 To .LangList.ListCount
If .LangList.List(i - 1) = Options("Language") Then
.LangList.Selected(i - 1) = True
End If
Next
Application.ScreenUpdating = True
.Show
Application.ScreenUpdating = False
If Right(.Import_URL, 1) <> "/" Then
.Import_URL.Value = .Import_URL.Value & "/"
End If
Call Save_Option("Def_BusUnit", .Def_BusUnit.Value)
Call Save_Option("Def_Date", .Def_Date.Value)
Call Save_Option("Def_LedGrp", .Def_LedGrp.Value)
Call Save_Option("Def_Source", .Def_Source.Value)
Call Save_Option("Def_UserID", .Def_UserID.Value)
Call Save_Option("Multibook", IIf(.MultiBook_Enable, "Y", "N"))
Call Save_Option("Def_AutoGen", IIf(.Def_AutoGen, "Y", "N"))
Call Save_Option("Unicode", IIf(.Unicode, "Y", "N"))
Call Save_Option("LogMessage", IIf(.Log_ErrorsOnly, "E", "B"))
Call Save_Option("DisplayMessage", IIf(.Display_Mesg, "Y", "N"))
Call Save_Option("DocSeq", IIf(.DocSeq_Enable, "Y", "N"))
Call Save_Option("Def_DocType", .Def_DocType)
Call Save_Option("Import_URL", .Import_URL)
Call Save_Option("Import_UserID", .Import_UserID)
Call Save_Option("ChangeStatus", IIf(.ChangeStatus_YES, "C", "N"))
Call Save_Option("DuplicateOption", IIf(.DuplicateOption, "S", "U"))
Call Save_Option("ErrorOption", IIf(.ErrorOption, "S", "A"))
Call Save_Option("DecPosition", IIf(.DecPosition, "Y", "N"))
If .LangList.ListIndex >= 0 Then
If Options("Language") <> .LangList.List(.LangList.ListIndex) Then
Set_Homepage_Labels
Call Save_Option("Language", .LangList.List(.LangList.ListIndex))
End If
End If
End With
Unload Form_Options
'If LstBox.ListIndex <> 0 Then
' With Dlgsheet.EditBoxes("OprLangPref")
' .Text = LstBox.List(LstBox.Value)
' .Enabled = False
' End With
'End If
End Sub
'===============================================================================
' Entry Function for Online and Batch Journal Import
' HowMany : Indicates how many journals sheets to be processed
' (SELECTED, THIS ONE)
' Mode : Indicates Onine import (XML link) or Batch import (flat file)
' (IMPORT, FILE)
'===============================================================================
Public Sub Import_Journals(HowMany, mode As String)
Dim SelSheet, SelList As Variant
Dim List1 As Variant
Dim StatusBarOptn As Boolean
Dim StatusBarText As String
Dim Counter2 As Integer
Dim Outfile As Integer
Dim IndexFile As Integer
Dim SheetList() As Variant
Dim i As Integer
Dim UsrID, Pwd As String
Dim Byte1 As Byte
Dim tmpStr, BaseFile, FileExt As String
Dim posDot, posSlash As Integer
GetSheet
StatusBarOptn = Application.DisplayStatusBar
StatusBarText = Application.StatusBar
Select Case HowMany
Case "THIS ONE"
Set SelSheet = ActiveSheet
With Form_ImportNow
.Caption = GetMsg(Lang, 41, 25)
.ButtonOK.Caption = GetMsg(Lang, 12, 1)
.ButtonCancel.Caption = GetMsg(Lang, 12, 2)
.Import_URL.Caption = Options("Import_URL")
.Import_UserID.Value = Options("Import_UserID")
.Label3.Caption = GetMsg(Lang, 12, 30) 'UserID
.Label4.Caption = GetMsg(Lang, 12, 31) 'Password
End With
Form_ImportNow.Show
Application.ScreenUpdating = False
If Form_ImportNow.Import_UserID <> "" Then
Call Import_Sheet(SelSheet.Name, mode, Outfile, Form_ImportNow.Import_UserID, Form_ImportNow.Import_Pwd)
End If
Unload Form_ImportNow
SelSheet.Activate
Cells(11, 8).Select
Case "SELECTED"
If ListArray(SheetList) Then
With Form_SelectSheet
If mode = "IMPORT" Then
.Caption = GetMsg(Lang, 41, 25)
.ButtonAll.Caption = GetMsg(Lang, 41, 7)
.ButtonAll.Visible = True
.ButtonNone.Visible = True
.OnlineFrame.Visible = True
.Height = .Height - .Target.Height - 10
.OnlineFrame.Top = .OnlineFrame.Top - .Target.Height - 10
.OnlineFrame.Caption = GetMsg(Lang, 12, 29)
.Import_URL.Caption = Options("Import_URL")
.Import_UserID.Value = Options("Import_UserID")
.Label3.Caption = GetMsg(Lang, 12, 30) 'UserID
.Label4.Caption = GetMsg(Lang, 12, 31) 'Password
Else
.Caption = GetMsg(Lang, 41, 26)
.ButtonAll.Caption = GetMsg(Lang, 41, 8)
.ButtonAll.Visible = True
.ButtonNone.Visible = True
.Label2.Visible = True
.Target.Visible = True
.WriteIndex.Visible = True
.Label2.Caption = GetMsg(Lang, 41, 28)
.Target = Application.DefaultFilePath & "\" & Mid(CurWrkBook, 1, InStr(1, CurWrkBook, ".", 1)) & "txt"
.Height = .Height - .OnlineFrame.Height - 5
End If
.ListBox1.Clear
.ListBox1.List = SheetList
End With
Form_SelectSheet.Show
Set List1 = Form_SelectSheet.ListBox1
Application.ScreenUpdating = False
For i = 1 To List1.ListCount
If List1.Selected(i - 1) Then
If mode <> "IMPORT" Then
If Form_SelectSheet.WriteIndex = True Then
If IndexFile = 0 Then
posDot = InStrRev(Form_SelectSheet.Target, ".")
posSlash = InStrRev(Form_SelectSheet.Target, "\")
If posDot > 0 And posSlash > 0 And posSlash > posDot Then
posDot = 0
End If
If posDot = 0 Then
BaseFile = Form_SelectSheet.Target & "_"
FileExt = ".txt"
Else
BaseFile = Left(Form_SelectSheet.Target, posDot - 1) & "_"
FileExt = Mid(Form_SelectSheet.Target, posDot)
End If
IndexFile = FreeFile()
Open (BaseFile & "index" & FileExt) For Output As IndexFile
Else
Close #Outfile
End If
tmpStr = BaseFile & List1.List(i - 1) & FileExt
Print #IndexFile, tmpStr
Outfile = FreeFile()
Open tmpStr For Output As Outfile
Else
If Outfile = 0 Then
Outfile = FreeFile()
Open Form_SelectSheet.Target For Output As Outfile
End If
End If
End If
Call Import_Sheet(List1.List(i - 1), mode, Outfile, Form_SelectSheet.Import_UserID, Form_SelectSheet.Import_Pwd)
End If
Next
Unload Form_SelectSheet
If mode <> "IMPORT" And Outfile <> 0 Then
If IndexFile <> 0 Then
Close #IndexFile
End If
Close #Outfile
End If
Else
'Msg: "No journal sheet for import"
Title = GetMsg(Lang, 95, 1)
Message = GetMsg(Lang, 95, 2)
i = MsgBox(Message, 64, Title)
End If
End Select
Application.StatusBar = StatusBarText
Application.StatusBar = GetMsg(Lang, 1, 3)
Application.DisplayStatusBar = StatusBarOptn
If HowMany <> "THIS ONE" Then
Call GotoControlPage
End If
End Sub
'=========================================
' Online import the given journal sheet
'=========================================
'Private Sub Import_Sheet(LineSheet As String, mode As String, OutFile As Integer, UsrID, Pwd As String)
Private Sub Import_Sheet(LineSheet As String, mode As String, Outfile As Integer, UsrID As String, Pwd As String)
Dim HdrSheet As String
Dim strXML As String
Dim strURL As String
Dim strResponse As String
Dim FirstRowNum As Integer
Dim LastRowNum As Integer
Dim ReturnCD As Long
Dim i As Long
' ICE: 688200002: AH
Dim iGenerateXML As Integer
HdrSheet = LineSheet & "_H"
ActiveWorkbook.Worksheets(LineSheet).Activate
Application.DisplayStatusBar = True
Application.StatusBar = GetMsg(Lang, 1, 1)
FormatJournal (LineSheet)
'CheckHdrs
'Import the Journal sheet
'NvsError = ImportSheet(ActiveSheet)
If mode <> "IMPORT" Then
' ICE: 688200002: AH: Adds a parameter to store status code
Call Generate_XML(strXML, LineSheet, 0, iGenerateXML)
' ICE: 688200002: AH if iGenerateXML is 0, i.e. no error
If iGenerateXML = 0 Then
Print #Outfile, strXML
End If
Else
strXML = "<?xml version=""1.0""?>" & vbCrLf
strXML = strXML & "<Postreq>" & vbCrLf
strXML = strXML & "<PROCESS_OPTION>" & vbCrLf
strXML = strXML & " <DUPLICATE_OPTION>" & Options("DuplicateOption") & "</DUPLICATE_OPTION>" & vbCrLf
strXML = strXML & " <ERROR_OPTION>" & Options("ErrorOption") & "</ERROR_OPTION>" & vbCrLf
strXML = strXML & " <DECIMAL_POSITION>" & Options("DecPosition") & "</DECIMAL_POSITION>" & vbCrLf
strXML = strXML & " <DEFAULT_USERID>" & UsrID & "</DEFAULT_USERID>" & vbCrLf
strXML = strXML & "</PROCESS_OPTION>" & vbCrLf
' ICE: 688200002: AH: Adds a parameter to store status code
Call Generate_XML(strXML, LineSheet, 2, iGenerateXML)
' ICE: 688200002: AH if iGenerateXML is 0, i.e. no error
If iGenerateXML = 0 Then
strXML = strXML & "</Postreq>" & vbCrLf
'http://<server>/xmllink/ps/Excel_Journal_Import?userid=<user-id>&pwd=<password>
Rem AG 3323966 Posting directly so do not append service name
Rem and append a & instead of ? before userid and pwd
'strURL = Options("Import_URL") & "Excel_Journal_Import"
strURL = Options("Import_URL")
strURL = Replace(strURL, "xmllink", "psc")
strURL = strURL & "EMPLOYEE/ERP/s/WEBLIB_GL.JOURNAL_ID.FieldFormula.Iscript_Excel_Journal/?postDataBin=y&disconnect=y"
'strURL = strURL & "&userid=" & UsrID & "&pwd=" & Pwd
ReturnCD = sendXML(strXML, strResponse, strURL, LineSheet, UsrID, Pwd)
Select Case ReturnCD
Case 0
' Import was successful
Select Case Options("ChangeStatus")
Case "N"
'do nothing
ActiveSheet.Protect
Case "C"
'Change Header status to IMPORTED
ActiveWorkbook.Worksheets(HdrSheet).Activate
ActiveSheet.Unprotect
LastRowNum = ActiveSheet.Columns(2).Find("", , xlFormulas, xlWhole, xlByRows).Row - 1
ActiveSheet.Range(Cells(3, 2), Cells(LastRowNum, 2)).Select
Selection.Value = "U"
ActiveSheet.Protect
'Change Line status to IMPORTED
ActiveWorkbook.Worksheets(LineSheet).Activate
ActiveSheet.Unprotect
FirstRowNum = ActiveSheet.Range("InsertLine").Row + 1
LastRowNum = ActiveSheet.Range("BottomLine").Row - 1
ActiveSheet.Range(Cells(FirstRowNum, 3), Cells(LastRowNum, 3)).Select
Selection.Value = "U"
ActiveSheet.Protect
End Select
Case -101
'Unknow generic error
Case -102
'Error loading the source XML document
Case -103
'Error loading response XML document
Case -104
'Application Error message found
Case Else
'Import error: unable to import journal headers associated with sheet
Title = GetMsg(Lang, 95, 1)
Message = GetMsg(Lang, 95, 4)
Message = Message & " '" & LineSheet
If Options("DisplayMessage") = "E" Then
i = MsgBox(Message, 16, Title)
End If
End Select
If ReturnCD = 0 Then
If Options("LogMessage") = "B" Then
Call AddLogEntry(LogFileName, LineSheet, "Success", strResponse)
End If
If Options("DisplayMessage") = "Y" Then
i = MsgBox(strResponse, vbOKOnly, "Import OK - Sheet " & LineSheet)
End If
Else
Call AddLogEntry(LogFileName, LineSheet, "Import failed", strResponse)
If Options("DisplayMessage") = "Y" Then
i = MsgBox(strResponse, vbExclamation + vbOKOnly, "Import Failed - Sheet " & LineSheet)
End If
End If
' ICE: 688200002: AH
End If
End If
End Sub
'===============================================
' Create XML elements from given rage of data
' ICE: 688200002: Add checking on OPEN_ITEM_KEY to make sure there is no space in the middle of the value
'===============================================
Private Function Row2XML(Sel As Range, indent As Integer, Optional HdrSheet As String) As String
Dim Cel As Range
Dim FldValue As String
Dim DecChar As String
Dim i As Integer
Dim XML As String
Dim sColName As String
Dim iMsg As Integer
DecChar = Application.International(xlDecimalSeparator)
XML = ""
For Each Cel In Sel
If IsNumeric(Cel.Value) And Cel.NumberFormat <> "@" Then
If Cel.Value = 0 Then
FldValue = ""
Else
Rem Bug13784334-UNEXPECTED WARNING MESSAGE WHEN CHECKING THE DECIMALS IN JRNL1-XLS-WINDOW
FldValue = Cel.Value
Rem FldValue = CDec(Cel.Value)
Rem If DecChar <> "." And InStr(FldValue, DecChar) > 0 Then
Rem Mid(FldValue, InStr(FldValue, DecChar), 1) = "."
Rem End If
End If
Else
If IsDate(Cel.Value) And Cel.NumberFormat <> "@" Then
FldValue = Format$(Cel.Value, "yyyymmdd")
Else
FldValue = Encode_XMLchar(Trim$(Cel.Value))
End If
End If
' ICE: 688200002: ah BEGIN
sColName = UCase$(Cells(1, Cel.Column).Value)
If sColName = "OPEN_ITEM_KEY" And Len(FldValue) > 0 Then
'Make sure FldValue does not have space in the middle
If ImbeddedSpace(FldValue) Then
iMsg = MsgBox(GetMsg(Lang, 91, 12), vbExclamation, HdrSheet)
Err.Raise (-999)
End If
End If
' ICE: 688200002: ah END
If Len(FldValue) > 0 Then
XML = XML & Space$(indent) & "<" & UCase$(Cells(1, Cel.Column).Value) & ">"
XML = XML & FldValue
XML = XML & "</" & UCase$(Cells(1, Cel.Column).Value) & ">" & vbCrLf
End If
Next
Row2XML = XML
End Function
'=============================================
' Generate XML string for the current sheet
'=============================================
'ICE: 688200002 Add iStatus for status indicator
Private Sub Generate_XML(XMLtext As String, LineSheet As String, indent As Integer, iStatus As Integer)
Dim DataRange As Range
Dim i As Integer
Dim HdrSeqNum As Integer
Dim FirstRowNum As Integer
Dim ThisRow As Range
Dim HdrSheet As String
Dim HeaderArray() As Variant
Dim tempXML As String 'use smaller chunk of XML string for performance
' ICE: 688200002: ah BEGIN set iStatus = 0 to indicate there is no error first
iStatus = 0
On Error GoTo doError
' ICE: 688200002: ah END
HdrSheet = LineSheet & "_H"
ActiveWorkbook.Worksheets(HdrSheet).Activate
If Build_HdrArray(HdrSheet, HeaderArray) Then
For i = LBound(HeaderArray) To UBound(HeaderArray)
If Cells(i + 2, 2) = "C" Then
Set DataRange = ActiveSheet.Range(Cells(i + 2, HdrCtrlCOL + 1), Cells(i + 2, LastHdrCOL))
XMLtext = XMLtext & Space(indent) & "<JRNL_HDR_IMP>" & vbCrLf
XMLtext = XMLtext & Row2XML(DataRange, indent + 2)
HdrSeqNum = Cells(i + 2, 3).Value
ActiveWorkbook.Worksheets(LineSheet).Activate
With Range(Cells(2, 2), Cells(Range("BottomLine").Row, 2))
Set ThisRow = .Find(HdrSeqNum, , , xlWhole, xlByRows)
If Not ThisRow Is Nothing Then
FirstRowNum = ThisRow.Row
tempXML = ""
Do
If Cells(ThisRow.Row, 3) = "C" Then
Set DataRange = Range(Cells(ThisRow.Row, 8), Cells(ThisRow.Row, Range("LastLineCol").Column))
tempXML = tempXML & Space(indent + 2) & "<JRNL_LN_IMP>" & vbCrLf
' ICE: 688200002:
tempXML = tempXML & Row2XML(DataRange, indent + 4, "Sheet: " & LineSheet)
tempXML = tempXML & Space(indent + 2) & "</JRNL_LN_IMP>" & vbCrLf
If Len(tempXML) > 10000 Then
XMLtext = XMLtext & tempXML
tempXML = ""
End If
End If
Set ThisRow = .FindNext(ThisRow)
Loop While Not ThisRow Is Nothing And ThisRow.Row <> FirstRowNum
XMLtext = XMLtext & tempXML
tempXML = ""
End If
End With
XMLtext = XMLtext & Space(indent) & "</JRNL_HDR_IMP>" & vbCrLf
ActiveWorkbook.Worksheets(HdrSheet).Activate
End If
Next i
End If
' ICE: 688200002: ah BEGIN
doError:
If Err.Number <> 0 Then
iStatus = Err.Number
End If
' ICE: 688200002: ah END
End Sub
'======================================
' Post and Send journal over the web
'======================================
'Private Function sendXML(strXML, strResponse, strURL, LineSheet, UsrID, Pwd As String) As Long
Private Function sendXML(strXML, strResponse, strURL As String, LineSheet, UsrID As String, Pwd As String) As Long
Const SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS = 13056
'Dim xDoc As MSXML2.DOMDocument60
'Dim xHTTP As XMLHTTP
'Const WinHttpRequestOption_EnableHttp1_1 = 17
'Dim xHTTP As MSXML2.ServerXMLHTTP60
Dim xDoc As MSXML.DOMDocument
Dim xHTTP As MSXML.XMLHTTPRequest
'Dim xError, xImported, xUpdated As MSXML.IXMLDOMElement
'Added an additional element xWarning to handle the warnings
'Dim xError, xWarning, xImported, xUpdated As IXMLDOMNodeList
'Dim tmp, tmp2, tmp3 As String
'Dim StatusNum As Long
'Dim i, k, RowNum As Integer
'Dim Y, M, D, Dsep, Jdate, SysID, jid As String
'Dim Dorder As Integer
Dim xError, xImported, xUpdated As MSXML.IXMLDOMNodeList
Dim tmp As String
Dim StatusNum As Long
Dim i, k As Integer
On Error GoTo doError
strResponse = ""
'Set xDoc = New MSXML2.DOMDocument60
Set xDoc = New MSXML.DOMDocument
'Set xDoc = CreateObject("MSXML.DOMDocument")
xDoc.async = False
If xDoc.loadXML(strXML) Then
'Set xHTTP = New XMLHTTP
'Set xHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")
Set xHTTP = New MSXML.XMLHTTPRequest
'Set xHTTP = CreateObject("MSXML.DOMDocument")
'xHTTP.setTimeouts 0, 0, 0, 0
'xHTTP.Open "POST", strURL, False, UsrID, Pwd
'xHTTP.setRequestHeader "content-type", "application/x-www-form-urlencoded"
'xHTTP.setRequestHeader "accept", "text/xml/html"
'xHTTP.setRequestHeader "accept-charset", "utf-8, iso_8859-1"
'xHTTP.setRequestHeader "userid", UsrID
'xHTTP.setRequestHeader "pwd", Pwd
'xHTTP.setOption 2, SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS
xHTTP.Open "POST", strURL, False
xHTTP.setRequestHeader "content-type", "application/x-www-form-urlencoded"
xHTTP.setRequestHeader "accept", "text/xml/html"
xHTTP.setRequestHeader "accept-charset", "utf-8, iso_8859-1"
Rem AG 3323966 Adding the Username and Password to the header
xHTTP.setRequestHeader "userid", UsrID
xHTTP.setRequestHeader "pwd", Pwd
xHTTP.send xDoc.XML
strResponse = xHTTP.responseText
If xDoc.loadXML(strResponse) Then
Dsep = Application.International(xlDateSeparator)
Dorder = Application.International(xlDateOrder)
'xlDateOrder (0=MDY, 1=DMY, 2=YMD)
Set xError = xDoc.getElementsByTagName("error")
strResponse = ""
Set xImported = xDoc.getElementsByTagName("imported")
tmp = ""
k = 0
ActiveWorkbook.Worksheets(LineSheet).Activate
ActiveSheet.Unprotect
ActiveWorkbook.Worksheets(LineSheet & "_H").Activate
ActiveSheet.Unprotect
For i = 1 To xImported.Length
tmp2 = Decode_XMLchar(xImported.Item(i - 1).Text)
tmp3 = Mid(tmp2, InStr(tmp2, ")") - 10, 10)
Y = Left(tmp3, 4)
M = Mid(tmp3, 6, 2)
D = Right(tmp3, 2)
If Dorder = 0 Then
Jdate = M & Dsep & D & Dsep & Y
ElseIf Dorder = 1 Then
Jdate = D & Dsep & M & Dsep & Y
Else
Jdate = Y & Dsep & M & Dsep & D
End If
tmp = tmp & vbLf & " " & Replace(tmp2, tmp3, Jdate)
k = k + 1
SysID = RTrim(Left(tmp2, InStr(tmp2, "(") - 1))
tmp3 = LTrim(Mid(tmp2, InStr(tmp2, ",") + 1))
jid = RTrim(Left(tmp3, InStr(tmp3, ",") - 1))
RowNum = ActiveSheet.Columns(3).Find(SysID, , xlFormulas, xlWhole, xlByRows).Row
If Cells(RowNum, 5) = "NEXT" Then
Cells(RowNum, 5) = jid
ActiveWorkbook.Worksheets(LineSheet).Activate
If Cells(4, 8) = Val(SysID) And Cells(4, 10) = "NEXT" Then
Cells(4, 10) = jid
End If
RowNum = ActiveSheet.Columns(2).Find(SysID, , xlFormulas, xlWhole, xlByRows).Row
While Cells(RowNum, 2) = Val(SysID)
If Cells(RowNum, 5) = "NEXT" Then
Cells(RowNum, 5) = jid
End If
RowNum = RowNum + 1
Wend
ActiveWorkbook.Worksheets(LineSheet & "_H").Activate
End If
Next
strResponse = GetMsg(Lang, 80, 2, str(k)) & tmp
ActiveWorkbook.Worksheets(LineSheet & "_H").Activate
ActiveSheet.Protect
ActiveWorkbook.Worksheets(LineSheet).Activate
ActiveSheet.Protect
Set xUpdated = xDoc.getElementsByTagName("updated")
tmp = ""
k = 0
For i = 1 To xUpdated.Length
tmp2 = Decode_XMLchar(xUpdated.Item(i - 1).Text)
tmp3 = Mid(tmp2, InStr(tmp2, ")") - 10, 10)
Y = Left(tmp3, 4)
M = Mid(tmp3, 6, 2)
D = Right(tmp3, 2)
If Dorder = 0 Then
Jdate = M & Dsep & D & Dsep & Y
ElseIf Dorder = 1 Then
Jdate = D & Dsep & M & Dsep & Y
Else
Jdate = Y & Dsep & M & Dsep & D
End If
tmp = tmp & vbLf & " " & Replace(tmp2, tmp3, Jdate)
k = k + 1
Next
If k > 0 Then
strResponse = strResponse & vbLf & GetMsg(Lang, 80, 3, str(k)) & tmp
End If
'Warnings are processed in this loop
Set xWarning = xDoc.getElementsByTagName("warning")
i = 0
tmp = ""
For i = 1 To xWarning.Length
If i > 1 Then
tmp = tmp & vbLf
End If
tmp = tmp & Decode_XMLchar(xWarning.Item(i - 1).Text)
Next
strResponse = strResponse & vbLf & tmp
sendXML = 0
If xError.Length > 0 Then
'Application Error message found
If Options("ErrorOption") <> "S" Then
sendXML = -104
End If
i = 0
tmp = ""
For i = 1 To xError.Length
If i > 1 Then
tmp = tmp & vbLf
End If
tmp = tmp & Decode_XMLchar(xError.Item(i - 1).Text)
Next
strResponse = strResponse & vbLf & tmp
End If
Else
'Error loading response XML document
sendXML = -103
End If
Else
'Error loading the source XML document
sendXML = -102
strResponse = xDoc.parseError.reason & vbCrLf & strXML
End If
Exit Function
doError:
If Err.Number <> 0 Then
sendXML = Err.Number
strResponse = Err.Description & Options("Import_URL")
Else
'Unknown error
sendXML = -101
End If
End Function
Public Sub CustomizeCF()
Form_CustomizeCF.Show
End Sub
Private Sub genericErrorHandler(objErr As ErrObject, strProcName As String)
MsgBox prompt:="The following error occurred in procedure " & strProcName _
& ": " & vbCrLf & objErr.Description & vbCrLf & "Error #: " & objErr.Number, _
Title:="ERROR"
End Sub
'11/16/04: AH Created this for ICE 622590000
'The purpose of this function is to encode special characters contained in UID/password ! @ # $ % ^ & * ( ) - _ = + \ |[ ] {} ; : / ? . > <
Public Function URL_Encode(ByVal sOriginal As String) As String
Dim i_Len, i_Counter As Integer
Dim s_Encoded, s_Parsed As String
'figure out length of passed in string
i_Len = Len(sOriginal)
s_Encoded = ""
s_Parsed = ""
'parse each character and replace it with encoded value if it's one of the special character
For i_Counter = 1 To i_Len
'get each character out of the sOriginal string
s_Parsed = Mid(sOriginal, i_Counter, 1)
Select Case s_Parsed
Case "!"
s_Encoded = s_Encoded + "%21"
Case "@"
s_Encoded = s_Encoded + "%40"
Case "#"
s_Encoded = s_Encoded + "%23"
Case "$"
s_Encoded = s_Encoded + "%24"
Case "%"
s_Encoded = s_Encoded + "%25"
Case "^"
s_Encoded = s_Encoded + "%5E"
Case "&"
s_Encoded = s_Encoded + "%26"
Case "*"
s_Encoded = s_Encoded + "%2A"
Case "("
s_Encoded = s_Encoded + "%28"
Case ")"
s_Encoded = s_Encoded + "%29"
Case "-"
s_Encoded = s_Encoded + "%2D"
Case "_"
s_Encoded = s_Encoded + "%5F"
Case "="
s_Encoded = s_Encoded + "%3D"
Case "+"
s_Encoded = s_Encoded + "%2B"
Case "\"
s_Encoded = s_Encoded + "%5C"
Case "|"
s_Encoded = s_Encoded + "%7C"
Case "["
s_Encoded = s_Encoded + "%5B"
Case "]"
s_Encoded = s_Encoded + "%5D"
Case "{"
s_Encoded = s_Encoded + "%7B"
Case "}"
s_Encoded = s_Encoded + "%7D"
Case ";"
s_Encoded = s_Encoded + "%3B"
Case ":"
s_Encoded = s_Encoded + "%3A"
Case "/"
s_Encoded = s_Encoded + "%2F"
Case "?"
s_Encoded = s_Encoded + "%3F"
Case "."
s_Encoded = s_Encoded + "%2E"
Case ">"
s_Encoded = s_Encoded + "%3E"
Case "<"
s_Encoded = s_Encoded + "%3C"
Case Else
s_Encoded = s_Encoded + s_Parsed
End Select
Next
URL_Encode = s_Encoded
End Function