Importing from excel

  • Thread starter Thread starter Tony in Michigan
  • Start date Start date
T

Tony in Michigan

Hello,
I have multiple weekly part change spreadsheets that I need to import into
access for manipulation.

There are four columns
Description: Short text sentence

New number: parts number/s in prefix-base-suffix form, cell contains from
one to the theoretical limit of the cell. They are seperated by spaces,
sometimes <CR> or other "hard" seperator

Supplier code: 5 char code

Supplier name: Short text sentense.

I need to import these spreadsheets into access, Explode the new number
field, placing one part per record and copy the other three fields to the new
record.

A former co-worker wrote VBA in excel to do this, for space seperated parts.
unfortunatly, he is gone, and I still need to bring it into access to
aggregate, clean and query against other data. How would I do this in access?


Below is the excel code.

Function Explode()

Dim strRow As String
Dim strNewNumber1 As String
Dim strNewNumber2 As String
Dim intPosition As Integer

strRow = 2

Do Until Range("B" & strRow).Value = ""

Range("B" & strRow).Select
strNewNumber1 = Trim(Range("B" & strRow).Value)
If InStr(strNewNumber1, " ") > 0 Then

intPosition = InStr(strNewNumber1, " ")
strNewNumber2 = Left(strNewNumber1, intPosition - 1)
strNewNumber1 = Trim(Mid(strNewNumber1, intPosition))

Rows(strRow & ":" & strRow).Select
Selection.Copy
Rows(strRow + 1 & ":" & strRow + 1).Select
Selection.Insert Shift:=xlDown

Range("B" & strRow).Value = strNewNumber2
Range("B" & strRow + 1).Value = strNewNumber1

End If

strRow = strRow + 1

Loop


End Function
 
If it were I, I would create an interim table in ACCESS to hold the original
data from the spreadsheet (no data manipulation to be done for the import).

Run a delete query on the table, and then import the spreadsheet's data into
the table (using TransferSpreadsheet) --- see the web page in my signature
for various examples).

Then use an append query to read the data from the interim table, with
calculated field(s) to "explode" the "new number" field into its separate
components, and let the append query add the data to your permanent table.

This process could be automated via macro or VBA code. If you need
assistance with how to write calculated field(s) to "explode" the part
number information, post examples of the "new number" data and how you want
to explode / parse out the correct/needed data.
 
Back
Top