Converting macros from Excel 2003 to Excel 2010 - Client Access dialog

[Revenue]

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

 

[Revenue]

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

 

[Don Guillett]

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??

 

[Martin Brown]

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