I received an interesting question yesterday from a potential customer.
They are migrating all their Oracle PeopleSoft data to SAP.
(They are exporting from PeopleSoft into Excel, importing it into Access to tweak it, and exporting to csv from Access to upload to SAP)
Basically, how to take 1 (PeopleSoft) row and make it into multiple rows (SAP)?
For example:
PeopleSoft format: A B -200 0 500
to SAP format:
A B -200 0 0
A B 0 0 0
A B 0 0 500
So I decided to make a blog post on it so others may benefit:
Here is what the source would look like:
And here is what the result would look like:
…and then we’ll save the table to a csv file to upload to SAP.
Here is the code to get the data into the tblSAP:
Sub SAPFormat()
Dim rst As Recordset
Dim strInsSQL As String
Dim intCounter As Integer
Dim dblNegative As Double
Dim dblZero As Double
Dim dblPositive As Double
Set rst = CurrentDb.OpenRecordset("SELECT Col1, Number1,Number2,Number3 FROM tblPeopleSoft")
CurrentDb.Execute "DELETE * FROM tblSAP"
rst.MoveFirst
Do Until rst.EOF
For intCounter = 1 To 3
'reset the variables
dblNegative = 0
dblZero = 0
dblPositive = 0
'evaluate the numbers
Select Case intCounter
Case 1 'evaluate number 1
If rst("Number1") < 0 Then
dblNegative = rst("Number1")
dblZero = 0
dblPositive = 0
End If
If rst("Number1") = 0 Then
dblNegative = 0
dblZero = rst("Number1")
dblPositive = 0
End If
If rst("Number1") > 0 Then
dblNegative = 0
dblZero = 0
dblPositive = rst("Number1")
End If
'stay in the loop but insert the record
GoTo InsertThis
Case 2 'evaluate number 2
If rst("Number2") < 0 Then
dblNegative = rst("Number2")
dblZero = 0
dblPositive = 0
End If
If rst("Number2") = 0 Then
dblNegative = 0
dblZero = rst("Number2")
dblPositive = 0
End If
If rst("Number2") > 0 Then
dblNegative = 0
dblZero = 0
dblPositive = rst("Number2")
End If
'stay in the loop but insert the record
GoTo InsertThis
Case 3 'evaluate number 3
If rst("Number3") < 0 Then
dblNegative = rst("Number3")
dblZero = 0
dblPositive = 0
End If
If rst("Number3") = 0 Then
dblNegative = 0
dblZero = rst("Number3")
dblPositive = 0
End If
If rst("Number3") > 0 Then
dblNegative = 0
dblZero = 0
dblPositive = rst("Number3")
End If
'stay in the loop but insert the record
GoTo InsertThis
End Select
'label to insert the record
InsertThis:
strInsSQL = "INSERT INTO tblSAP ( Col1, Negative, Zero, Positive ) "
strInsSQL = strInsSQL & " VALUES ("
strInsSQL = strInsSQL & "'" & rst("Col1") & "',"
strInsSQL = strInsSQL & dblNegative & ","
strInsSQL = strInsSQL & dblZero & ","
strInsSQL = strInsSQL & dblPositive & ")"
CurrentDb.Execute strInsSQL
Next intCounter
rst.MoveNext
Loop
'clean up
rst.Close
Set rst = Nothing
End Sub
Please let me know if you find some fault in my logic.
How to Fix Run Time Error 1004 in Excel
If you work with Microsoft Excel frequently, chances ar ling for a solution. Fortunately, this error is well-documented, and there are several ways to resolve it. In this article, we’ll explore the causes of run time error 1004, practical steps to fix it, and preventive measures to reduce the chances of it happening again. What […]
How To Parse A Flat File In Excel VBA
In another post I demonstrated how to access a file on your computer using the MS Office Library. Here it is if you don’t know what I’m talking about. In this post, I am going to show you how to access the file and load it into your spreadsheet. I will do the same thing […]
How to pick a file to load In VBA
How to Pick a File in VBA: FileDialog & GetOpenFilename Explained When building Excel VBA applications, you’ll often need to let users pick a file to load in VBA. Instead of hard-coding file paths, you can use built-in dialogs that make file selection easy and user-friendly. VBA offers two main approaches: FileDialog object (flexible, customizable) […]
How can I interact with other Office applications (Excel) using VBA in Access?
Need to write your Access data or query to an Excel file? Here is the how to do it: Most people are familiar with Excel and know how to use it well (enough), and when you start talking about Access, they get scared off, and don’t know what to do anymore. Well, here you are […]
Support these sponsors: