Conversion of excel vba code to access vba

F

filnigeria

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
 
F

filnigeria

Alrite but is the anyway to open excel from within access so that i only
have to open one application instead of two

thanks
 
A

Arvin Meyer

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

5941 The requested member of the collection does not exist...Please help! 0
Excel VBA 1
Importing from a text file 3
Copy a range data from 1 sheet to multiple selected sheets 1
slow code with many loopings 6
VBA Excel - Select a value from one cell in Excel to update a SQL table 1
fill in data that is not empty 1
Excel 2013 Merge Data 2

Top