Transfer data from one form to another by hand in a loop

[PeDa]

I want to step through a mailbox, and for each mail item open two forms at the same time (one showing data from an attachment in the the mail item, and the other showing data from a table). I want to be able to paste (by hand) data from the mail-data form to the table-data form, and then close the forms and move on to the next mail item. I can arrange the stepping by making one of the two forms modal, but then I can't paste to/from the non-modal form. If neither of the forms are modal, they both open with the first mail item, but the programme loops all the way through all the other mail items.

For Each Item In MyItems
  If (Left(Item.Subject, 22) = "Afhandeling toezegging") Then
    priToezegging_i = Val(Mid(Item.Subject, 23))
    Me.Repaint
    MyFileName = pubHulpbestandenPad & "Toezegging" & Format(pubAuditor_i, "00")
    For Each MyAttachment In Item.Attachments
      If Right(MyAttachment.FileName, 5) = ".xlsm" Then
        MyFileName = MyFileName & ".xlsm"
        If Dir(MyFileName) <> "" Then
          SetAttr MyFileName, vbNormal
          On Error GoTo ExcelError
Killer:   Kill MyFileName
          On Error GoTo 0
        End If
        MyAttachment.SaveAsFile MyFileName
        ReadOneSpreadsheet (MyFileName)
      End If
    Next MyAttachment
    Me.Repaint
  End If
Next Item

Private Sub ReadOneSpreadsheet(MyFileName)

Dim MijnObjXL As Object
Dim MijnObjWkb As Object
Dim MijnObjSht As Object
Dim MijnDatumGereed As String, MijnAanpassing As String, MijnOpenArgs As String

'Set object variables
Set MijnObjXL = New Excel.Application
'MijnObjXL.Visible = True
Set MijnObjWkb = MijnObjXL.Workbooks.Open(MyFileName)
Set MijnObjSht = MijnObjWkb.Worksheets(1)
MijnObjSht.Activate

'Read data from the spreadsheet...
pubTekst1 = MijnObjSht.Cells(16, 4) 'these threee are for the form showing data from the spreadsheet
pubTekst2 = MijnObjSht.Cells(17, 4) 
pubNummer2_i = priToezegging_i 
MijnOpenArgs = priToezegging_i & ",0,0,0,0,0,0,0,'2013-01-01'"
DoCmd.Hourglass False
DoCmd.OpenForm "frmToezeggingAfhandelenen", , , , , acWindowNormal 'data from the spreadsheet
DoCmd.OpenForm "frmToezeggingenBladeren", , , , , acWindowNormal, MijnOpenArgs 'data from the table
DoCmd.Hourglass True
MijnObjWkb.Close savechanges:=False 'Close the file
Set MijnObjSht = Nothing 'Clear the object references
Set MijnObjWkb = Nothing
MijnObjXL.Quit 'Quit Excel
Set MijnObjXL = Nothing
End Sub

 

[MajP]

Instead of looping set up functions to scroll the items collection. Something like this.

public myItems as outlook.items
Public currentItem as integer

'Some code somewhere to instantiate myItems

Public function getNextItem() as outlook.item
  if currentItem < myItems.count
    currentItem = currentItem + 1
    set getNextItem = myItems(currentItem)
  else
    Msgbox "Last item"
  end if
end function

Public function getCurrentItem() as outlook.item
 set getCurrentItem = myItems(currentItem)
end function

Public function getPreviousItem() as outlook.item
  if currentItem > 0
    currentItem = currentItem - 1
    set getNextItem = myItems(currentItem)
  else
    Msgbox "Last item"
  end if
end function

 

[PeDa]

>Instead of looping set up functions to scroll the items collection. Something like this.

I get the idea; thank you!
Peter

 

[Andrzejek]

If you found MayP's post helpful, click on "Great post? Star it" to show appreciation.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.