Updating info from one workbook to another

stickyfeet · Feb 13th 2005

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.

[vba]
'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
[/vba]

Thanks in advance.

**Andy Pope** · Feb 13th 2005

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.

Code

'Update the Stats File
 
Set mybook = Workbooks.Open(Filename:="c:\Stats.xls")
 
'''myBookRow = [c:\Stats.xls]!Sheet1.Range("A65536").End(xlUp).Row + 1


' use the reference to the stats.xls workbook
' using first worksheet
With mybook.Worksheets(1)
    ' get next empty row
    myBookRow = .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
End With

Display More

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

stickyfeet · Feb 13th 2005

Re: Updating info from one workbook to another

Andy

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.

Updating info from one workbook to another

Participate now!

Share