update worksheet in one file with worksheet in another file

  • Thread starter Thread starter John Keith
  • Start date Start date
J

John Keith

I want to have one worksheet in one of my excel files (file 1) to
reflect the contents of a worksheet in another file (file 2) every
time it is opened (file 1), that is, everytime it is opened this one
tab should reflect the last changes made on the tab in another file,
including formating. I suspect this is possible but I don't have a
clue how to do it. I have a few questions:

Can this be done without VBA? (I'm not even a beginner with VBA!)

I did search the web some and found the following that looks
interesting:

http://techrepublic.com.com/5208-6230-0.html?forumID=101&threadID=215162&messageID=2195715

Is this a starting point I could work with? Will this method described
above copy the formats?

Thanks for any suggestions!


John Keith
(e-mail address removed)
 
How about just opening the second workbook, copying that worksheet to the
workbook that you want, then closing that second workbook.

The could would look something like:

Option Explicit
Sub auto_open()

Dim wkbkName As String
Dim wksName As String
Dim testStr As String
Dim wkbk As Workbook
Dim wks As Worksheet

wkbkName = "C:\my documents\excel\book1.xls"
wksName = "Sheet133"

testStr = ""
On Error Resume Next
testStr = Dir(wkbkName)
On Error GoTo 0

If testStr = "" Then
MsgBox wkbkName & vbLf & "is not available"
Exit Sub
End If

Set wkbk = Workbooks.Open(Filename:=wkbkName)

Set wks = Nothing
On Error Resume Next
Set wks = wkbk.Worksheets(wksName)
On Error GoTo 0

If wks Is Nothing Then
wkbk.Close savechanges:=False
MsgBox wksName & vbLf & "isn't in" & wkbkName
Exit Sub
End If

On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Worksheets(wksName).Delete
Application.DisplayAlerts = True
On Error GoTo 0

wks.Copy _
before:=ThisWorkbook.Worksheets(1)

wkbk.Close savechanges:=False

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Dave,

I appreciate expertise in replying to my inquiry, thanks.

Years ago I actually did some coding so I'm just dangerous enough to
get a flavor of what you are suggesting be done in what you provided
below. Man, I wish I could sit down and talk with you! Let me ask a
few questions and make some statements about your code:

I see a statement "on error go to 0" but I don't understand what "0"
refers to?

Likewise, "on error resume next", what "next" is this referring to, I
don't see any next statements in the code?

There is a lot of error checking that I think could be removed since I
know the file exists and where it is found.

Would this macro have to be run after the spreadsheet is opened or
does the action take place each time the file is opened? (see, I'm
really ignorant of how these things work!)

I have seen the getsarted link before and maybe I need to try and work
through it sometime!

How about just opening the second workbook, copying that worksheet to the
workbook that you want, then closing that second workbook.

The could would look something like:

Option Explicit
Sub auto_open()

Dim wkbkName As String
Dim wksName As String
Dim testStr As String
Dim wkbk As Workbook
Dim wks As Worksheet

wkbkName = "C:\my documents\excel\book1.xls"
wksName = "Sheet133"

testStr = ""
On Error Resume Next
testStr = Dir(wkbkName)
On Error GoTo 0

If testStr = "" Then
MsgBox wkbkName & vbLf & "is not available"
Exit Sub
End If

Set wkbk = Workbooks.Open(Filename:=wkbkName)

Set wks = Nothing
On Error Resume Next
Set wks = wkbk.Worksheets(wksName)
On Error GoTo 0

If wks Is Nothing Then
wkbk.Close savechanges:=False
MsgBox wksName & vbLf & "isn't in" & wkbkName
Exit Sub
End If

On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Worksheets(wksName).Delete
Application.DisplayAlerts = True
On Error GoTo 0

wks.Copy _
before:=ThisWorkbook.Worksheets(1)

wkbk.Close savechanges:=False

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Click to expand...

John Keith
(e-mail address removed)
 
There are things that can cause your code to break.

Trying to open a file that doesn't exist is one of those.

"On Error Resume Next" tells excel's VBA to ignore any error that it sees. That
I as the programmer expect that an error may (not will, just may) occur.

Then I do the check (Dir()).

Then the "on Error goto 0" tells excell to go back checking for errors. I don't
expect any more in the code (famous last words!). If an error is found, excel's
VBA will do what it wants--pop up an ugly, irritating error.

There are only a couple of checks in the code. You could remove them if you
wanted. You'd probably end up saving a second over the next year! I wouldn't
remove them.

And by calling the procedure Auto_Open and placing it into a General module, the
code will run each time you open the workbook (well, if you allow macros to
run).

It couldn't hurt to skim David McRitchie's getstarted page--maybe bookmark it
and come back later when you have more time.
 
There are things that can cause your code to break.
Click to expand...

Dave,

Thank you very much for taking the time to explain the items I
questioned in my previous post. For years I've wanted to learn VBA,
maybe this challenge will get me started. As good as the excel news
groups are I still wish I had a local mentor :-(

PS - I think I have a good book, Pure Visual Basic by Dan Fox, but
every book I've seen assumes a level of understanding that I don't
think I'm at.
John Keith
(e-mail address removed)
 
Back
Top