|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 13th, 2004, 08:16 AM
|
|||
|
|||
Conversion of excel vba code to access vba
I have a VBA code in excel that extracts data from a txt file that needs to
be extracted character by character because there are different types of lines 16 CHINA FIRST AUTOMOBILE GROUP IMPORT AND EXPORT CORP. NO.99,DONGFENG STREET,THE PEOPLE'S 1 16 REPUBLIC OF CHINA 2 21 TO ORDER OF FOUNTAIN TRUST BANK PLC 1 26 1 WESTERN METAL PRODUCTS COMPANY LIMITED. 18,WEMPCO ROAD,PLOT 1A BLOCK D,OGBA SCHEME,IKEJA, LAGOS, 1 26 1 NIGERIA 2 41 001961590001 000004 CONTAINER 000131600000000000000000123980CONSOLIDATED CARGO 44 001N/M 47 001STC. 8 UNIT OF CA6472A GASOLINE RV STATION WAGON (4X2)WITH ACCESSORIES AND PARTS this is code that i wrote for excel to process it Can any anyone tell me if i can convertit for access or rewrite some of the code so that it will work in access Sub Jordan_Extraction_CAL() Dim i As Integer Dim j As Integer Dim K As Integer Dim m As Integer Dim n As Integer Dim o As Integer Dim p As Integer Dim q As Integer Dim r As Integer Dim s As Integer Dim t As Integer Dim u As Integer Dim v As Integer Dim w As Integer Dim x As Integer Dim y As Integer Dim BLNO As String Dim vessCODE As String Dim VOYno As String Dim CargoWeight As Long Dim ContainerTareWeight As Long j = 4 m = 4 n = 4 o = 4 p = 4 q = 4 r = 4 s = 4 t = 4 u = 4 v = 4 w = 4 x = 4 y = 4 For i = 1 To 5000 'Mainly for the name of the file If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 11 Then vessCODE = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 11, 3) VOYno = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 34, 8) End If 'Mainly for record 12 FIRST RECORD OF 1 B/L 'sheet 1 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 12 Then m = m + 1 BLnumber = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 98, 17) 'B/L number Worksheets("RECORD 12").Cells(m, 1).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 17) 'Pre-vessel Code Worksheets("RECORD 12").Cells(m, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 37, 3) 'Pre-vessel Name Worksheets("RECORD 12").Cells(m, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 40, 20) 'Pre-voyage No Worksheets("RECORD 12").Cells(m, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 60, 8) 'Port Of Discharge Worksheets("RECORD 12").Cells(m, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 68, 5) 'Port of Loading Worksheets("RECORD 12").Cells(m, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 73, 5) 'BL CY-CFS items Worksheets("RECORD 12").Cells(m, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 78, 9) 'BL Prepaid/Collect 'display words If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 1) = "P" Then Worksheets("RECORD 12").Cells(m, 8).Value = "Prepaid" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 1) = "C" Then Worksheets("RECORD 12").Cells(m, 8).Value = "Collect" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 1) = "F" Then Worksheets("RECORD 12").Cells(m, 8).Value = "Foreign" End If 'Tranship-ID If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 88, 1) = "Y" Then Worksheets("RECORD 12").Cells(m, 9).Value = "Yes" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 88, 1) = "N" Then Worksheets("RECORD 12").Cells(m, 9).Value = "No" End If 'BL all empty ctn-ID If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 89, 1) = "Y" Then Worksheets("RECORD 12").Cells(m, 10).Value = "Yes" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 89, 1) = "N" Then Worksheets("RECORD 12").Cells(m, 10).Value = "No" End If 'Loading date Worksheets("RECORD 12").Cells(m, 11).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 90, 8) 'Orginal BL Worksheets("RECORD 12").Cells(m, 12).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 98, 17) 'Port Of Issue (Orig BL) Worksheets("RECORD 12").Cells(m, 13).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 115, 5) 'Pre-voyage arrival date Worksheets("RECORD 12").Cells(m, 14).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 120, 8) End If 'Mainly for record 13 Send Record of 1 B/L 'Sheet 2 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 13 Then n = n + 1 'B/L number Worksheets("RECORD 13").Cells(n, 1).Value = BLnumber 'Port of Origin Worksheets("RECORD 13").Cells(n, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 5) 'Port of Discharge Worksheets("RECORD 13").Cells(n, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 11, 5) 'Final Destination (Port Code) Worksheets("RECORD 13").Cells(n, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 21, 5) 'Final Destination (Name) Worksheets("RECORD 13").Cells(n, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 26, 20) End If 'Mainly for record 16 Shippers Feild 'Sheet 3 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 16 Then o = o + 1 'B/L number Worksheets("RECORD 16").Cells(o, 1).Value = BLnumber '#shippers code Worksheets("RECORD 16").Cells(o, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 7) 'Shipper Item (1) Worksheets("RECORD 16").Cells(o, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 23, 35) 'Shipper Item (2) Worksheets("RECORD 16").Cells(o, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 35) 'Shipper Item (3) Worksheets("RECORD 16").Cells(o, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 93, 35) End If 'mainly for record 21 CONSIGHEE FIELDS 'Sheet 4 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 21 Then p = p + 1 'B/L number Worksheets("RECORD 21").Cells(p, 1).Value = BLnumber 'consignee Code Worksheets("RECORD 21").Cells(p, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 7) 'consignee Item - (1) Worksheets("RECORD 21").Cells(p, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 23, 35) 'consignee Item - (2) Worksheets("RECORD 21").Cells(p, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 35) 'consignee Item - (3) Worksheets("RECORD 21").Cells(p, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 93, 35) End If 'Mainly for record 26 Notify Party feilds (multiple records) 'Sheet 4 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 26 Then q = q + 1 'B/L number Worksheets("RECORD 26").Cells(q, 1).Value = BLnumber 'Notify-I Worksheets("RECORD 26").Cells(q, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 1) '#Notify Code Worksheets("RECORD 26").Cells(q, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 7, 7) 'Notify Field-(1) Worksheets("RECORD 26").Cells(q, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 23, 35) 'Notify Field-(2) Worksheets("RECORD 26").Cells(q, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 35) 'Notify Field-(3) Worksheets("RECORD 26").Cells(q, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 93, 35) End If 'Mainly for record 41 Cargo Fields (Line Details) 'Sheet 5 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 41 Then r = r + 1 'B/L number Worksheets("RECORD 41").Cells(r, 1).Value = BLnumber 'Cargo sequence# Worksheets("RECORD 41").Cells(r, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 3) 'commodity code Worksheets("RECORD 41").Cells(r, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 9) 'No of packages/containers Worksheets("RECORD 41").Cells(r, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 19, 6) 'Package in words Worksheets("RECORD 41").Cells(r, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 28, 15) 'Cargo gross Weight Worksheets("RECORD 41").Cells(r, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 43, 10) 'Cargo Net weight Worksheets("RECORD 41").Cells(r, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 53, 10) 'cargo measurement Worksheets("RECORD 41").Cells(r, 8).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 63, 10) 'commodity name Worksheets("RECORD 41").Cells(r, 9).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 73, 128) End If 'Mainly for RECORD 44 - Cargo Marks And Nos (Multiple Records) 'Sheet6 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 44 Then s = s + 1 'B/L number Worksheets("RECORD 44").Cells(s, 1).Value = BLnumber 'Cargo Sequence No# Worksheets("RECORD 44").Cells(s, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 3) 'Commodity code Worksheets("RECORD 44").Cells(s, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 128) End If 'Mainly for RECORD 47 - Cargo Descriptions (Multiple Records) 'Sheet 7 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 47 Then t = t + 1 'B/L number Worksheets("RECORD 47").Cells(t, 1).Value = BLnumber 'Cargo Sequence No# Worksheets("RECORD 47").Cells(t, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 3) 'Cargo Description-(1) Worksheets("RECORD 47").Cells(t, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 30) 'Cargo Description-(2) Worksheets("RECORD 47").Cells(t, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 39, 30) 'Cargo Description-(3) Worksheets("RECORD 47").Cells(t, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 69, 30) 'Cargo Description-(4) Worksheets("RECORD 47").Cells(t, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 99, 30) End If 'Mainly for record 51 Container Field Record 'Sheet 8 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 51 Then j = j + 1 'B/L number Worksheets("RECORD 51").Cells(j, 1).Value = BLnumber 'Container number (51) Worksheets("RECORD 51").Cells(j, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 11) 'Container SOC (51) If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 20, 1) = "Y" Then Worksheets("RECORD 51").Cells(j, 3).Value = "Yes" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 20, 1) = "N" Then Worksheets("RECORD 51").Cells(j, 3).Value = "No" End If 'Seal No (51) Worksheets("RECORD 51").Cells(j, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 21, 10) 'Container Size (51) Worksheets("RECORD 51").Cells(j, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 31, 4) 'Cntr Loading Status (51) If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 36, 1) = "F" Then Worksheets("RECORD 51").Cells(j, 6).Value = "Full" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 36, 1) = "P" Then Worksheets("RECORD 51").Cells(j, 6).Value = "Part" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 36, 1) = "E" Then Worksheets("RECORD 51").Cells(j, 6).Value = "Empty" End If 'Cntr CY-CFS (51) Worksheets("RECORD 51").Cells(j, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 37, 10) 'Cntr No of Packages (51) Worksheets("RECORD 51").Cells(j, 8).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 47, 6) 'Cntr Kind of Packages (51) Worksheets("RECORD 51").Cells(j, 9).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 53, 8) 'Cntr Cargo Weight (51) Worksheets("RECORD 51").Cells(j, 10).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 61, 10) 'Cntr Tare Weight (51) Worksheets("RECORD 51").Cells(j, 11).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 71, 10) 'Cntr Cargo Measurement (51) Worksheets("RECORD 51").Cells(j, 12).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 81, 10) 'Seal No (51) Worksheets("RECORD 51").Cells(j, 13).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 91, 25) End If 'Mainly for record 61 Freight Charges Feild 'Sheet 9 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 61 Then u = u + 1 'B/L number Worksheets("RECORD 61").Cells(u, 1).Value = BLnumber 'Cargo Sequence No# Worksheets("RECORD 61").Cells(u, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 3, 3) 'Sequence No Worksheets("RECORD 61").Cells(u, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 2) 'Feight Charge Code Worksheets("RECORD 61").Cells(u, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 8, 4) 'Payable at (Port) Worksheets("RECORD 61").Cells(u, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 12, 5) 'Quantity Worksheets("RECORD 61").Cells(u, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 17, 8) 'Currency Worksheets("RECORD 61").Cells(u, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 25, 3) 'Rate of Feight Charges Worksheets("RECORD 61").Cells(u, 8).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 28, 13) 'Unit of Quantity Worksheets("RECORD 61").Cells(u, 9).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 41, 4) 'Amount Worksheets("RECORD 61").Cells(u, 10).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 45, 13) 'Sign of Amount Worksheets("RECORD 61").Cells(u, 11).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 1) 'Exchange Rate Worksheets("RECORD 61").Cells(u, 12).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 59, 10) 'Exch to Currency Code Worksheets("RECORD 61").Cells(u, 13).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 69, 3) 'Equivalent Worksheets("RECORD 61").Cells(u, 14).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 72, 13) 'Sign Of Equivalent Amount Worksheets("RECORD 61").Cells(u, 15).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 85, 1) 'Prepaid or Collect 'display words If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 86, 1) = "P" Then Worksheets("RECORD 61").Cells(u, 16).Value = "Prepaid" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 86, 1) = "C" Then Worksheets("RECORD 61").Cells(u, 16).Value = "Collect" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 86, 1) = "F" Then Worksheets("RECORD 61").Cells(u, 16).Value = "Foreign" End If 'Description oF Details Worksheets("RECORD 61").Cells(u, 17).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 30) 'Party Responsible to Pay If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 117, 1) = "S" Then Worksheets("RECORD 61").Cells(u, 18).Value = "Shipper" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 117, 1) = "C" Then Worksheets("RECORD 61").Cells(u, 18).Value = "Consignee" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 117, 1) = "N" Then Worksheets("RECORD 61").Cells(u, 18).Value = "Notify Party" End If 'Cntr Size Worksheets("RECORD 61").Cells(u, 19).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 118, 1) End If 'Mainly for record 72 Other Fields 'Sheet 10 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 72 Then v = v + 1 'B/L number Worksheets("RECORD 72").Cells(v, 1).Value = BLnumber 'Bill of Laden Text (1) Worksheets("RECORD 72").Cells(v, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 35) 'Bill of Laden Text (2) Worksheets("RECORD 72").Cells(v, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 41, 35) 'Bill of Laden Text (3) Worksheets("RECORD 72").Cells(v, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 76, 35) End If 'Mainly for record 74 Other Fields 'Sheet 11 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 74 Then w = w + 1 'B/L number Worksheets("RECORD 74").Cells(w, 1).Value = BLnumber 'Place of B/L Issue Worksheets("RECORD 74").Cells(w, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 5) 'Date Of Issue Worksheets("RECORD 74").Cells(w, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 11, 8) 'Perpaid at (Port Code) Worksheets("RECORD 74").Cells(w, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 41, 5) 'Payable at (Port Code) Worksheets("RECORD 74").Cells(w, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 46, 5) End If Next i ChDir "D:\Joss Blaze\temp" ActiveWorkbook.SaveAs Filename:="D:\Joss Blaze\temp\temp.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End Sub 
|
|
#2
July 13th, 2004, 10:44 AM
|
|||
|
|||
|
Conversion of excel vba code to access vba
Alrite but is the anyway to open excel from within access so that i only
have to open one application instead of two thanks "Arvin Meyer" wrote in message ... You should be able to convert it, but you will not be able to refer to cells, since that concept doesn't exist in a database. You can build an array in memory and refer to elements of the array. You can also work on a line at a time by hunting down carraige returns Chr(13) and Line feeds Chr(10) and acting upon the data. My recommendation, though, would be to use the code in Excel that you know is already working properly, then import the finished data into Access. -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads: http://www.datastrat.com http://www.mvps.org/access "filnigeria" wrote in message ... I have a VBA code in excel that extracts data from a txt file that needs to be extracted character by character because there are different types of lines 16 CHINA FIRST AUTOMOBILE GROUP IMPORT AND EXPORT CORP. NO.99,DONGFENG STREET,THE PEOPLE'S 1 16 REPUBLIC OF CHINA 2 21 TO ORDER OF FOUNTAIN TRUST BANK PLC 1 26 1 WESTERN METAL PRODUCTS COMPANY LIMITED. 18,WEMPCO ROAD,PLOT 1A BLOCK D,OGBA SCHEME,IKEJA, LAGOS, 1 26 1 NIGERIA 2 41 001961590001 000004 CONTAINER 000131600000000000000000123980CONSOLIDATED CARGO 44 001N/M 47 001STC. 8 UNIT OF CA6472A GASOLINE RV STATION WAGON (4X2)WITH ACCESSORIES AND PARTS this is code that i wrote for excel to process it Can any anyone tell me if i can convertit for access or rewrite some of the code so that it will work in access Sub Jordan_Extraction_CAL() Dim i As Integer Dim j As Integer Dim K As Integer Dim m As Integer Dim n As Integer Dim o As Integer Dim p As Integer Dim q As Integer Dim r As Integer Dim s As Integer Dim t As Integer Dim u As Integer Dim v As Integer Dim w As Integer Dim x As Integer Dim y As Integer Dim BLNO As String Dim vessCODE As String Dim VOYno As String Dim CargoWeight As Long Dim ContainerTareWeight As Long j = 4 m = 4 n = 4 o = 4 p = 4 q = 4 r = 4 s = 4 t = 4 u = 4 v = 4 w = 4 x = 4 y = 4 For i = 1 To 5000 'Mainly for the name of the file If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 11 Then vessCODE = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 11, 3) VOYno = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 34, 8) End If 'Mainly for record 12 FIRST RECORD OF 1 B/L 'sheet 1 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 12 Then m = m + 1 BLnumber = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 98, 17) 'B/L number Worksheets("RECORD 12").Cells(m, 1).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 17) 'Pre-vessel Code Worksheets("RECORD 12").Cells(m, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 37, 3) 'Pre-vessel Name Worksheets("RECORD 12").Cells(m, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 40, 20) 'Pre-voyage No Worksheets("RECORD 12").Cells(m, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 60, 8) 'Port Of Discharge Worksheets("RECORD 12").Cells(m, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 68, 5) 'Port of Loading Worksheets("RECORD 12").Cells(m, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 73, 5) 'BL CY-CFS items Worksheets("RECORD 12").Cells(m, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 78, 9) 'BL Prepaid/Collect 'display words If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 1) = "P" Then Worksheets("RECORD 12").Cells(m, 8).Value = "Prepaid" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 1) = "C" Then Worksheets("RECORD 12").Cells(m, 8).Value = "Collect" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 1) = "F" Then Worksheets("RECORD 12").Cells(m, 8).Value = "Foreign" End If 'Tranship-ID If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 88, 1) = "Y" Then Worksheets("RECORD 12").Cells(m, 9).Value = "Yes" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 88, 1) = "N" Then Worksheets("RECORD 12").Cells(m, 9).Value = "No" End If 'BL all empty ctn-ID If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 89, 1) = "Y" Then Worksheets("RECORD 12").Cells(m, 10).Value = "Yes" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 89, 1) = "N" Then Worksheets("RECORD 12").Cells(m, 10).Value = "No" End If 'Loading date Worksheets("RECORD 12").Cells(m, 11).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 90, 8) 'Orginal BL Worksheets("RECORD 12").Cells(m, 12).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 98, 17) 'Port Of Issue (Orig BL) Worksheets("RECORD 12").Cells(m, 13).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 115, 5) 'Pre-voyage arrival date Worksheets("RECORD 12").Cells(m, 14).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 120, 8) End If 'Mainly for record 13 Send Record of 1 B/L 'Sheet 2 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 13 Then n = n + 1 'B/L number Worksheets("RECORD 13").Cells(n, 1).Value = BLnumber 'Port of Origin Worksheets("RECORD 13").Cells(n, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 5) 'Port of Discharge Worksheets("RECORD 13").Cells(n, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 11, 5) 'Final Destination (Port Code) Worksheets("RECORD 13").Cells(n, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 21, 5) 'Final Destination (Name) Worksheets("RECORD 13").Cells(n, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 26, 20) End If 'Mainly for record 16 Shippers Feild 'Sheet 3 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 16 Then o = o + 1 'B/L number Worksheets("RECORD 16").Cells(o, 1).Value = BLnumber '#shippers code Worksheets("RECORD 16").Cells(o, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 7) 'Shipper Item (1) Worksheets("RECORD 16").Cells(o, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 23, 35) 'Shipper Item (2) Worksheets("RECORD 16").Cells(o, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 35) 'Shipper Item (3) Worksheets("RECORD 16").Cells(o, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 93, 35) End If 'mainly for record 21 CONSIGHEE FIELDS 'Sheet 4 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 21 Then p = p + 1 'B/L number Worksheets("RECORD 21").Cells(p, 1).Value = BLnumber 'consignee Code Worksheets("RECORD 21").Cells(p, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 7) 'consignee Item - (1) Worksheets("RECORD 21").Cells(p, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 23, 35) 'consignee Item - (2) Worksheets("RECORD 21").Cells(p, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 35) 'consignee Item - (3) Worksheets("RECORD 21").Cells(p, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 93, 35) End If 'Mainly for record 26 Notify Party feilds (multiple records) 'Sheet 4 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 26 Then q = q + 1 'B/L number Worksheets("RECORD 26").Cells(q, 1).Value = BLnumber 'Notify-I Worksheets("RECORD 26").Cells(q, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 1) '#Notify Code Worksheets("RECORD 26").Cells(q, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 7, 7) 'Notify Field-(1) Worksheets("RECORD 26").Cells(q, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 23, 35) 'Notify Field-(2) Worksheets("RECORD 26").Cells(q, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 35) 'Notify Field-(3) Worksheets("RECORD 26").Cells(q, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 93, 35) End If 'Mainly for record 41 Cargo Fields (Line Details) 'Sheet 5 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 41 Then r = r + 1 'B/L number Worksheets("RECORD 41").Cells(r, 1).Value = BLnumber 'Cargo sequence# Worksheets("RECORD 41").Cells(r, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 3) 'commodity code Worksheets("RECORD 41").Cells(r, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 9) 'No of packages/containers Worksheets("RECORD 41").Cells(r, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 19, 6) 'Package in words Worksheets("RECORD 41").Cells(r, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 28, 15) 'Cargo gross Weight Worksheets("RECORD 41").Cells(r, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 43, 10) 'Cargo Net weight Worksheets("RECORD 41").Cells(r, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 53, 10) 'cargo measurement Worksheets("RECORD 41").Cells(r, 8).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 63, 10) 'commodity name Worksheets("RECORD 41").Cells(r, 9).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 73, 128) End If 'Mainly for RECORD 44 - Cargo Marks And Nos (Multiple Records) 'Sheet6 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 44 Then s = s + 1 'B/L number Worksheets("RECORD 44").Cells(s, 1).Value = BLnumber 'Cargo Sequence No# Worksheets("RECORD 44").Cells(s, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 3) 'Commodity code Worksheets("RECORD 44").Cells(s, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 128) End If 'Mainly for RECORD 47 - Cargo Descriptions (Multiple Records) 'Sheet 7 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 47 Then t = t + 1 'B/L number Worksheets("RECORD 47").Cells(t, 1).Value = BLnumber 'Cargo Sequence No# Worksheets("RECORD 47").Cells(t, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 3) 'Cargo Description-(1) Worksheets("RECORD 47").Cells(t, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 30) 'Cargo Description-(2) Worksheets("RECORD 47").Cells(t, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 39, 30) 'Cargo Description-(3) Worksheets("RECORD 47").Cells(t, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 69, 30) 'Cargo Description-(4) Worksheets("RECORD 47").Cells(t, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 99, 30) End If 'Mainly for record 51 Container Field Record 'Sheet 8 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 51 Then j = j + 1 'B/L number Worksheets("RECORD 51").Cells(j, 1).Value = BLnumber 'Container number (51) Worksheets("RECORD 51").Cells(j, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 11) 'Container SOC (51) If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 20, 1) = "Y" Then Worksheets("RECORD 51").Cells(j, 3).Value = "Yes" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 20, 1) = "N" Then Worksheets("RECORD 51").Cells(j, 3).Value = "No" End If 'Seal No (51) Worksheets("RECORD 51").Cells(j, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 21, 10) 'Container Size (51) Worksheets("RECORD 51").Cells(j, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 31, 4) 'Cntr Loading Status (51) If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 36, 1) = "F" Then Worksheets("RECORD 51").Cells(j, 6).Value = "Full" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 36, 1) = "P" Then Worksheets("RECORD 51").Cells(j, 6).Value = "Part" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 36, 1) = "E" Then Worksheets("RECORD 51").Cells(j, 6).Value = "Empty" End If 'Cntr CY-CFS (51) Worksheets("RECORD 51").Cells(j, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 37, 10) 'Cntr No of Packages (51) Worksheets("RECORD 51").Cells(j, 8).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 47, 6) 'Cntr Kind of Packages (51) Worksheets("RECORD 51").Cells(j, 9).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 53, 8) 'Cntr Cargo Weight (51) Worksheets("RECORD 51").Cells(j, 10).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 61, 10) 'Cntr Tare Weight (51) Worksheets("RECORD 51").Cells(j, 11).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 71, 10) 'Cntr Cargo Measurement (51) Worksheets("RECORD 51").Cells(j, 12).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 81, 10) 'Seal No (51) Worksheets("RECORD 51").Cells(j, 13).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 91, 25) End If 'Mainly for record 61 Freight Charges Feild 'Sheet 9 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 61 Then u = u + 1 'B/L number Worksheets("RECORD 61").Cells(u, 1).Value = BLnumber 'Cargo Sequence No# Worksheets("RECORD 61").Cells(u, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 3, 3) 'Sequence No Worksheets("RECORD 61").Cells(u, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 2) 'Feight Charge Code Worksheets("RECORD 61").Cells(u, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 8, 4) 'Payable at (Port) Worksheets("RECORD 61").Cells(u, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 12, 5) 'Quantity Worksheets("RECORD 61").Cells(u, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 17, 8) 'Currency Worksheets("RECORD 61").Cells(u, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 25, 3) 'Rate of Feight Charges Worksheets("RECORD 61").Cells(u, 8).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 28, 13) 'Unit of Quantity Worksheets("RECORD 61").Cells(u, 9).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 41, 4) 'Amount Worksheets("RECORD 61").Cells(u, 10).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 45, 13) 'Sign of Amount Worksheets("RECORD 61").Cells(u, 11).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 1) 'Exchange Rate Worksheets("RECORD 61").Cells(u, 12).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 59, 10) 'Exch to Currency Code Worksheets("RECORD 61").Cells(u, 13).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 69, 3) 'Equivalent Worksheets("RECORD 61").Cells(u, 14).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 72, 13) 'Sign Of Equivalent Amount Worksheets("RECORD 61").Cells(u, 15).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 85, 1) 'Prepaid or Collect 'display words If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 86, 1) = "P" Then Worksheets("RECORD 61").Cells(u, 16).Value = "Prepaid" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 86, 1) = "C" Then Worksheets("RECORD 61").Cells(u, 16).Value = "Collect" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 86, 1) = "F" Then Worksheets("RECORD 61").Cells(u, 16).Value = "Foreign" End If 'Description oF Details Worksheets("RECORD 61").Cells(u, 17).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 30) 'Party Responsible to Pay If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 117, 1) = "S" Then Worksheets("RECORD 61").Cells(u, 18).Value = "Shipper" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 117, 1) = "C" Then Worksheets("RECORD 61").Cells(u, 18).Value = "Consignee" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 117, 1) = "N" Then Worksheets("RECORD 61").Cells(u, 18).Value = "Notify Party" End If 'Cntr Size Worksheets("RECORD 61").Cells(u, 19).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 118, 1) End If 'Mainly for record 72 Other Fields 'Sheet 10 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 72 Then v = v + 1 'B/L number Worksheets("RECORD 72").Cells(v, 1).Value = BLnumber 'Bill of Laden Text (1) Worksheets("RECORD 72").Cells(v, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 35) 'Bill of Laden Text (2) Worksheets("RECORD 72").Cells(v, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 41, 35) 'Bill of Laden Text (3) Worksheets("RECORD 72").Cells(v, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 76, 35) End If 'Mainly for record 74 Other Fields 'Sheet 11 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 74 Then w = w + 1 'B/L number Worksheets("RECORD 74").Cells(w, 1).Value = BLnumber 'Place of B/L Issue Worksheets("RECORD 74").Cells(w, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 5) 'Date Of Issue Worksheets("RECORD 74").Cells(w, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 11, 8) 'Perpaid at (Port Code) Worksheets("RECORD 74").Cells(w, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 41, 5) 'Payable at (Port Code) Worksheets("RECORD 74").Cells(w, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 46, 5) End If Next i ChDir "D:\Joss Blaze\temp" ActiveWorkbook.SaveAs Filename:="D:\Joss Blaze\temp\temp.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End Sub
|
|
#3
July 13th, 2004, 10:44 AM
|
|||
|
|||
|
Conversion of excel vba code to access vba
Alrite but is the anyway to open excel from within access so that i only
have to open one application instead of two thanks "Arvin Meyer" wrote in message ... You should be able to convert it, but you will not be able to refer to cells, since that concept doesn't exist in a database. You can build an array in memory and refer to elements of the array. You can also work on a line at a time by hunting down carraige returns Chr(13) and Line feeds Chr(10) and acting upon the data. My recommendation, though, would be to use the code in Excel that you know is already working properly, then import the finished data into Access. -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads: http://www.datastrat.com http://www.mvps.org/access "filnigeria" wrote in message ... I have a VBA code in excel that extracts data from a txt file that needs to be extracted character by character because there are different types of lines 16 CHINA FIRST AUTOMOBILE GROUP IMPORT AND EXPORT CORP. NO.99,DONGFENG STREET,THE PEOPLE'S 1 16 REPUBLIC OF CHINA 2 21 TO ORDER OF FOUNTAIN TRUST BANK PLC 1 26 1 WESTERN METAL PRODUCTS COMPANY LIMITED. 18,WEMPCO ROAD,PLOT 1A BLOCK D,OGBA SCHEME,IKEJA, LAGOS, 1 26 1 NIGERIA 2 41 001961590001 000004 CONTAINER 000131600000000000000000123980CONSOLIDATED CARGO 44 001N/M 47 001STC. 8 UNIT OF CA6472A GASOLINE RV STATION WAGON (4X2)WITH ACCESSORIES AND PARTS this is code that i wrote for excel to process it Can any anyone tell me if i can convertit for access or rewrite some of the code so that it will work in access Sub Jordan_Extraction_CAL() Dim i As Integer Dim j As Integer Dim K As Integer Dim m As Integer Dim n As Integer Dim o As Integer Dim p As Integer Dim q As Integer Dim r As Integer Dim s As Integer Dim t As Integer Dim u As Integer Dim v As Integer Dim w As Integer Dim x As Integer Dim y As Integer Dim BLNO As String Dim vessCODE As String Dim VOYno As String Dim CargoWeight As Long Dim ContainerTareWeight As Long j = 4 m = 4 n = 4 o = 4 p = 4 q = 4 r = 4 s = 4 t = 4 u = 4 v = 4 w = 4 x = 4 y = 4 For i = 1 To 5000 'Mainly for the name of the file If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 11 Then vessCODE = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 11, 3) VOYno = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 34, 8) End If 'Mainly for record 12 FIRST RECORD OF 1 B/L 'sheet 1 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 12 Then m = m + 1 BLnumber = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 98, 17) 'B/L number Worksheets("RECORD 12").Cells(m, 1).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 17) 'Pre-vessel Code Worksheets("RECORD 12").Cells(m, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 37, 3) 'Pre-vessel Name Worksheets("RECORD 12").Cells(m, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 40, 20) 'Pre-voyage No Worksheets("RECORD 12").Cells(m, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 60, 8) 'Port Of Discharge Worksheets("RECORD 12").Cells(m, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 68, 5) 'Port of Loading Worksheets("RECORD 12").Cells(m, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 73, 5) 'BL CY-CFS items Worksheets("RECORD 12").Cells(m, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 78, 9) 'BL Prepaid/Collect 'display words If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 1) = "P" Then Worksheets("RECORD 12").Cells(m, 8).Value = "Prepaid" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 1) = "C" Then Worksheets("RECORD 12").Cells(m, 8).Value = "Collect" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 1) = "F" Then Worksheets("RECORD 12").Cells(m, 8).Value = "Foreign" End If 'Tranship-ID If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 88, 1) = "Y" Then Worksheets("RECORD 12").Cells(m, 9).Value = "Yes" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 88, 1) = "N" Then Worksheets("RECORD 12").Cells(m, 9).Value = "No" End If 'BL all empty ctn-ID If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 89, 1) = "Y" Then Worksheets("RECORD 12").Cells(m, 10).Value = "Yes" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 89, 1) = "N" Then Worksheets("RECORD 12").Cells(m, 10).Value = "No" End If 'Loading date Worksheets("RECORD 12").Cells(m, 11).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 90, 8) 'Orginal BL Worksheets("RECORD 12").Cells(m, 12).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 98, 17) 'Port Of Issue (Orig BL) Worksheets("RECORD 12").Cells(m, 13).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 115, 5) 'Pre-voyage arrival date Worksheets("RECORD 12").Cells(m, 14).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 120, 8) End If 'Mainly for record 13 Send Record of 1 B/L 'Sheet 2 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 13 Then n = n + 1 'B/L number Worksheets("RECORD 13").Cells(n, 1).Value = BLnumber 'Port of Origin Worksheets("RECORD 13").Cells(n, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 5) 'Port of Discharge Worksheets("RECORD 13").Cells(n, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 11, 5) 'Final Destination (Port Code) Worksheets("RECORD 13").Cells(n, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 21, 5) 'Final Destination (Name) Worksheets("RECORD 13").Cells(n, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 26, 20) End If 'Mainly for record 16 Shippers Feild 'Sheet 3 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 16 Then o = o + 1 'B/L number Worksheets("RECORD 16").Cells(o, 1).Value = BLnumber '#shippers code Worksheets("RECORD 16").Cells(o, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 7) 'Shipper Item (1) Worksheets("RECORD 16").Cells(o, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 23, 35) 'Shipper Item (2) Worksheets("RECORD 16").Cells(o, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 35) 'Shipper Item (3) Worksheets("RECORD 16").Cells(o, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 93, 35) End If 'mainly for record 21 CONSIGHEE FIELDS 'Sheet 4 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 21 Then p = p + 1 'B/L number Worksheets("RECORD 21").Cells(p, 1).Value = BLnumber 'consignee Code Worksheets("RECORD 21").Cells(p, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 7) 'consignee Item - (1) Worksheets("RECORD 21").Cells(p, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 23, 35) 'consignee Item - (2) Worksheets("RECORD 21").Cells(p, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 35) 'consignee Item - (3) Worksheets("RECORD 21").Cells(p, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 93, 35) End If 'Mainly for record 26 Notify Party feilds (multiple records) 'Sheet 4 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 26 Then q = q + 1 'B/L number Worksheets("RECORD 26").Cells(q, 1).Value = BLnumber 'Notify-I Worksheets("RECORD 26").Cells(q, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 1) '#Notify Code Worksheets("RECORD 26").Cells(q, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 7, 7) 'Notify Field-(1) Worksheets("RECORD 26").Cells(q, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 23, 35) 'Notify Field-(2) Worksheets("RECORD 26").Cells(q, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 35) 'Notify Field-(3) Worksheets("RECORD 26").Cells(q, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 93, 35) End If 'Mainly for record 41 Cargo Fields (Line Details) 'Sheet 5 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 41 Then r = r + 1 'B/L number Worksheets("RECORD 41").Cells(r, 1).Value = BLnumber 'Cargo sequence# Worksheets("RECORD 41").Cells(r, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 3) 'commodity code Worksheets("RECORD 41").Cells(r, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 9) 'No of packages/containers Worksheets("RECORD 41").Cells(r, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 19, 6) 'Package in words Worksheets("RECORD 41").Cells(r, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 28, 15) 'Cargo gross Weight Worksheets("RECORD 41").Cells(r, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 43, 10) 'Cargo Net weight Worksheets("RECORD 41").Cells(r, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 53, 10) 'cargo measurement Worksheets("RECORD 41").Cells(r, 8).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 63, 10) 'commodity name Worksheets("RECORD 41").Cells(r, 9).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 73, 128) End If 'Mainly for RECORD 44 - Cargo Marks And Nos (Multiple Records) 'Sheet6 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 44 Then s = s + 1 'B/L number Worksheets("RECORD 44").Cells(s, 1).Value = BLnumber 'Cargo Sequence No# Worksheets("RECORD 44").Cells(s, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 3) 'Commodity code Worksheets("RECORD 44").Cells(s, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 128) End If 'Mainly for RECORD 47 - Cargo Descriptions (Multiple Records) 'Sheet 7 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 47 Then t = t + 1 'B/L number Worksheets("RECORD 47").Cells(t, 1).Value = BLnumber 'Cargo Sequence No# Worksheets("RECORD 47").Cells(t, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 3) 'Cargo Description-(1) Worksheets("RECORD 47").Cells(t, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 30) 'Cargo Description-(2) Worksheets("RECORD 47").Cells(t, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 39, 30) 'Cargo Description-(3) Worksheets("RECORD 47").Cells(t, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 69, 30) 'Cargo Description-(4) Worksheets("RECORD 47").Cells(t, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 99, 30) End If 'Mainly for record 51 Container Field Record 'Sheet 8 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 51 Then j = j + 1 'B/L number Worksheets("RECORD 51").Cells(j, 1).Value = BLnumber 'Container number (51) Worksheets("RECORD 51").Cells(j, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 11) 'Container SOC (51) If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 20, 1) = "Y" Then Worksheets("RECORD 51").Cells(j, 3).Value = "Yes" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 20, 1) = "N" Then Worksheets("RECORD 51").Cells(j, 3).Value = "No" End If 'Seal No (51) Worksheets("RECORD 51").Cells(j, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 21, 10) 'Container Size (51) Worksheets("RECORD 51").Cells(j, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 31, 4) 'Cntr Loading Status (51) If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 36, 1) = "F" Then Worksheets("RECORD 51").Cells(j, 6).Value = "Full" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 36, 1) = "P" Then Worksheets("RECORD 51").Cells(j, 6).Value = "Part" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 36, 1) = "E" Then Worksheets("RECORD 51").Cells(j, 6).Value = "Empty" End If 'Cntr CY-CFS (51) Worksheets("RECORD 51").Cells(j, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 37, 10) 'Cntr No of Packages (51) Worksheets("RECORD 51").Cells(j, 8).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 47, 6) 'Cntr Kind of Packages (51) Worksheets("RECORD 51").Cells(j, 9).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 53, 8) 'Cntr Cargo Weight (51) Worksheets("RECORD 51").Cells(j, 10).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 61, 10) 'Cntr Tare Weight (51) Worksheets("RECORD 51").Cells(j, 11).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 71, 10) 'Cntr Cargo Measurement (51) Worksheets("RECORD 51").Cells(j, 12).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 81, 10) 'Seal No (51) Worksheets("RECORD 51").Cells(j, 13).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 91, 25) End If 'Mainly for record 61 Freight Charges Feild 'Sheet 9 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 61 Then u = u + 1 'B/L number Worksheets("RECORD 61").Cells(u, 1).Value = BLnumber 'Cargo Sequence No# Worksheets("RECORD 61").Cells(u, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 3, 3) 'Sequence No Worksheets("RECORD 61").Cells(u, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 2) 'Feight Charge Code Worksheets("RECORD 61").Cells(u, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 8, 4) 'Payable at (Port) Worksheets("RECORD 61").Cells(u, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 12, 5) 'Quantity Worksheets("RECORD 61").Cells(u, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 17, 8) 'Currency Worksheets("RECORD 61").Cells(u, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 25, 3) 'Rate of Feight Charges Worksheets("RECORD 61").Cells(u, 8).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 28, 13) 'Unit of Quantity Worksheets("RECORD 61").Cells(u, 9).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 41, 4) 'Amount Worksheets("RECORD 61").Cells(u, 10).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 45, 13) 'Sign of Amount Worksheets("RECORD 61").Cells(u, 11).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 1) 'Exchange Rate Worksheets("RECORD 61").Cells(u, 12).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 59, 10) 'Exch to Currency Code Worksheets("RECORD 61").Cells(u, 13).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 69, 3) 'Equivalent Worksheets("RECORD 61").Cells(u, 14).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 72, 13) 'Sign Of Equivalent Amount Worksheets("RECORD 61").Cells(u, 15).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 85, 1) 'Prepaid or Collect 'display words If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 86, 1) = "P" Then Worksheets("RECORD 61").Cells(u, 16).Value = "Prepaid" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 86, 1) = "C" Then Worksheets("RECORD 61").Cells(u, 16).Value = "Collect" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 86, 1) = "F" Then Worksheets("RECORD 61").Cells(u, 16).Value = "Foreign" End If 'Description oF Details Worksheets("RECORD 61").Cells(u, 17).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 30) 'Party Responsible to Pay If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 117, 1) = "S" Then Worksheets("RECORD 61").Cells(u, 18).Value = "Shipper" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 117, 1) = "C" Then Worksheets("RECORD 61").Cells(u, 18).Value = "Consignee" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 117, 1) = "N" Then Worksheets("RECORD 61").Cells(u, 18).Value = "Notify Party" End If 'Cntr Size Worksheets("RECORD 61").Cells(u, 19).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 118, 1) End If 'Mainly for record 72 Other Fields 'Sheet 10 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 72 Then v = v + 1 'B/L number Worksheets("RECORD 72").Cells(v, 1).Value = BLnumber 'Bill of Laden Text (1) Worksheets("RECORD 72").Cells(v, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 35) 'Bill of Laden Text (2) Worksheets("RECORD 72").Cells(v, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 41, 35) 'Bill of Laden Text (3) Worksheets("RECORD 72").Cells(v, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 76, 35) End If 'Mainly for record 74 Other Fields 'Sheet 11 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 74 Then w = w + 1 'B/L number Worksheets("RECORD 74").Cells(w, 1).Value = BLnumber 'Place of B/L Issue Worksheets("RECORD 74").Cells(w, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 5) 'Date Of Issue Worksheets("RECORD 74").Cells(w, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 11, 8) 'Perpaid at (Port Code) Worksheets("RECORD 74").Cells(w, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 41, 5) 'Payable at (Port Code) Worksheets("RECORD 74").Cells(w, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 46, 5) End If Next i ChDir "D:\Joss Blaze\temp" ActiveWorkbook.SaveAs Filename:="D:\Joss Blaze\temp\temp.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End Sub
|
|
#4
July 13th, 2004, 11:32 AM
|
|||
|
|||
|
Conversion of excel vba code to access vba
You should be able to convert it, but you will not be able to refer to
cells, since that concept doesn't exist in a database. You can build an array in memory and refer to elements of the array. You can also work on a line at a time by hunting down carraige returns Chr(13) and Line feeds Chr(10) and acting upon the data. My recommendation, though, would be to use the code in Excel that you know is already working properly, then import the finished data into Access. -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads: http://www.datastrat.com http://www.mvps.org/access "filnigeria" wrote in message ... I have a VBA code in excel that extracts data from a txt file that needs to be extracted character by character because there are different types of lines 16 CHINA FIRST AUTOMOBILE GROUP IMPORT AND EXPORT CORP. NO.99,DONGFENG STREET,THE PEOPLE'S 1 16 REPUBLIC OF CHINA 2 21 TO ORDER OF FOUNTAIN TRUST BANK PLC 1 26 1 WESTERN METAL PRODUCTS COMPANY LIMITED. 18,WEMPCO ROAD,PLOT 1A BLOCK D,OGBA SCHEME,IKEJA, LAGOS, 1 26 1 NIGERIA 2 41 001961590001 000004 CONTAINER 000131600000000000000000123980CONSOLIDATED CARGO 44 001N/M 47 001STC. 8 UNIT OF CA6472A GASOLINE RV STATION WAGON (4X2)WITH ACCESSORIES AND PARTS this is code that i wrote for excel to process it Can any anyone tell me if i can convertit for access or rewrite some of the code so that it will work in access Sub Jordan_Extraction_CAL() Dim i As Integer Dim j As Integer Dim K As Integer Dim m As Integer Dim n As Integer Dim o As Integer Dim p As Integer Dim q As Integer Dim r As Integer Dim s As Integer Dim t As Integer Dim u As Integer Dim v As Integer Dim w As Integer Dim x As Integer Dim y As Integer Dim BLNO As String Dim vessCODE As String Dim VOYno As String Dim CargoWeight As Long Dim ContainerTareWeight As Long j = 4 m = 4 n = 4 o = 4 p = 4 q = 4 r = 4 s = 4 t = 4 u = 4 v = 4 w = 4 x = 4 y = 4 For i = 1 To 5000 'Mainly for the name of the file If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 11 Then vessCODE = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 11, 3) VOYno = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 34, 8) End If 'Mainly for record 12 FIRST RECORD OF 1 B/L 'sheet 1 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 12 Then m = m + 1 BLnumber = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 98, 17) 'B/L number Worksheets("RECORD 12").Cells(m, 1).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 17) 'Pre-vessel Code Worksheets("RECORD 12").Cells(m, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 37, 3) 'Pre-vessel Name Worksheets("RECORD 12").Cells(m, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 40, 20) 'Pre-voyage No Worksheets("RECORD 12").Cells(m, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 60, 8) 'Port Of Discharge Worksheets("RECORD 12").Cells(m, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 68, 5) 'Port of Loading Worksheets("RECORD 12").Cells(m, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 73, 5) 'BL CY-CFS items Worksheets("RECORD 12").Cells(m, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 78, 9) 'BL Prepaid/Collect 'display words If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 1) = "P" Then Worksheets("RECORD 12").Cells(m, 8).Value = "Prepaid" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 1) = "C" Then Worksheets("RECORD 12").Cells(m, 8).Value = "Collect" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 1) = "F" Then Worksheets("RECORD 12").Cells(m, 8).Value = "Foreign" End If 'Tranship-ID If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 88, 1) = "Y" Then Worksheets("RECORD 12").Cells(m, 9).Value = "Yes" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 88, 1) = "N" Then Worksheets("RECORD 12").Cells(m, 9).Value = "No" End If 'BL all empty ctn-ID If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 89, 1) = "Y" Then Worksheets("RECORD 12").Cells(m, 10).Value = "Yes" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 89, 1) = "N" Then Worksheets("RECORD 12").Cells(m, 10).Value = "No" End If 'Loading date Worksheets("RECORD 12").Cells(m, 11).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 90, 8) 'Orginal BL Worksheets("RECORD 12").Cells(m, 12).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 98, 17) 'Port Of Issue (Orig BL) Worksheets("RECORD 12").Cells(m, 13).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 115, 5) 'Pre-voyage arrival date Worksheets("RECORD 12").Cells(m, 14).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 120, 8) End If 'Mainly for record 13 Send Record of 1 B/L 'Sheet 2 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 13 Then n = n + 1 'B/L number Worksheets("RECORD 13").Cells(n, 1).Value = BLnumber 'Port of Origin Worksheets("RECORD 13").Cells(n, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 5) 'Port of Discharge Worksheets("RECORD 13").Cells(n, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 11, 5) 'Final Destination (Port Code) Worksheets("RECORD 13").Cells(n, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 21, 5) 'Final Destination (Name) Worksheets("RECORD 13").Cells(n, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 26, 20) End If 'Mainly for record 16 Shippers Feild 'Sheet 3 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 16 Then o = o + 1 'B/L number Worksheets("RECORD 16").Cells(o, 1).Value = BLnumber '#shippers code Worksheets("RECORD 16").Cells(o, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 7) 'Shipper Item (1) Worksheets("RECORD 16").Cells(o, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 23, 35) 'Shipper Item (2) Worksheets("RECORD 16").Cells(o, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 35) 'Shipper Item (3) Worksheets("RECORD 16").Cells(o, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 93, 35) End If 'mainly for record 21 CONSIGHEE FIELDS 'Sheet 4 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 21 Then p = p + 1 'B/L number Worksheets("RECORD 21").Cells(p, 1).Value = BLnumber 'consignee Code Worksheets("RECORD 21").Cells(p, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 7) 'consignee Item - (1) Worksheets("RECORD 21").Cells(p, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 23, 35) 'consignee Item - (2) Worksheets("RECORD 21").Cells(p, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 35) 'consignee Item - (3) Worksheets("RECORD 21").Cells(p, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 93, 35) End If 'Mainly for record 26 Notify Party feilds (multiple records) 'Sheet 4 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 26 Then q = q + 1 'B/L number Worksheets("RECORD 26").Cells(q, 1).Value = BLnumber 'Notify-I Worksheets("RECORD 26").Cells(q, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 1) '#Notify Code Worksheets("RECORD 26").Cells(q, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 7, 7) 'Notify Field-(1) Worksheets("RECORD 26").Cells(q, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 23, 35) 'Notify Field-(2) Worksheets("RECORD 26").Cells(q, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 35) 'Notify Field-(3) Worksheets("RECORD 26").Cells(q, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 93, 35) End If 'Mainly for record 41 Cargo Fields (Line Details) 'Sheet 5 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 41 Then r = r + 1 'B/L number Worksheets("RECORD 41").Cells(r, 1).Value = BLnumber 'Cargo sequence# Worksheets("RECORD 41").Cells(r, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 3) 'commodity code Worksheets("RECORD 41").Cells(r, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 9) 'No of packages/containers Worksheets("RECORD 41").Cells(r, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 19, 6) 'Package in words Worksheets("RECORD 41").Cells(r, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 28, 15) 'Cargo gross Weight Worksheets("RECORD 41").Cells(r, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 43, 10) 'Cargo Net weight Worksheets("RECORD 41").Cells(r, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 53, 10) 'cargo measurement Worksheets("RECORD 41").Cells(r, 8).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 63, 10) 'commodity name Worksheets("RECORD 41").Cells(r, 9).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 73, 128) End If 'Mainly for RECORD 44 - Cargo Marks And Nos (Multiple Records) 'Sheet6 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 44 Then s = s + 1 'B/L number Worksheets("RECORD 44").Cells(s, 1).Value = BLnumber 'Cargo Sequence No# Worksheets("RECORD 44").Cells(s, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 3) 'Commodity code Worksheets("RECORD 44").Cells(s, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 128) End If 'Mainly for RECORD 47 - Cargo Descriptions (Multiple Records) 'Sheet 7 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 47 Then t = t + 1 'B/L number Worksheets("RECORD 47").Cells(t, 1).Value = BLnumber 'Cargo Sequence No# Worksheets("RECORD 47").Cells(t, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 3) 'Cargo Description-(1) Worksheets("RECORD 47").Cells(t, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 30) 'Cargo Description-(2) Worksheets("RECORD 47").Cells(t, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 39, 30) 'Cargo Description-(3) Worksheets("RECORD 47").Cells(t, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 69, 30) 'Cargo Description-(4) Worksheets("RECORD 47").Cells(t, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 99, 30) End If 'Mainly for record 51 Container Field Record 'Sheet 8 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 51 Then j = j + 1 'B/L number Worksheets("RECORD 51").Cells(j, 1).Value = BLnumber 'Container number (51) Worksheets("RECORD 51").Cells(j, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 11) 'Container SOC (51) If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 20, 1) = "Y" Then Worksheets("RECORD 51").Cells(j, 3).Value = "Yes" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 20, 1) = "N" Then Worksheets("RECORD 51").Cells(j, 3).Value = "No" End If 'Seal No (51) Worksheets("RECORD 51").Cells(j, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 21, 10) 'Container Size (51) Worksheets("RECORD 51").Cells(j, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 31, 4) 'Cntr Loading Status (51) If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 36, 1) = "F" Then Worksheets("RECORD 51").Cells(j, 6).Value = "Full" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 36, 1) = "P" Then Worksheets("RECORD 51").Cells(j, 6).Value = "Part" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 36, 1) = "E" Then Worksheets("RECORD 51").Cells(j, 6).Value = "Empty" End If 'Cntr CY-CFS (51) Worksheets("RECORD 51").Cells(j, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 37, 10) 'Cntr No of Packages (51) Worksheets("RECORD 51").Cells(j, 8).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 47, 6) 'Cntr Kind of Packages (51) Worksheets("RECORD 51").Cells(j, 9).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 53, 8) 'Cntr Cargo Weight (51) Worksheets("RECORD 51").Cells(j, 10).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 61, 10) 'Cntr Tare Weight (51) Worksheets("RECORD 51").Cells(j, 11).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 71, 10) 'Cntr Cargo Measurement (51) Worksheets("RECORD 51").Cells(j, 12).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 81, 10) 'Seal No (51) Worksheets("RECORD 51").Cells(j, 13).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 91, 25) End If 'Mainly for record 61 Freight Charges Feild 'Sheet 9 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 61 Then u = u + 1 'B/L number Worksheets("RECORD 61").Cells(u, 1).Value = BLnumber 'Cargo Sequence No# Worksheets("RECORD 61").Cells(u, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 3, 3) 'Sequence No Worksheets("RECORD 61").Cells(u, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 2) 'Feight Charge Code Worksheets("RECORD 61").Cells(u, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 8, 4) 'Payable at (Port) Worksheets("RECORD 61").Cells(u, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 12, 5) 'Quantity Worksheets("RECORD 61").Cells(u, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 17, 8) 'Currency Worksheets("RECORD 61").Cells(u, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 25, 3) 'Rate of Feight Charges Worksheets("RECORD 61").Cells(u, 8).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 28, 13) 'Unit of Quantity Worksheets("RECORD 61").Cells(u, 9).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 41, 4) 'Amount Worksheets("RECORD 61").Cells(u, 10).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 45, 13) 'Sign of Amount Worksheets("RECORD 61").Cells(u, 11).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 1) 'Exchange Rate Worksheets("RECORD 61").Cells(u, 12).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 59, 10) 'Exch to Currency Code Worksheets("RECORD 61").Cells(u, 13).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 69, 3) 'Equivalent Worksheets("RECORD 61").Cells(u, 14).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 72, 13) 'Sign Of Equivalent Amount Worksheets("RECORD 61").Cells(u, 15).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 85, 1) 'Prepaid or Collect 'display words If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 86, 1) = "P" Then Worksheets("RECORD 61").Cells(u, 16).Value = "Prepaid" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 86, 1) = "C" Then Worksheets("RECORD 61").Cells(u, 16).Value = "Collect" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 86, 1) = "F" Then Worksheets("RECORD 61").Cells(u, 16).Value = "Foreign" End If 'Description oF Details Worksheets("RECORD 61").Cells(u, 17).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 30) 'Party Responsible to Pay If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 117, 1) = "S" Then Worksheets("RECORD 61").Cells(u, 18).Value = "Shipper" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 117, 1) = "C" Then Worksheets("RECORD 61").Cells(u, 18).Value = "Consignee" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 117, 1) = "N" Then Worksheets("RECORD 61").Cells(u, 18).Value = "Notify Party" End If 'Cntr Size Worksheets("RECORD 61").Cells(u, 19).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 118, 1) End If 'Mainly for record 72 Other Fields 'Sheet 10 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 72 Then v = v + 1 'B/L number Worksheets("RECORD 72").Cells(v, 1).Value = BLnumber 'Bill of Laden Text (1) Worksheets("RECORD 72").Cells(v, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 35) 'Bill of Laden Text (2) Worksheets("RECORD 72").Cells(v, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 41, 35) 'Bill of Laden Text (3) Worksheets("RECORD 72").Cells(v, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 76, 35) End If 'Mainly for record 74 Other Fields 'Sheet 11 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 74 Then w = w + 1 'B/L number Worksheets("RECORD 74").Cells(w, 1).Value = BLnumber 'Place of B/L Issue Worksheets("RECORD 74").Cells(w, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 5) 'Date Of Issue Worksheets("RECORD 74").Cells(w, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 11, 8) 'Perpaid at (Port Code) Worksheets("RECORD 74").Cells(w, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 41, 5) 'Payable at (Port Code) Worksheets("RECORD 74").Cells(w, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 46, 5) End If Next i ChDir "D:\Joss Blaze\temp" ActiveWorkbook.SaveAs Filename:="D:\Joss Blaze\temp\temp.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End Sub
|
|
#5
July 13th, 2004, 11:32 AM
|
|||
|
|||
|
Conversion of excel vba code to access vba
You should be able to convert it, but you will not be able to refer to
cells, since that concept doesn't exist in a database. You can build an array in memory and refer to elements of the array. You can also work on a line at a time by hunting down carraige returns Chr(13) and Line feeds Chr(10) and acting upon the data. My recommendation, though, would be to use the code in Excel that you know is already working properly, then import the finished data into Access. -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads: http://www.datastrat.com http://www.mvps.org/access "filnigeria" wrote in message ... I have a VBA code in excel that extracts data from a txt file that needs to be extracted character by character because there are different types of lines 16 CHINA FIRST AUTOMOBILE GROUP IMPORT AND EXPORT CORP. NO.99,DONGFENG STREET,THE PEOPLE'S 1 16 REPUBLIC OF CHINA 2 21 TO ORDER OF FOUNTAIN TRUST BANK PLC 1 26 1 WESTERN METAL PRODUCTS COMPANY LIMITED. 18,WEMPCO ROAD,PLOT 1A BLOCK D,OGBA SCHEME,IKEJA, LAGOS, 1 26 1 NIGERIA 2 41 001961590001 000004 CONTAINER 000131600000000000000000123980CONSOLIDATED CARGO 44 001N/M 47 001STC. 8 UNIT OF CA6472A GASOLINE RV STATION WAGON (4X2)WITH ACCESSORIES AND PARTS this is code that i wrote for excel to process it Can any anyone tell me if i can convertit for access or rewrite some of the code so that it will work in access Sub Jordan_Extraction_CAL() Dim i As Integer Dim j As Integer Dim K As Integer Dim m As Integer Dim n As Integer Dim o As Integer Dim p As Integer Dim q As Integer Dim r As Integer Dim s As Integer Dim t As Integer Dim u As Integer Dim v As Integer Dim w As Integer Dim x As Integer Dim y As Integer Dim BLNO As String Dim vessCODE As String Dim VOYno As String Dim CargoWeight As Long Dim ContainerTareWeight As Long j = 4 m = 4 n = 4 o = 4 p = 4 q = 4 r = 4 s = 4 t = 4 u = 4 v = 4 w = 4 x = 4 y = 4 For i = 1 To 5000 'Mainly for the name of the file If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 11 Then vessCODE = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 11, 3) VOYno = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 34, 8) End If 'Mainly for record 12 FIRST RECORD OF 1 B/L 'sheet 1 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 12 Then m = m + 1 BLnumber = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 98, 17) 'B/L number Worksheets("RECORD 12").Cells(m, 1).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 17) 'Pre-vessel Code Worksheets("RECORD 12").Cells(m, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 37, 3) 'Pre-vessel Name Worksheets("RECORD 12").Cells(m, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 40, 20) 'Pre-voyage No Worksheets("RECORD 12").Cells(m, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 60, 8) 'Port Of Discharge Worksheets("RECORD 12").Cells(m, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 68, 5) 'Port of Loading Worksheets("RECORD 12").Cells(m, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 73, 5) 'BL CY-CFS items Worksheets("RECORD 12").Cells(m, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 78, 9) 'BL Prepaid/Collect 'display words If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 1) = "P" Then Worksheets("RECORD 12").Cells(m, 8).Value = "Prepaid" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 1) = "C" Then Worksheets("RECORD 12").Cells(m, 8).Value = "Collect" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 1) = "F" Then Worksheets("RECORD 12").Cells(m, 8).Value = "Foreign" End If 'Tranship-ID If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 88, 1) = "Y" Then Worksheets("RECORD 12").Cells(m, 9).Value = "Yes" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 88, 1) = "N" Then Worksheets("RECORD 12").Cells(m, 9).Value = "No" End If 'BL all empty ctn-ID If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 89, 1) = "Y" Then Worksheets("RECORD 12").Cells(m, 10).Value = "Yes" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 89, 1) = "N" Then Worksheets("RECORD 12").Cells(m, 10).Value = "No" End If 'Loading date Worksheets("RECORD 12").Cells(m, 11).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 90, 8) 'Orginal BL Worksheets("RECORD 12").Cells(m, 12).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 98, 17) 'Port Of Issue (Orig BL) Worksheets("RECORD 12").Cells(m, 13).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 115, 5) 'Pre-voyage arrival date Worksheets("RECORD 12").Cells(m, 14).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 120, 8) End If 'Mainly for record 13 Send Record of 1 B/L 'Sheet 2 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 13 Then n = n + 1 'B/L number Worksheets("RECORD 13").Cells(n, 1).Value = BLnumber 'Port of Origin Worksheets("RECORD 13").Cells(n, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 5) 'Port of Discharge Worksheets("RECORD 13").Cells(n, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 11, 5) 'Final Destination (Port Code) Worksheets("RECORD 13").Cells(n, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 21, 5) 'Final Destination (Name) Worksheets("RECORD 13").Cells(n, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 26, 20) End If 'Mainly for record 16 Shippers Feild 'Sheet 3 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 16 Then o = o + 1 'B/L number Worksheets("RECORD 16").Cells(o, 1).Value = BLnumber '#shippers code Worksheets("RECORD 16").Cells(o, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 7) 'Shipper Item (1) Worksheets("RECORD 16").Cells(o, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 23, 35) 'Shipper Item (2) Worksheets("RECORD 16").Cells(o, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 35) 'Shipper Item (3) Worksheets("RECORD 16").Cells(o, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 93, 35) End If 'mainly for record 21 CONSIGHEE FIELDS 'Sheet 4 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 21 Then p = p + 1 'B/L number Worksheets("RECORD 21").Cells(p, 1).Value = BLnumber 'consignee Code Worksheets("RECORD 21").Cells(p, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 7) 'consignee Item - (1) Worksheets("RECORD 21").Cells(p, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 23, 35) 'consignee Item - (2) Worksheets("RECORD 21").Cells(p, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 35) 'consignee Item - (3) Worksheets("RECORD 21").Cells(p, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 93, 35) End If 'Mainly for record 26 Notify Party feilds (multiple records) 'Sheet 4 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 26 Then q = q + 1 'B/L number Worksheets("RECORD 26").Cells(q, 1).Value = BLnumber 'Notify-I Worksheets("RECORD 26").Cells(q, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 1) '#Notify Code Worksheets("RECORD 26").Cells(q, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 7, 7) 'Notify Field-(1) Worksheets("RECORD 26").Cells(q, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 23, 35) 'Notify Field-(2) Worksheets("RECORD 26").Cells(q, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 35) 'Notify Field-(3) Worksheets("RECORD 26").Cells(q, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 93, 35) End If 'Mainly for record 41 Cargo Fields (Line Details) 'Sheet 5 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 41 Then r = r + 1 'B/L number Worksheets("RECORD 41").Cells(r, 1).Value = BLnumber 'Cargo sequence# Worksheets("RECORD 41").Cells(r, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 3) 'commodity code Worksheets("RECORD 41").Cells(r, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 9) 'No of packages/containers Worksheets("RECORD 41").Cells(r, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 19, 6) 'Package in words Worksheets("RECORD 41").Cells(r, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 28, 15) 'Cargo gross Weight Worksheets("RECORD 41").Cells(r, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 43, 10) 'Cargo Net weight Worksheets("RECORD 41").Cells(r, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 53, 10) 'cargo measurement Worksheets("RECORD 41").Cells(r, 8).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 63, 10) 'commodity name Worksheets("RECORD 41").Cells(r, 9).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 73, 128) End If 'Mainly for RECORD 44 - Cargo Marks And Nos (Multiple Records) 'Sheet6 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 44 Then s = s + 1 'B/L number Worksheets("RECORD 44").Cells(s, 1).Value = BLnumber 'Cargo Sequence No# Worksheets("RECORD 44").Cells(s, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 3) 'Commodity code Worksheets("RECORD 44").Cells(s, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 128) End If 'Mainly for RECORD 47 - Cargo Descriptions (Multiple Records) 'Sheet 7 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 47 Then t = t + 1 'B/L number Worksheets("RECORD 47").Cells(t, 1).Value = BLnumber 'Cargo Sequence No# Worksheets("RECORD 47").Cells(t, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 3) 'Cargo Description-(1) Worksheets("RECORD 47").Cells(t, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 30) 'Cargo Description-(2) Worksheets("RECORD 47").Cells(t, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 39, 30) 'Cargo Description-(3) Worksheets("RECORD 47").Cells(t, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 69, 30) 'Cargo Description-(4) Worksheets("RECORD 47").Cells(t, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 99, 30) End If 'Mainly for record 51 Container Field Record 'Sheet 8 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 51 Then j = j + 1 'B/L number Worksheets("RECORD 51").Cells(j, 1).Value = BLnumber 'Container number (51) Worksheets("RECORD 51").Cells(j, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 11) 'Container SOC (51) If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 20, 1) = "Y" Then Worksheets("RECORD 51").Cells(j, 3).Value = "Yes" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 20, 1) = "N" Then Worksheets("RECORD 51").Cells(j, 3).Value = "No" End If 'Seal No (51) Worksheets("RECORD 51").Cells(j, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 21, 10) 'Container Size (51) Worksheets("RECORD 51").Cells(j, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 31, 4) 'Cntr Loading Status (51) If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 36, 1) = "F" Then Worksheets("RECORD 51").Cells(j, 6).Value = "Full" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 36, 1) = "P" Then Worksheets("RECORD 51").Cells(j, 6).Value = "Part" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 36, 1) = "E" Then Worksheets("RECORD 51").Cells(j, 6).Value = "Empty" End If 'Cntr CY-CFS (51) Worksheets("RECORD 51").Cells(j, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 37, 10) 'Cntr No of Packages (51) Worksheets("RECORD 51").Cells(j, 8).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 47, 6) 'Cntr Kind of Packages (51) Worksheets("RECORD 51").Cells(j, 9).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 53, 8) 'Cntr Cargo Weight (51) Worksheets("RECORD 51").Cells(j, 10).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 61, 10) 'Cntr Tare Weight (51) Worksheets("RECORD 51").Cells(j, 11).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 71, 10) 'Cntr Cargo Measurement (51) Worksheets("RECORD 51").Cells(j, 12).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 81, 10) 'Seal No (51) Worksheets("RECORD 51").Cells(j, 13).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 91, 25) End If 'Mainly for record 61 Freight Charges Feild 'Sheet 9 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 61 Then u = u + 1 'B/L number Worksheets("RECORD 61").Cells(u, 1).Value = BLnumber 'Cargo Sequence No# Worksheets("RECORD 61").Cells(u, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 3, 3) 'Sequence No Worksheets("RECORD 61").Cells(u, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 2) 'Feight Charge Code Worksheets("RECORD 61").Cells(u, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 8, 4) 'Payable at (Port) Worksheets("RECORD 61").Cells(u, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 12, 5) 'Quantity Worksheets("RECORD 61").Cells(u, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 17, 8) 'Currency Worksheets("RECORD 61").Cells(u, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 25, 3) 'Rate of Feight Charges Worksheets("RECORD 61").Cells(u, 8).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 28, 13) 'Unit of Quantity Worksheets("RECORD 61").Cells(u, 9).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 41, 4) 'Amount Worksheets("RECORD 61").Cells(u, 10).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 45, 13) 'Sign of Amount Worksheets("RECORD 61").Cells(u, 11).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 1) 'Exchange Rate Worksheets("RECORD 61").Cells(u, 12).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 59, 10) 'Exch to Currency Code Worksheets("RECORD 61").Cells(u, 13).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 69, 3) 'Equivalent Worksheets("RECORD 61").Cells(u, 14).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 72, 13) 'Sign Of Equivalent Amount Worksheets("RECORD 61").Cells(u, 15).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 85, 1) 'Prepaid or Collect 'display words If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 86, 1) = "P" Then Worksheets("RECORD 61").Cells(u, 16).Value = "Prepaid" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 86, 1) = "C" Then Worksheets("RECORD 61").Cells(u, 16).Value = "Collect" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 86, 1) = "F" Then Worksheets("RECORD 61").Cells(u, 16).Value = "Foreign" End If 'Description oF Details Worksheets("RECORD 61").Cells(u, 17).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 30) 'Party Responsible to Pay If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 117, 1) = "S" Then Worksheets("RECORD 61").Cells(u, 18).Value = "Shipper" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 117, 1) = "C" Then Worksheets("RECORD 61").Cells(u, 18).Value = "Consignee" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 117, 1) = "N" Then Worksheets("RECORD 61").Cells(u, 18).Value = "Notify Party" End If 'Cntr Size Worksheets("RECORD 61").Cells(u, 19).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 118, 1) End If 'Mainly for record 72 Other Fields 'Sheet 10 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 72 Then v = v + 1 'B/L number Worksheets("RECORD 72").Cells(v, 1).Value = BLnumber 'Bill of Laden Text (1) Worksheets("RECORD 72").Cells(v, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 35) 'Bill of Laden Text (2) Worksheets("RECORD 72").Cells(v, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 41, 35) 'Bill of Laden Text (3) Worksheets("RECORD 72").Cells(v, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 76, 35) End If 'Mainly for record 74 Other Fields 'Sheet 11 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 74 Then w = w + 1 'B/L number Worksheets("RECORD 74").Cells(w, 1).Value = BLnumber 'Place of B/L Issue Worksheets("RECORD 74").Cells(w, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 5) 'Date Of Issue Worksheets("RECORD 74").Cells(w, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 11, 8) 'Perpaid at (Port Code) Worksheets("RECORD 74").Cells(w, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 41, 5) 'Payable at (Port Code) Worksheets("RECORD 74").Cells(w, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 46, 5) End If Next i ChDir "D:\Joss Blaze\temp" ActiveWorkbook.SaveAs Filename:="D:\Joss Blaze\temp\temp.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End Sub
|
|
#6
July 15th, 2004, 02:23 AM
|
|||
|
|||
|
Conversion of excel vba code to access vba
Here's an example of Excel automation:
http://www.mvps.org/access/modules/mdl0006.htm -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads: http://www.datastrat.com http://www.mvps.org/access "filnigeria" wrote in message ... Alrite but is the anyway to open excel from within access so that i only have to open one application instead of two thanks "Arvin Meyer" wrote in message ... You should be able to convert it, but you will not be able to refer to cells, since that concept doesn't exist in a database. You can build an array in memory and refer to elements of the array. You can also work on a line at a time by hunting down carraige returns Chr(13) and Line feeds Chr(10) and acting upon the data. My recommendation, though, would be to use the code in Excel that you know is already working properly, then import the finished data into Access. -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads: http://www.datastrat.com http://www.mvps.org/access "filnigeria" wrote in message ... I have a VBA code in excel that extracts data from a txt file that needs to be extracted character by character because there are different types of lines 16 CHINA FIRST AUTOMOBILE GROUP IMPORT AND EXPORT CORP. NO.99,DONGFENG STREET,THE PEOPLE'S 1 16 REPUBLIC OF CHINA 2 21 TO ORDER OF FOUNTAIN TRUST BANK PLC 1 26 1 WESTERN METAL PRODUCTS COMPANY LIMITED. 18,WEMPCO ROAD,PLOT 1A BLOCK D,OGBA SCHEME,IKEJA, LAGOS, 1 26 1 NIGERIA 2 41 001961590001 000004 CONTAINER 000131600000000000000000123980CONSOLIDATED CARGO 44 001N/M 47 001STC. 8 UNIT OF CA6472A GASOLINE RV STATION WAGON (4X2)WITH ACCESSORIES AND PARTS this is code that i wrote for excel to process it Can any anyone tell me if i can convertit for access or rewrite some of the code so that it will work in access Sub Jordan_Extraction_CAL() Dim i As Integer Dim j As Integer Dim K As Integer Dim m As Integer Dim n As Integer Dim o As Integer Dim p As Integer Dim q As Integer Dim r As Integer Dim s As Integer Dim t As Integer Dim u As Integer Dim v As Integer Dim w As Integer Dim x As Integer Dim y As Integer Dim BLNO As String Dim vessCODE As String Dim VOYno As String Dim CargoWeight As Long Dim ContainerTareWeight As Long j = 4 m = 4 n = 4 o = 4 p = 4 q = 4 r = 4 s = 4 t = 4 u = 4 v = 4 w = 4 x = 4 y = 4 For i = 1 To 5000 'Mainly for the name of the file If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 11 Then vessCODE = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 11, 3) VOYno = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 34, 8) End If 'Mainly for record 12 FIRST RECORD OF 1 B/L 'sheet 1 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 12 Then m = m + 1 BLnumber = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 98, 17) 'B/L number Worksheets("RECORD 12").Cells(m, 1).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 17) 'Pre-vessel Code Worksheets("RECORD 12").Cells(m, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 37, 3) 'Pre-vessel Name Worksheets("RECORD 12").Cells(m, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 40, 20) 'Pre-voyage No Worksheets("RECORD 12").Cells(m, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 60, 8) 'Port Of Discharge Worksheets("RECORD 12").Cells(m, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 68, 5) 'Port of Loading Worksheets("RECORD 12").Cells(m, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 73, 5) 'BL CY-CFS items Worksheets("RECORD 12").Cells(m, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 78, 9) 'BL Prepaid/Collect 'display words If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 1) = "P" Then Worksheets("RECORD 12").Cells(m, 8).Value = "Prepaid" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 1) = "C" Then Worksheets("RECORD 12").Cells(m, 8).Value = "Collect" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 1) = "F" Then Worksheets("RECORD 12").Cells(m, 8).Value = "Foreign" End If 'Tranship-ID If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 88, 1) = "Y" Then Worksheets("RECORD 12").Cells(m, 9).Value = "Yes" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 88, 1) = "N" Then Worksheets("RECORD 12").Cells(m, 9).Value = "No" End If 'BL all empty ctn-ID If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 89, 1) = "Y" Then Worksheets("RECORD 12").Cells(m, 10).Value = "Yes" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 89, 1) = "N" Then Worksheets("RECORD 12").Cells(m, 10).Value = "No" End If 'Loading date Worksheets("RECORD 12").Cells(m, 11).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 90, 8) 'Orginal BL Worksheets("RECORD 12").Cells(m, 12).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 98, 17) 'Port Of Issue (Orig BL) Worksheets("RECORD 12").Cells(m, 13).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 115, 5) 'Pre-voyage arrival date Worksheets("RECORD 12").Cells(m, 14).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 120, 8) End If 'Mainly for record 13 Send Record of 1 B/L 'Sheet 2 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 13 Then n = n + 1 'B/L number Worksheets("RECORD 13").Cells(n, 1).Value = BLnumber 'Port of Origin Worksheets("RECORD 13").Cells(n, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 5) 'Port of Discharge Worksheets("RECORD 13").Cells(n, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 11, 5) 'Final Destination (Port Code) Worksheets("RECORD 13").Cells(n, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 21, 5) 'Final Destination (Name) Worksheets("RECORD 13").Cells(n, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 26, 20) End If 'Mainly for record 16 Shippers Feild 'Sheet 3 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 16 Then o = o + 1 'B/L number Worksheets("RECORD 16").Cells(o, 1).Value = BLnumber '#shippers code Worksheets("RECORD 16").Cells(o, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 7) 'Shipper Item (1) Worksheets("RECORD 16").Cells(o, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 23, 35) 'Shipper Item (2) Worksheets("RECORD 16").Cells(o, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 35) 'Shipper Item (3) Worksheets("RECORD 16").Cells(o, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 93, 35) End If 'mainly for record 21 CONSIGHEE FIELDS 'Sheet 4 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 21 Then p = p + 1 'B/L number Worksheets("RECORD 21").Cells(p, 1).Value = BLnumber 'consignee Code Worksheets("RECORD 21").Cells(p, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 7) 'consignee Item - (1) Worksheets("RECORD 21").Cells(p, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 23, 35) 'consignee Item - (2) Worksheets("RECORD 21").Cells(p, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 35) 'consignee Item - (3) Worksheets("RECORD 21").Cells(p, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 93, 35) End If 'Mainly for record 26 Notify Party feilds (multiple records) 'Sheet 4 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 26 Then q = q + 1 'B/L number Worksheets("RECORD 26").Cells(q, 1).Value = BLnumber 'Notify-I Worksheets("RECORD 26").Cells(q, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 1) '#Notify Code Worksheets("RECORD 26").Cells(q, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 7, 7) 'Notify Field-(1) Worksheets("RECORD 26").Cells(q, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 23, 35) 'Notify Field-(2) Worksheets("RECORD 26").Cells(q, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 35) 'Notify Field-(3) Worksheets("RECORD 26").Cells(q, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 93, 35) End If 'Mainly for record 41 Cargo Fields (Line Details) 'Sheet 5 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 41 Then r = r + 1 'B/L number Worksheets("RECORD 41").Cells(r, 1).Value = BLnumber 'Cargo sequence# Worksheets("RECORD 41").Cells(r, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 3) 'commodity code Worksheets("RECORD 41").Cells(r, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 9) 'No of packages/containers Worksheets("RECORD 41").Cells(r, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 19, 6) 'Package in words Worksheets("RECORD 41").Cells(r, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 28, 15) 'Cargo gross Weight Worksheets("RECORD 41").Cells(r, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 43, 10) 'Cargo Net weight Worksheets("RECORD 41").Cells(r, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 53, 10) 'cargo measurement Worksheets("RECORD 41").Cells(r, 8).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 63, 10) 'commodity name Worksheets("RECORD 41").Cells(r, 9).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 73, 128) End If 'Mainly for RECORD 44 - Cargo Marks And Nos (Multiple Records) 'Sheet6 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 44 Then s = s + 1 'B/L number Worksheets("RECORD 44").Cells(s, 1).Value = BLnumber 'Cargo Sequence No# Worksheets("RECORD 44").Cells(s, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 3) 'Commodity code Worksheets("RECORD 44").Cells(s, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 128) End If 'Mainly for RECORD 47 - Cargo Descriptions (Multiple Records) 'Sheet 7 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 47 Then t = t + 1 'B/L number Worksheets("RECORD 47").Cells(t, 1).Value = BLnumber 'Cargo Sequence No# Worksheets("RECORD 47").Cells(t, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 3) 'Cargo Description-(1) Worksheets("RECORD 47").Cells(t, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 30) 'Cargo Description-(2) Worksheets("RECORD 47").Cells(t, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 39, 30) 'Cargo Description-(3) Worksheets("RECORD 47").Cells(t, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 69, 30) 'Cargo Description-(4) Worksheets("RECORD 47").Cells(t, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 99, 30) End If 'Mainly for record 51 Container Field Record 'Sheet 8 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 51 Then j = j + 1 'B/L number Worksheets("RECORD 51").Cells(j, 1).Value = BLnumber 'Container number (51) Worksheets("RECORD 51").Cells(j, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 9, 11) 'Container SOC (51) If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 20, 1) = "Y" Then Worksheets("RECORD 51").Cells(j, 3).Value = "Yes" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 20, 1) = "N" Then Worksheets("RECORD 51").Cells(j, 3).Value = "No" End If 'Seal No (51) Worksheets("RECORD 51").Cells(j, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 21, 10) 'Container Size (51) Worksheets("RECORD 51").Cells(j, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 31, 4) 'Cntr Loading Status (51) If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 36, 1) = "F" Then Worksheets("RECORD 51").Cells(j, 6).Value = "Full" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 36, 1) = "P" Then Worksheets("RECORD 51").Cells(j, 6).Value = "Part" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 36, 1) = "E" Then Worksheets("RECORD 51").Cells(j, 6).Value = "Empty" End If 'Cntr CY-CFS (51) Worksheets("RECORD 51").Cells(j, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 37, 10) 'Cntr No of Packages (51) Worksheets("RECORD 51").Cells(j, 8).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 47, 6) 'Cntr Kind of Packages (51) Worksheets("RECORD 51").Cells(j, 9).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 53, 8) 'Cntr Cargo Weight (51) Worksheets("RECORD 51").Cells(j, 10).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 61, 10) 'Cntr Tare Weight (51) Worksheets("RECORD 51").Cells(j, 11).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 71, 10) 'Cntr Cargo Measurement (51) Worksheets("RECORD 51").Cells(j, 12).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 81, 10) 'Seal No (51) Worksheets("RECORD 51").Cells(j, 13).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 91, 25) End If 'Mainly for record 61 Freight Charges Feild 'Sheet 9 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 61 Then u = u + 1 'B/L number Worksheets("RECORD 61").Cells(u, 1).Value = BLnumber 'Cargo Sequence No# Worksheets("RECORD 61").Cells(u, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 3, 3) 'Sequence No Worksheets("RECORD 61").Cells(u, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 2) 'Feight Charge Code Worksheets("RECORD 61").Cells(u, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 8, 4) 'Payable at (Port) Worksheets("RECORD 61").Cells(u, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 12, 5) 'Quantity Worksheets("RECORD 61").Cells(u, 6).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 17, 8) 'Currency Worksheets("RECORD 61").Cells(u, 7).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 25, 3) 'Rate of Feight Charges Worksheets("RECORD 61").Cells(u, 8).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 28, 13) 'Unit of Quantity Worksheets("RECORD 61").Cells(u, 9).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 41, 4) 'Amount Worksheets("RECORD 61").Cells(u, 10).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 45, 13) 'Sign of Amount Worksheets("RECORD 61").Cells(u, 11).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 58, 1) 'Exchange Rate Worksheets("RECORD 61").Cells(u, 12).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 59, 10) 'Exch to Currency Code Worksheets("RECORD 61").Cells(u, 13).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 69, 3) 'Equivalent Worksheets("RECORD 61").Cells(u, 14).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 72, 13) 'Sign Of Equivalent Amount Worksheets("RECORD 61").Cells(u, 15).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 85, 1) 'Prepaid or Collect 'display words If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 86, 1) = "P" Then Worksheets("RECORD 61").Cells(u, 16).Value = "Prepaid" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 86, 1) = "C" Then Worksheets("RECORD 61").Cells(u, 16).Value = "Collect" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 86, 1) = "F" Then Worksheets("RECORD 61").Cells(u, 16).Value = "Foreign" End If 'Description oF Details Worksheets("RECORD 61").Cells(u, 17).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 87, 30) 'Party Responsible to Pay If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 117, 1) = "S" Then Worksheets("RECORD 61").Cells(u, 18).Value = "Shipper" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 117, 1) = "C" Then Worksheets("RECORD 61").Cells(u, 18).Value = "Consignee" End If If Mid(Worksheets("Raw Data").Cells(i, 1).Value, 117, 1) = "N" Then Worksheets("RECORD 61").Cells(u, 18).Value = "Notify Party" End If 'Cntr Size Worksheets("RECORD 61").Cells(u, 19).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 118, 1) End If 'Mainly for record 72 Other Fields 'Sheet 10 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 72 Then v = v + 1 'B/L number Worksheets("RECORD 72").Cells(v, 1).Value = BLnumber 'Bill of Laden Text (1) Worksheets("RECORD 72").Cells(v, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 35) 'Bill of Laden Text (2) Worksheets("RECORD 72").Cells(v, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 41, 35) 'Bill of Laden Text (3) Worksheets("RECORD 72").Cells(v, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 76, 35) End If 'Mainly for record 74 Other Fields 'Sheet 11 If Left((Worksheets("Raw Data").Cells(i, 1).Value), 2) = 74 Then w = w + 1 'B/L number Worksheets("RECORD 74").Cells(w, 1).Value = BLnumber 'Place of B/L Issue Worksheets("RECORD 74").Cells(w, 2).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 6, 5) 'Date Of Issue Worksheets("RECORD 74").Cells(w, 3).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 11, 8) 'Perpaid at (Port Code) Worksheets("RECORD 74").Cells(w, 4).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 41, 5) 'Payable at (Port Code) Worksheets("RECORD 74").Cells(w, 5).Value = Mid(Worksheets("Raw Data").Cells(i, 1).Value, 46, 5) End If Next i ChDir "D:\Joss Blaze\temp" ActiveWorkbook.SaveAs Filename:="D:\Joss Blaze\temp\temp.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End Sub
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| access ...excel and infopath integration | trey braid | Using Forms | 0 | June 14th, 2004 11:28 PM |
| Using Excel sheet for an Access form | Yannick | Using Forms | 1 | June 8th, 2004 08:12 PM |
| Need help changing a excel sheet to a access DB, NOT Linked | Greg | Database Design | 3 | May 15th, 2004 01:52 AM |
| Import Excel into Access | VKG | Worksheet Functions | 9 | March 19th, 2004 08:46 AM |
| Unable to access excel sheet on server from excel | Bob Fisher | Links and Linking | 0 | September 19th, 2003 05:18 PM |
Linear Mode
