Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    shell program calls (Excel 2000)

    We us an accounts program called Masterpiece Ace, which has a Microsoft Access front end. The program has a built in method to extract accounts data into excel files, and I wish to automate this method for our accounts month ends

    I have tried to access the program using the following shell function

    Dim ReturnValue As String
    ReturnValue = Shell("C:Program FilesMasterpiece Net ACEMPACE.MDE", 1)
    AppActivate ReturnValue ' Activate the program.

    But I keep on getting a runtime error of 5 which appears to indicate that the program might not be platform compatible. However when I copy the string C:Program FilesMasterpiece Net ACEMPACE.MDE into the windows start menu run function everything proceeds without any problems.

    I would be grateful for any ideas as to how to overcome this?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: shell program calls (Excel 2000)

    Shell expects an executable program as argument. You need to add the fully qualified path to MSAccess.exe:

    Shell "C:Program FilesMicrosoft OfficeOfficeMSAccess.exe C:Program FilesMasterpiece Net ACEMPACE.MDE", 1)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: shell program calls (Excel 2000)

    An alternative to Shell is the ShellExecute function (a Windows API function).

    Put this code in a standard module:

    Declare Function FindWindow Lib "User32" Alias "FindWindowA" _
    (ByVal lpClassName As String, _
    ByVal lpWindowName As Long) As Long

    Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
    (ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long

    Public Const SW_SHOWNORMAL = 1
    Public Const SW_SHOWMAXIMIZED As Long = 3

    Use code like this to open the database:

    Dim hWnd As Long
    Dim lngResult As Long
    hWnd = FindWindow("XLMAIN", 0)
    lngResult = ShellExecute(hWnd, "Open", _
    "C:Program FilesMasterpiece Net ACEMPACE.MDE", 0&, 0&, SW_SHOWNORMAL)
    If lngResult <= 32 Then
    MsgBox "Can't open document.", vbExclamation
    End If

    ShellExecute takes the extension of the filename (.MDE in this case) and looks up the program to open it with in the Windows Registry.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: shell program calls (Excel 2000)

    Have you tried MS Query (it's an add in)? In the Excel press the Data tab, choose Get External Data, then pick New Database Query. In the Choose Database menu choose MS Access then "point" to where the data is. After you have found it and are able to pull the data you need you can use the macro record feature to record all the steps to get the data then edit the macro as need be.
    I use this every day to get info out of our Pervasive ODBC database into Excel and find it very easy to use.
    Just my 2 cents worth.
    Stats

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: shell program calls (Excel 2000)

    Hans

    Thanks for your efforts but so far no success. According to our techies, Computer Associates have added some code to the program so that it must be accessed from a menu, rather than direct. Hence the mde rather than mdb extension.

    However I have now overcome most of the problem by opening the program manually, and then using sendkeys to perform the rest of the tasks. However I would be grateful for some assistance with the following problem:

    Is it possible to intersperse sendkey instructions with VBA variables. For example, at the start of the program I would like to to use dialogue boxes to tell the program for which month and cost centres to extract the data. I will then need to use send keys to send these variables as 2 and three digit numbers to the masterpiece program.

    Many thanks and best regards

    Alex

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: shell program calls (Excel 2000)

    Yes, quite simple:

    Sub Test()
    Dim sTextToInsert as String
    sTextToInsert=InputBox("Please enter your Name")
    Sendkeys "start with these keys" & sTextToInsert & "And End with these"

    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: shell program calls (Excel 2000)

    I also have some sample code to send mouseclicks to a program.
    Interested?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: shell program calls (Excel 2000)

    Jan

    Thanks, we are nearly there. The down load is now properly created. However before I instate you as an excel code god I need to solve one final problem:

    I need to manipulate the excel work book after it has been created. However the macro moves on and finishes before the accounts work book becomes available. I have tried putting True at the end of the save statement so as to try to get the SendKey statement to finalise before the macro moves on. I have also tried a simple do loop timer. However I need a more sophisticated delay mechanism which will allow the save statement to finalise before resetting the excel program focus to the created accounts worksheet. Please see below for a sample of code.

    SendKeys "USER" & "~", True

    SendKeys "PASSWORD" & "{TAB}", True
    SendKeys "~", True 'Enter main menu

    SendKeys "{TAB}" & "~", True 'Enter heiarchy menu

    SendKeys "{TAB}" & "{TAB}" & "{TAB}" & "~", True 'Enter reports menu

    SendKeys "{TAB}" & "{TAB}" & "{TAB}" & "~", True 'Select report 66

    SendKeys ICostCentre, True 'Enter the cost centre

    SendKeys "{TAB}" & "{TAB}" & IMonth & "~", True 'Enter the start period

    SendKeys "{TAB}" & "{TAB}" & "{TAB}" & "2" & "~", True 'Enter the finish period

    SendKeys "{TAB}" & "{TAB}" & "{TAB}" & "{TAB}" & "~", True

    SendKeys "KPMG_R066_" & ICostCentre & ".xls", True

    SendKeys "{TAB}" & "{TAB}" & "~", True 'Output sheet to D drive temporarily

    '**NEED A DELAY MECHANISM HERE**

    'Workbooks("KPMG_R066_273.xls").Activate
    'ActiveWorkbook.Sheets("KPMG_R0066_XLS").Activate

    'Columns("b:b").Select
    'Selection.Insert

    'Range("b2").Activate
    'ActiveCell.Value = Mid(ActiveCell.Offset(0, -1), 4, 20)

    End Sub

    Any ideas Oh Guru

    PS: Any additional info re mouseclicks would be most welcome

    Alex

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: shell program calls (Excel 2000)

    Is the workbook you are trying to activate at the end of the process loaded into XL automatically or does it need to be opened?

    One way (not very elegant though) is to end the macro just before the statement that tries to activate the newly created workbook.

    Then have (e.g.) the next code that should process the new workbook in a separate sub called "ContinueExport".

    As the last statement of the exprting code (where you put "**NEED A DELAY MECHANISM HERE**"), insert these lines:

    Application.OnTime Now + TimeValue("00:00:03"), "ContinueExport"
    DoEvents

    Sub ContinueExport()
    '.......
    'Workbooks("KPMG_R066_273.xls").Activate
    'ActiveWorkbook.Sheets("KPMG_R0066_XLS").Activate

    'Columns("b:b").Select
    'Selection.Insert

    'Range("b2").Activate
    'ActiveCell.Value = Mid(ActiveCell.Offset(0, -1), 4, 20)
    '...............
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: shell program calls (Excel 2000)

    OK Jan you have now attained the status of code god as far as I am concerned. You can walk on water, levatate and do the other things that we mere mortals can only dream of.

    As you can probably gather it worked. But I am totally baffled as to why it worked. Can you please explain:
    Application.OnTime Now + TimeValue("00:00:03"), "ContinueExport" [why is continue export in ""?and how does this function work?]
    DoEvents [What is this bit?]

    Many thanks and best regards

    Alex

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: shell program calls (Excel 2000)

    The OnTime method schedules a macro (the one between quotes) to be run at a future time (in this case 3 seconds from "now"). This way, all macros are first terminated and then the scheduled macro is run (when it's time has come).

    DoEvents ensures pending (system) actions are performed. Danger: also pending keystrokes wil be executed. If you press F2 during the execution, strange things may happen, since Excel will go into edit mode *while the macro is running*. Lots of things are not possible in edit mode, like saving and if one of those is used in your macro, a crash might follow!

    Since you are controlling an application using sendkeys, you already have to stay away from the keyboard...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: shell program calls (Excel 2000)

    Jan

    Thanks for the advice so far. The program now works if run for each cost centre in turn. I now want to combine running all of the 9 cost centres into one program. Please see program listing below. There are 2 problem areas which are highlighted by lines commencing '***. Can you please advise how I can overcome the problems shown.

    Thanks

    Alex
    __________________________________________________ _____


    Dim ICostCentre As Integer
    Dim IMonth As Integer

    Public Sub DownLoadGLReport66()

    IMonth = 2

    SendKeys "%{TAB}", True
    SendKeys "CHANGED" & "~", True

    SendKeys "CHANGED" & "{TAB}", True
    SendKeys "~", True 'Enter main menu

    SendKeys "{TAB}" & "~", True 'Enter heiarchy menu

    SendKeys "{TAB}" & "{TAB}" & "{TAB}" & "~", True 'Enter reports menu

    SendKeys "{TAB}" & "{TAB}" & "{TAB}" & "~", True 'Select report 66

    ICostCentre = 273
    Call CreateExport
    '***Program does the following 4 lines before finishing the CreateExport and ContinueExport functions
    '***Needs to finish these functions, then re-set the ICostCentre variable and then
    '***do another CreateExport function etc with a new ICostCentre set to 274
    ICostCentre = 274
    SendKeys "%{TAB}", True
    SendKeys "{TAB}" & "{TAB}" & "{TAB}" & "{TAB}", True
    Call CreateExport


    End Sub

    Public Sub CreateExport()
    SendKeys ICostCentre, True 'Enter the cost centre

    SendKeys "{TAB}" & "{TAB}" & IMonth & "~", True 'Enter the start period

    SendKeys "{TAB}" & "{TAB}" & "{TAB}" & "2" & "~", True 'Enter the finish period

    SendKeys "{TAB}" & "{TAB}" & "{TAB}" & "{TAB}" & "~", True

    SendKeys "KPMG_R066_" & ICostCentre & ".xls", True

    SendKeys "{TAB}" & "{TAB}" & "~", True 'Output sheet to D drive temporarily

    Application.OnTime Now + TimeValue("00:00:20"), "ContinueExport"
    DoEvents




    End Sub

    Public Sub ContinueExport()
    Dim n As Integer

    Workbooks("KPMG_R066_" & ICostCentre & ".xls").Activate
    ActiveWorkbook.Sheets("KPMG_R0066_XLS").Activate

    Columns("b:b").Select
    Selection.Insert

    Columns("i:i").Select
    Selection.Insert

    Columns("i:i").Select
    Selection.NumberFormat = "#,##0.00"

    n = 2
    Do
    Range("a" & n).Activate
    ActiveCell.Offset(0, 1).Value = Mid(ActiveCell.Value, 8, 20)
    ActiveCell.Offset(0, 8).Value = ActiveCell.Offset(0, 6).Value + ActiveCell.Offset(0, 7).Value
    ActiveCell.Offset(0, 14).Value = Mid(ActiveCell.Offset(0, 13), 5, 6)
    strformula = "=IF(" & CStr(ActiveCell.Offset(0, 15).Address(False, False)) & "=""""," & CStr(ActiveCell.Offset(0, 14).Address(False, False)) & "," & CStr(ActiveCell.Offset(0, 15).Address(False, False)) & ")"
    ActiveCell.Offset(0, 16).Value = strformula
    n = n + 1
    Loop Until ActiveCell.Value = ""

    ActiveCell.EntireRow.Select
    Selection.Clear

    '***The exported file is created in a previous version of excel
    '***Is there a way to select save as latest version of excel.
    '***Program currently stops here and waits for my respose
    ActiveWorkbook.Close SaveChanges:=True


    End Sub

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: shell program calls (Excel 2000)

    I'm afraid you'll have to redisgn the flow of the macro.

    I guess it would be best to:

    - First create all export files (using a counter?), this could be done in one sub.
    - Then import them all in the continue_export sub.

    The reason why it is not working as you have it now is because the OnTime method sets a timer to run a macro, but that macro will not run before all currently running macros have finished.

    Remove the OnTime statement from the createExport macro and place it in the main macro that calls the CreateExport macro. Make sure it is the last statement. Then adapt the continueExport sub so it will import (open) all exported files in a row.

    To save to the default XL format, use the statement:

    Activeworkbook.SaveAs FileName:=Activeworkbook.fullname, FileFormat:=xlNormal
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •