J
jbrotto
Guest
It seems each line in my excel file that I copy and paste into my app makes a single file for each document transfer. Sometimes an Inventory transfer might have one line item and other times multiple.
The Excel is in this link:
Shoppers ASN file weekly test for new program.xlsx
I would copy the column in the excel and paste to according textboxes in the app I made. Then click the add button and finally click Process.
My code
Public Class Form1
Dim SQLString, SQLString2 As String
Dim entriesDOCNUM, dates, entriesStoreNum, entriesStorName, quantities, UPCS, description As String
Dim counters, counters2, counters3 As Integer
Dim whereclaus As String
Dim fileNumber, startFileNumber As Integer
Dim inDirectory, outDirectory As String
Dim CSVtext As String = ""
Dim browserDirectory As New FolderBrowserDialog
Dim n As Integer = "0"
Dim lineData(), lineData2(), lineData3(), lineData4(), lineData5(), lineData6(), lineDate() As String
Dim biggestCount As Integer
Dim storeNameChange As Boolean = False
Dim storeName1, storeName2 As String
Dim storeNum1, storeNum2 As String
Dim POnumber As Integer
Private Sub BrowseBtn_Click(sender As Object, e As EventArgs) Handles BrowseBtn.Click
If browserDirectory.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Try
DirectoryTxtBox.Text = browserDirectory.SelectedPath.ToString
DirectoryTxtBox.Text = DirectoryTxtBox.Text
inDirectory = DirectoryTxtBox.Text
Catch ex As Exception
End Try
Else
Exit Sub
End If
End Sub
Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles ResetBox.Click
entriesDOCNUM = ""
dates = ""
entriesStoreNum = ""
entriesStorName = ""
quantities = ""
UPCS = ""
description = ""
POnumber = 0
DateTxtBox.Text = ""
DescriptionTxtBox.Text = ""
DirectoryTxtBox.Text = ""
FileNumberStartTxtBox.Text = ""
InvtoryTransTxtBox.Text = ""
QuantityTxtBox.Text = ""
StoreNameTxtBox.Text = ""
StoreNumTxtBox.Text = ""
POtxtBox.Text = ""
UPCtxtBox.Text = ""
n = "0"
End Sub
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles AddBtn.Click
' direcory
Try
fileNumber = FileNumberStartTxtBox.Text
Catch ex As Exception
fileNumber = 5050
End Try
'get the start point to match max later on
startFileNumber = fileNumber
'entries
entriesDOCNUM = InvtoryTransTxtBox.Text
entriesStoreNum = StoreNumTxtBox.Text
entriesStorName = StoreNameTxtBox.Text
UPCS = UPCtxtBox.Text
description = DescriptionTxtBox.Text
quantities = QuantityTxtBox.Text
dates = DateTxtBox.Text
'takes to number of row
lineData = Split(entriesDOCNUM, vbNewLine)
'number of rows from invenotry transfer
counters = lineData.Count()
whereclaus = String.Join(" ", lineData)
'shows second item in array
'MessageBox.Show(lineData(1))
'entries
'takes to number of row
lineData2 = Split(entriesStoreNum, vbNewLine)
'number of rows from invenotry transfer
counters2 = lineData2.Count()
whereclaus = String.Join(" ", lineData)
'shows second item in array
'MessageBox.Show(lineData(1))
'entries
'takes to number of row
lineData3 = Split(entriesStorName, vbNewLine)
'number of rows from invenotry transfer
counters3 = lineData3.Count()
'takes to number of row upcs
lineData4 = Split(UPCS, vbNewLine)
'takes to number of row quantity
lineData5 = Split(quantities, vbNewLine)
'takes to number of row description
lineData6 = Split(description, vbNewLine)
'takes the date
lineDate = Split(dates, vbNewLine)
whereclaus = String.Join(" ", lineData)
'shows second item in array
'MessageBox.Show(lineData(1))
If counters > counters2 Then
biggestCount = counters
ElseIf counters2 > counters3 Then
biggestCount = counters2
Else
biggestCount = counters3
End If
End Sub
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles CloseBtn.Click
Me.Close()
End Sub
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles ProcessBtn.Click
If browserDirectory.SelectedPath < "" Then
Exit Sub
End If
'redlare after
POnumber = POtxtBox.Text.ToString.Trim
If POnumber.ToString.Length <> 5 Then
MsgBox("PO needs to be a 5 digit number", MsgBoxStyle.Critical, "PO Length")
Exit Sub
End If
Do Until biggestCount + startFileNumber = fileNumber Or n = biggestCount
Dim linenum As Integer = 118
storeNameChange = False
SQLString = ""
CSVtext = ""
storeName1 = ""
storeName2 = ""
SQLString = "<?xml version=""1.0"" encoding="" UTF-8""?> " & vbNewLine &
"<Tran><Form_Id>SDM856LD</Form_Id><FS_VER>5, 0, 1, 256</FS_VER><FS_UNITNUM></FS_UNITNUM><FS_LANG>E</FS_LANG><FS_FSNUM>FS2001112301287</FS_FSNUM><FS_PO_UNITNUM></FS_PO_UNITNUM><FS_Viewer><FS_Title>exAct® - Shoppers Drug Mart ASN DSD - Faxinating Solutions Inc.</FS_Title><FS_STYLE>SDM856LD\SDM856LD_01.xsl</FS_STYLE></FS_Viewer><LINENUM>0000000127</LINENUM><STATUS>Y</STATUS><BSN02>" &
lineData
&
"</BSN02><VENDOR>54324</VENDOR><CONF>Y</CONF><CreationFlag>N</CreationFlag><Shipment><LINENUM>0000000126</LINENUM><STATUS>Y</STATUS><DTM02_067>" &
lineDate & "</DTM02_067><N102_SF>163453 CANADA INC</N102_SF><N104_SF>ER7</N104_SF><N102_ST>" & lineData3 & "</N102_ST><N104_ST>" & lineData2 &
"</N104_ST><DerivedFlag>N</DerivedFlag><OrderPOSource></OrderPOSource><Order><LINENUM>0000000125</LINENUM><STATUS>Y</STATUS><PRF01>" &
POnumber.ToString() & "</PRF01>"
Try
' Do Until storeName1 <> storeName2
If storeName1 = storeName2 Then
SQLString = SQLString & "<Item><LINENUM>" & "0000000" & linenum & "</LINENUM><STATUS>Y</STATUS><LIN02>UP</LIN02><LIN03>" & lineData4 & "</LIN03><SN102>" & lineData5 & "</SN102><SN103>EA</SN103><PID05>" & lineData6 & "</PID05></Item>"
linenum = linenum + 1
n = n + 1
If n >= lineData.Length Then
'Exit Do
End If
storeName1 = lineData3.ToString()
If n > 0 Then
If storeName2 = lineData3(n - 1).ToString() Then
Else
storeName2 = storeName1
End If
If biggestCount + startFileNumber = fileNumber Then
storeNameChange = True
'Exit Do
End If
Else
storeNameChange = True
End If
End If
'Loop
SQLString = SQLString & "</Order></Shipment></Tran>"
CSVtext = SQLString
'redlare
outDirectory = inDirectory.ToString & "\0000" & fileNumber.ToString() & ".xml"
'unquote top after the file is made
Dim outFile As IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(outDirectory, False, System.Text.Encoding.Default)
outFile.WriteLine(CSVtext.ToString())
outFile.Close()
fileNumber = fileNumber + 1
If fileNumber = startFileNumber + lineData.Length Then
Exit Do
End If
If n = lineData2.Length Then
Exit Sub
End If
Catch ex As Exception
End Try
Loop
End Sub
End Class
Continue reading...
The Excel is in this link:
Shoppers ASN file weekly test for new program.xlsx
I would copy the column in the excel and paste to according textboxes in the app I made. Then click the add button and finally click Process.
My code
Public Class Form1
Dim SQLString, SQLString2 As String
Dim entriesDOCNUM, dates, entriesStoreNum, entriesStorName, quantities, UPCS, description As String
Dim counters, counters2, counters3 As Integer
Dim whereclaus As String
Dim fileNumber, startFileNumber As Integer
Dim inDirectory, outDirectory As String
Dim CSVtext As String = ""
Dim browserDirectory As New FolderBrowserDialog
Dim n As Integer = "0"
Dim lineData(), lineData2(), lineData3(), lineData4(), lineData5(), lineData6(), lineDate() As String
Dim biggestCount As Integer
Dim storeNameChange As Boolean = False
Dim storeName1, storeName2 As String
Dim storeNum1, storeNum2 As String
Dim POnumber As Integer
Private Sub BrowseBtn_Click(sender As Object, e As EventArgs) Handles BrowseBtn.Click
If browserDirectory.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Try
DirectoryTxtBox.Text = browserDirectory.SelectedPath.ToString
DirectoryTxtBox.Text = DirectoryTxtBox.Text
inDirectory = DirectoryTxtBox.Text
Catch ex As Exception
End Try
Else
Exit Sub
End If
End Sub
Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles ResetBox.Click
entriesDOCNUM = ""
dates = ""
entriesStoreNum = ""
entriesStorName = ""
quantities = ""
UPCS = ""
description = ""
POnumber = 0
DateTxtBox.Text = ""
DescriptionTxtBox.Text = ""
DirectoryTxtBox.Text = ""
FileNumberStartTxtBox.Text = ""
InvtoryTransTxtBox.Text = ""
QuantityTxtBox.Text = ""
StoreNameTxtBox.Text = ""
StoreNumTxtBox.Text = ""
POtxtBox.Text = ""
UPCtxtBox.Text = ""
n = "0"
End Sub
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles AddBtn.Click
' direcory
Try
fileNumber = FileNumberStartTxtBox.Text
Catch ex As Exception
fileNumber = 5050
End Try
'get the start point to match max later on
startFileNumber = fileNumber
'entries
entriesDOCNUM = InvtoryTransTxtBox.Text
entriesStoreNum = StoreNumTxtBox.Text
entriesStorName = StoreNameTxtBox.Text
UPCS = UPCtxtBox.Text
description = DescriptionTxtBox.Text
quantities = QuantityTxtBox.Text
dates = DateTxtBox.Text
'takes to number of row
lineData = Split(entriesDOCNUM, vbNewLine)
'number of rows from invenotry transfer
counters = lineData.Count()
whereclaus = String.Join(" ", lineData)
'shows second item in array
'MessageBox.Show(lineData(1))
'entries
'takes to number of row
lineData2 = Split(entriesStoreNum, vbNewLine)
'number of rows from invenotry transfer
counters2 = lineData2.Count()
whereclaus = String.Join(" ", lineData)
'shows second item in array
'MessageBox.Show(lineData(1))
'entries
'takes to number of row
lineData3 = Split(entriesStorName, vbNewLine)
'number of rows from invenotry transfer
counters3 = lineData3.Count()
'takes to number of row upcs
lineData4 = Split(UPCS, vbNewLine)
'takes to number of row quantity
lineData5 = Split(quantities, vbNewLine)
'takes to number of row description
lineData6 = Split(description, vbNewLine)
'takes the date
lineDate = Split(dates, vbNewLine)
whereclaus = String.Join(" ", lineData)
'shows second item in array
'MessageBox.Show(lineData(1))
If counters > counters2 Then
biggestCount = counters
ElseIf counters2 > counters3 Then
biggestCount = counters2
Else
biggestCount = counters3
End If
End Sub
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles CloseBtn.Click
Me.Close()
End Sub
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles ProcessBtn.Click
If browserDirectory.SelectedPath < "" Then
Exit Sub
End If
'redlare after
POnumber = POtxtBox.Text.ToString.Trim
If POnumber.ToString.Length <> 5 Then
MsgBox("PO needs to be a 5 digit number", MsgBoxStyle.Critical, "PO Length")
Exit Sub
End If
Do Until biggestCount + startFileNumber = fileNumber Or n = biggestCount
Dim linenum As Integer = 118
storeNameChange = False
SQLString = ""
CSVtext = ""
storeName1 = ""
storeName2 = ""
SQLString = "<?xml version=""1.0"" encoding="" UTF-8""?> " & vbNewLine &
"<Tran><Form_Id>SDM856LD</Form_Id><FS_VER>5, 0, 1, 256</FS_VER><FS_UNITNUM></FS_UNITNUM><FS_LANG>E</FS_LANG><FS_FSNUM>FS2001112301287</FS_FSNUM><FS_PO_UNITNUM></FS_PO_UNITNUM><FS_Viewer><FS_Title>exAct® - Shoppers Drug Mart ASN DSD - Faxinating Solutions Inc.</FS_Title><FS_STYLE>SDM856LD\SDM856LD_01.xsl</FS_STYLE></FS_Viewer><LINENUM>0000000127</LINENUM><STATUS>Y</STATUS><BSN02>" &
lineData
&"</BSN02><VENDOR>54324</VENDOR><CONF>Y</CONF><CreationFlag>N</CreationFlag><Shipment><LINENUM>0000000126</LINENUM><STATUS>Y</STATUS><DTM02_067>" &
lineDate
& "</DTM02_067><N102_SF>163453 CANADA INC</N102_SF><N104_SF>ER7</N104_SF><N102_ST>" & lineData3 & "</N102_ST><N104_ST>" & lineData2 &"</N104_ST><DerivedFlag>N</DerivedFlag><OrderPOSource></OrderPOSource><Order><LINENUM>0000000125</LINENUM><STATUS>Y</STATUS><PRF01>" &
POnumber.ToString() & "</PRF01>"
Try
' Do Until storeName1 <> storeName2
If storeName1 = storeName2 Then
SQLString = SQLString & "<Item><LINENUM>" & "0000000" & linenum & "</LINENUM><STATUS>Y</STATUS><LIN02>UP</LIN02><LIN03>" & lineData4
& "</LIN03><SN102>" & lineData5 & "</SN102><SN103>EA</SN103><PID05>" & lineData6 & "</PID05></Item>"linenum = linenum + 1
n = n + 1
If n >= lineData.Length Then
'Exit Do
End If
storeName1 = lineData3
.ToString()If n > 0 Then
If storeName2 = lineData3(n - 1).ToString() Then
Else
storeName2 = storeName1
End If
If biggestCount + startFileNumber = fileNumber Then
storeNameChange = True
'Exit Do
End If
Else
storeNameChange = True
End If
End If
'Loop
SQLString = SQLString & "</Order></Shipment></Tran>"
CSVtext = SQLString
'redlare
outDirectory = inDirectory.ToString & "\0000" & fileNumber.ToString() & ".xml"
'unquote top after the file is made
Dim outFile As IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(outDirectory, False, System.Text.Encoding.Default)
outFile.WriteLine(CSVtext.ToString())
outFile.Close()
fileNumber = fileNumber + 1
If fileNumber = startFileNumber + lineData.Length Then
Exit Do
End If
If n = lineData2.Length Then
Exit Sub
End If
Catch ex As Exception
End Try
Loop
End Sub
End Class
Continue reading...