Updating info from one workbook to another

  • Hi it seems I've just been bitten by the VBA bug for the past few days I just can't seem to get away from my PC.

    Anyhow this is my first post so please be gentle.

    I wish to input data in one workbook and then want certain bits of this info to update in another workbook.

    I want this info to write to the next available line.

    This is what I've been playing around with, can someone please point me in the right direction.

    'Update the Stats File

    Set myBook = Workbooks.Open(Filename:="c:\Stats.xls")

    myBookRow = [c:\Stats.xls]!Sheet1.Range("A65536").End(xlUp).Row + 1

    Cells(myBookRow, 1) = Date
    Cells(myBookRow, 2) = Now()
    Cells(myBookRow, 3) = User
    Cells(myBookRow, 4) = Sys
    Cells(myBookRow, 5) = Des
    Cells(myBookRow, 3) = TextBox1.Value

    Thanks in advance.

  • Re: Updating info from one workbook to another

    Hi stickyfeet,

    And welcome both to OzGrid and the wonderful world of VBA!

    Here is a small modification to your code.

    It makes use of the object reference you created to the stats.xls workbook.
    Without the explicit reference the using Cells() will either use the current worksheet, which may or may not be correct. Or worst just causes errors.

    If you are just starting out may I suggest, if you have not already, that you use Option Explicit at the top of all your code modules. It will force you to declare all of your variables BUT it will catch typos.
    You can make this automatic by checking the Variables require declartion option in Tools > Options within VBE


  • Re: Updating info from one workbook to another


    Firstly thanks for the welcome and secondly thanks for the help.

    It works a treat. Fantastic. I was struggling with it last night and earlier today.

    Its near enough complete. Once again a big thanks.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!