Jerry,
You could use a VBA macro
Below is an example from an MS Access app we use.
The example uses a connection based on:
Visual Basic for Applications
Microsoft Access 12.0 Object libary
Microsoft Office 12.0 Object library
Microsoft Office 12.0 Access database engine Objects
Microsoft ActiveX Data Objects 2.8 library
IBMDA400 - standard OLE DB
There will be differences because you are using Excel,
but the MC Press book:
i5/OS and Microsoft Office Integration Handbook
can help
Public Function fcnUploadPayPeriods() As Long
'Transfer Pay Periods to Host server
Dim cmdHost As New ADODB.Command
Dim cnnHost As New ADODB.Connection
Dim rstHost As New ADODB.Recordset
Dim AdoErr As ADODB.Errors
Dim vntHostFields As Variant
Dim aryHostValues(2) As Variant
Dim intRowCount As Integer
Dim db As DAO.Database
Dim rstUpload As DAO.Recordset
Set db = CurrentDb
Set rstUpload = db.OpenRecordset("qryPayPeriodsTxt")
'Set connection for no Journaling/Commitment Control (this is not working !?)
cnnHost.IsolationLevel = adXactChaos
'Open a connection to Senior
'cnnHost.Open "provider=IBMDA400;Data Source=Senior;"
cnnHost.Open "provider=IBMDA400;Data Source=SENIOR.SWIRECC.COM;"
'Create and run a command to clear the Host file we are loading
Set cmdHost.ActiveConnection = cnnHost
cmdHost.CommandText = "{{CLRPFM BASLOC53/PH20}}"
cmdHost.CommandType = adCmdText
cmdHost.Execute
Set cmdHost = Nothing
Set cmdHost.ActiveConnection = cnnHost
cmdHost.CommandText = "BASLOC53.PH20()"
cmdHost.CommandType = adCmdTable
cmdHost.Properties("Updatability") = 7
Set rstHost = cmdHost.Execute()
vntHostFields = Array("PDENDDT", "PDSEQNO", "PDYEAR")
While Not rstUpload.EOF
With rstUpload
aryHostValues(0) = !PayPeriodEndTxt
aryHostValues(1) = !PayPeriodSeqTxt
aryHostValues(2) = !PayPeriodYearTxt
End With
rstHost.AddNew vntHostFields, aryHostValues
intRowCount = intRowCount + 1
rstUpload.MoveNext
Wend
rstUpload.Close
Set rstHost = Nothing
Set rstUpload = Nothing
Set cmdHost = Nothing
Set cnnHost = Nothing
Set db = Nothing
fcnUploadPayPeriods = intRowCount
End Function
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jerry Draper
Sent: Tuesday, August 14, 2012 1:45 PM
To: Midrange Systems Technical Discussion
Subject: Re: update iSeries data with Excel
OK, I got the iSeries data in Excel using a view.
Now I want to modify some fields and write them back to the iSeries.
(yes, this situation is acceptable)
How can this be accomplished?
My ODBC connection is r/w.
Jerry
On 8/13/2012 5:45 PM, Evan Harris wrote:
Create a View
On Tue, Aug 14, 2012 at 12:19 PM, Jerry Draper <midrangel@xxxxxxxxxxxxx> wrote:
I have a need to update three fields in a file of 150 fields with Excel.
I can open the file with Excel (via an ODBC data source) but I get
all the fields.
How can I just open the file with the three fields that need updating?
1. Create an LF with just the three fields?
2. Create some kind of qry inside of excel that selects just the
fields I want?
TIA,
Jerry
--
Jerome Draper, Trilobyte Software Systems, since 1976 iSeries,
Network, and Connectivity Specialists -- iSeries, LAN/WAN/VPN
Representing WinTronix, Synapse, Netopia, HiT, and others .....
(415) 457-3431; www.trilosoft.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.
--
Jerome Draper, Trilobyte Software Systems, since 1976 iSeries, Network, and Connectivity Specialists -- iSeries, LAN/WAN/VPN Representing WinTronix, Synapse, Netopia, HiT, and others .....
(415) 457-3431; www.trilosoft.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
