macro writing

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following macro I wrote in Microsoft Excel VB to seperate out the
2 parts of a postcode. I want to be able to do this in an access database.
Can anyone tell me what I need to do? I have only just started using Excel VB
and have never used it in access so baby steps please.

Thanks


Sub PCodeSep()

Dim FullCode As String, First As String, Second As String, pos As Integer, i
As_ Integer
Dim number As Integer
number = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To number
FullCode = Cells(i, 1)
pos = InStr(FullCode, " ")
First = Left(FullCode, pos - 1)
Second = Mid(FullCode, pos + 1)
Cells(i, 2) = First
Cells(i, 3) = Second
Next i
End Sub
 
ACCESS macros are not VBA code -- it's unfortunate that other OFFICE
programs use macro to mean VBA, but in ACCESS we refer to VBA as..... VBA.

You don't say if this code is to run in ACCESS while ACCESS is automating
EXCEL, or if you just want to perform the code's function in ACCESS using a
value that you pass to it. More information please.
 
I just want to perform the codes function in ACCESS using a value that will
be passed in. My database has many columns - the first 4 are:

Name, Address, Town, Postcode.....rest of columns

I then want to create another table that will read like:

Name, Address, Town, 1st half if postcode, 2nd half of postcode.....rest of
columns

Unfortunately the data is too large to copy into excel and manipulate.

Thanks in advance.
 
Do you need to store the data that you parse from the PostCode data? You
could do that via a query that uses the original data.

Provide some examples of data for what you start with, what you want as the
output, and what you actually need to store. Your request will involve at
least one table in ACCESS (will the original data reside in ACCESS, or are
you linking to another data source such as a text file or EXCEL file?), but
exactly what and how is dependent on your needs.

Parsing string data is fairly easy to do in VBA, if you can "identify" how
to separate the data. Your VBA code from EXCEL indeed uses the parsing
strings.

On the possibility that you want to do the parsing and pass the results back
from the subroutine, you could change it to this:

Sub PCodeSep(FullCode As String, First As String, Second As String)
Dim pos As Integer
pos = InStr(FullCode, " ")
First = Left(FullCode, pos - 1)
Second = Mid(FullCode, pos + 1)
End Sub


Or if you wanted a function that returned either the First or Second string:

Function PCodeSep(FullCode As String, intFirstSecond As Integer) As String
Dim pos As Integer
pos = InStr(FullCode, " ")
Select Case intFirstSecond
Case 1 ' First value
PCodeSep = Left(FullCode, pos - 1)
Case 2 ' Second value
PCodeSep = Mid(FullCode, pos + 1)
End Select
End Function

--

Ken Snell
<MS ACCESS MVP>
 
Back
Top