I have tried various SendKeys combinations like the one below trying
to manipulate the dialog box for uploading a file to the Iseries,
where the transreqfile is a variable with the value of an Excel Range.
I know the file name is getting in the right location, but I can't get
the macro to choose either Enter afterwards or Open, then press OK.

I had all this working in Excel 2003 with:

SendKeys ("/" + "D" + "D" + "D" + "{Enter}" + "{Tab 6}" _
+ TransReqFile _
+ "{Enter}" + "{Tab 2}"), True

I wish we did not have to use the SendKeys method, and that there
would be a real visual basic solution to this.
Here is what I have so far in Excel 2010, with the client access
toolbar items now showing up in the add-ins at this point.

SendKeys ("/xD1" + "Tab 6" + TransReqFile + "{Enter}" + "{ok}")

Thanks for any ideas.


Bruce

Ok, this is the full routine that works about once every four or five
times. And, no I don't have any idea how it works part of the time,
but it has to work every time without fail in order to be useful.

Sub CA_Upload()
Dim TransmjeReqFile As String
TransReqFile = Range("Transreqfile").Value
Application.DisplayAlerts = False
Call Unhide
Call HideZeros
Range("Start").Offset(3, 0).CurrentRegion.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlValues
Range("G:G, P:R, AF:AG").Delete
Application.CutCopyMode = False
Application.DisplayAlerts = True
Range("A1").CurrentRegion.Select
SendKeys ("/xD1" + "{Tab 6}" + TransReqFile + "{Tab 2}" +
"{Enter}"), True
On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.Close
ActiveWorkbook.Close
Application.DisplayAlerts = True



End Sub

On Feb 7, 11:47*am, Revenue wrote:
Ok, this is the full routine that works about once every four or five
times. And, no I don't have any idea how it works part of the time,
but it has to work every time without fail in order to be useful.

Sub CA_Upload()
* * Dim TransmjeReqFile As String
* * TransReqFile = Range("Transreqfile").Value
* * Application.DisplayAlerts = False
* * Call Unhide
* * Call HideZeros
* * Range("Start").Offset(3, 0).CurrentRegion.Copy
* * Workbooks.Add
* * Selection.PasteSpecial Paste:=xlValues
* * Range("G:G, P:R, AF:AG").Delete
* * Application.CutCopyMode = False
* * Application.DisplayAlerts = True
* * Range("A1").CurrentRegion.Select
* * SendKeys ("/xD1" + "{Tab 6}" + TransReqFile + "{Tab 2}" +
"{Enter}"), True
* * On Error Resume Next
* * Application.DisplayAlerts = False
* * ActiveWorkbook.Close
* * ActiveWorkbook.Close
* * Application.DisplayAlerts = True

End Sub

Tell us what you want to happen??

On 07/02/2012 17:47, Revenue wrote:
Ok, this is the full routine that works about once every four or five
times. And, no I don't have any idea how it works part of the time,
but it has to work every time without fail in order to be useful.

Sub CA_Upload()
Dim TransmjeReqFile As String
TransReqFile = Range("Transreqfile").Value
Application.DisplayAlerts = False
Call Unhide
Call HideZeros
Range("Start").Offset(3, 0).CurrentRegion.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlValues
Range("G:G, P:R, AF:AG").Delete
Application.CutCopyMode = False
Application.DisplayAlerts = True
Range("A1").CurrentRegion.Select
SendKeys ("/xD1" + "{Tab 6}" + TransReqFile + "{Tab 2}" +
"{Enter}"), True
On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.Close
ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub

SendKeys is dicing with death, but if you have to do it that way I would
be inclined to guess there is a race condition where some of the
preceding lines are still executing when the keys are sent. Possibly
meaning that the region you want is not actually selected in time.

Most of the intermittent faults I have seen have been charting and
graphics related rather than with dialogue - but intermittent faults
usually mean timing or focus problems.

Add a WasteTime and/or DoEvents macro between the line before and after
the Sendkeys call and cross your fingers. XL2007 and 2010 seem more
prone to race conditions in VBA code than previous versions :(


--
Regards,
Martin Brown