Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2005
    Posts
    3

    Unanswered: Link 2 worksheets - w/auto update

    I need help, please!

    I need to link two worksheets together, the sheets are located in different workbooks. One sheet contains formulas and is linked to a SQL database that gives current value. I need to copy this sheet into another workbook and have it update automatically - like every night.

    I have no idea where to start. Thanks in advance!

  2. #2
    Join Date
    Mar 2005
    Posts
    8
    Auto-update may be done with an external script. You write a script in VB, create a scheduler task that runs this script with windows scripting host every night.

  3. #3
    Join Date
    Mar 2005
    Posts
    3
    Huh? Are there any resources I can use, or look up? I have very limited programming knowledge

    Or any specific directions you can give me? Thanks so much!

  4. #4
    Join Date
    Mar 2005
    Posts
    8
    First of all you need to record a macro that does all necessary operations with these two workbooks. Then create a file with .vbs extention and write something like this:

    Set appExcel = CreateObject("Excel.Application")

    ' here goes your just recorded macro. appExcel variable is exactly the same
    ' as Application object in Excel VBA. E.g. appExcel.ActiveWorkbook gives you
    ' the active workbook



    This something.vbs script file has to be run via Windows Scripting Host as follows:

    cscript something.vbs

  5. #5
    Join Date
    Mar 2005
    Posts
    3
    Thanks! I think I got the logic behind it down.

    1.) Macro that does all necessary operations between the 2 sheets.
    2.) .VBS extension file
    3.) VBS Script file

    Do you know where I can find books on how to work this out? The thing is, I started working for a company that doesn't have an IT dept. I don't have any programming knowledge and am basically starting from scratch. Thanks again.

  6. #6
    Join Date
    Mar 2005
    Posts
    8
    Mmm... There are three things I use:

    1. Microsoft Excel help
    2. Windows Scripting Host reference downloadable from download.microsoft.com (it also contains complete jscript and vbscript reference)
    3. Google ))

    There is no better way to start learning VBA than to record a macro and dig it with a help opened in a second window For a programming related information try www.codeproject.com. There are plenty of examples and "getting started" manuals there.

    As for this task with external processing of excel workbooks the main idea is that excel application can be run invisibly and quietly as an ActiveX object almost from anywhere.

Posting Permissions

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