Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Location
    Brantford, Ontario
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Opening files from another application (VB6)

    I'm looking to open a file from a non Microsoft application. I can start the application from visual basic, but I don't know how to make it the active window and access its Command bar.

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening files from another application (VB6)

    You need to elaborate here just a bit. Do you want the app to start so that a *user* can access the commandbar (start the app and make it active); or do you want your app to start the app (hidden or visible?) and programmatically access commandbar functionality (gee wiz!). <img src=/S/yawn.gif border=0 alt=yawn width=15 height=15>
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    New Lounger
    Join Date
    Aug 2002
    Location
    Brantford, Ontario
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening files from another application (VB6)

    Yes, programmatically access commandbar functionality. I have been using VB through Excel to analyse data (averages, max, mins, verify validity etc.), now I want to automate the entire data handling process which includes a data extraction software. So, yes I want all this done without a user interface and am looking to VB6 as a possible route as I have not been able to figure out how to do this with vb within Excel. Why the gee wiz? Thanks.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening files from another application (VB6)

    Have you ever automated Excel from within Excel itself?

    If so, the code in VB is the same.

    If not, what exactly are you trying to do?

    The more information you can give, the easier it will be for folks to help.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening files from another application (VB6)

    The gee wiz because I started to get a bit highfalutin.

    So, you want your VB app to grab the data from it's source (which is?) and pull it into Excel? If your data extraction software exposes an object model for it's properties and methods, you can use it from within Excel.

    If not, you still have ShellExecute (a Windows API function) or the simple Shell command. With API calls you can send messages to the app and "click" it's menus, etc. With Shell, you can use the API send/post message functions or use Sendkeys. Learning the API is more difficult than using Sendkeys, but Sendkeys is problematic (timing issues, error handling, etc.)

    Hope this helps. You might want to explain in more detail. For one, I question the need for "data extraction software". Could you not use Excel to extract (import) the data? Or is the data on tape or EBCDIC encoded or something?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening files from another application (VB6)

    Here here! I second the call for more info!
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  7. #7
    New Lounger
    Join Date
    Aug 2002
    Location
    Brantford, Ontario
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening files from another application (VB6)

    Sure, but when running a macro from Excel, Excel is already running and all commands are pointing to Excel.

    Windows(Fna).Activate 'activates a spreadsheet having a filename "Fna"
    Sheets(Shts).Select 'activates a sheet named "Shts"

    I'm trying to activate a file within another application.

    Shell (programxprogramcfg.exe) 'starts this application programcfg.exe

    This application has a command "File Open" (or Ctrl+O) and a hot key (or F9) which runs the application ( does a data extraction from a database)

    I don't know how to send a command to this application / make it the active window.

  8. #8
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening files from another application (VB6)

    OK.

    You don't need to make the window active.

    Here is some code to point you in the right direction

    First off, you need a reference to the Microsoft Excel X.0 Object Library in your VB app

    then in your code:

    dim objXL as Excel.Application
    dim wbk as WorkBook
    dim sht as WorkSheet

    Set objXL = CreateObject("Excel.Application")
    Set wbk = objXL.Workbooks.Open "C:Fullpathtomybook.xls"
    set sht = wbk.Sheets("SheetName")
    sht.Range("A1").Activate

    'Your data extraction code goes here.

    set sht = nothing
    wbk.close
    set wbk = nothing
    objXL.Quit
    set objXL = nothing

    Hopefull this should help.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  9. #9
    New Lounger
    Join Date
    Aug 2002
    Location
    Brantford, Ontario
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening files from another application (VB6)

    Thanks, I will go down that ShellExecute path a bit. The extraction software is necessary because the database is proprietary. It is a FIXDMACS SCADA system, polling a couple hundred addresses every 30 seconds. I have had some recent successes with macro writing within Excel for handling the extracted data (50 or so parameters with values for every 15 min or hour) and am trying to take the next step in automation. I'm sure there will come a time when I will have the skills and software to access the database directly but that time is not now.

  10. #10
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening files from another application (VB6)

    OH. My mistake.

    I though you were trying to access Excel from VB.

    It's this FIXDMACS SCADA system that you are trying to automate?

    Or are you trying to do both?

    Or am I all wrong?
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  11. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Opening files from another application (VB6)

    Has anyone checked with the database vendor? Maybe it can kick out a log every so often that you can pick up from disk when the timestamp changes?

  12. #12
    New Lounger
    Join Date
    Aug 2002
    Location
    Brantford, Ontario
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening files from another application (VB6)

    It is the handling of this data I am trying to automate. The SCADA system (process automation and data collection) stores 1 day data files on its own server. Someone transfers these files to another server. I copy these files to my hard drive, they are unreadable at this point. I choose one of 20 or so extraction files I have created using extraction software (3rd party software) that converts selected data into .csv format files. Now I copy these extracted files into Excel spreadsheets set up to display averages etc. Now I run validity checks on the data that is in the Excel sheets in the form of Excel macros, then I run macros that do stats on all the data. Each step of the way requires manual input. I would like to remove a couple of steps by automating the process - clearly some of these steps are rediculous. Right now I'm just looking at writing something that will be initiated by choosing one of the 20 or so extraction files and selecting a date. Something like:

    Parameter = InputBox("Enter the parameter") 'where the parameter identifies both the extraction file and the final Excel file
    St = InputBox("Enter the start date as mm/dd/yy")
    Et = InputBox("Enter the finish date as mm/dd/yy")
    It = InputBox("Enter the interval time as hh:mm:ss, always 01:00:00 or 00:15:00 for this program")
    Workbooks.OpenText Filename:="C:histxsource" & Parameter & ".txt", Origin:=xlWindows _
    , StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
    , Space:=False, Other:=False, FieldInfo:=Array(1, 2)
    Cells(1, 1) = "-st " & St & "@00:00:00"
    Cells(2, 1) = "-et " & Et & "@00:00:00"
    Cells(3, 1) = "-it " & It & "@00:00:00"
    Cells(4, 1) = 1
    ActiveWorkbook.Save
    ActiveWorkbook.Close SaveChanges:=False

    Now I have a file that the extraction program can use. I need to get the extraction program to open this file and then execute its extraction. When the extraction has completed the extraction program stores the results as a .cvs file which is easy to access through an Excel macro. I can call my other macros through my new procedure.

  13. #13
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Opening files from another application (VB6)

    Does the extraction program run from the command line and quit by itself? That would be easy. However, if you need to press buttons in a GUI with VBA, it gets dicey.

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Marietta, Georgia, USA
    Posts
    296
    Thanks
    9
    Thanked 4 Times in 4 Posts

    Re: Opening files from another application (VB6)

    There are other possibilities than VBA when you're trying to control another application. I'll suggest a program that I have successfully used in the past, which is Macro Mania:
    http://www.nstarsolutions.com/mm.htm

    It is fairly inexpensive ($45 personal use, or $98 professional use). I have used it for many years when VBA is not an option. In prior versions, it was written in Visual Basic, and used Windows API calls to control other applications (probably like SendKeys).
    Rick Groszkiewicz
    Life is too short to drink bad wine (or bad coffee!)

  15. #15
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Opening files from another application (VB6)

    Interesting program. Here's an updated link: http://www.nstarsolutions.com/products/4412.htm

Posting Permissions

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