update worksheet in one file with worksheet in another file

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:

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

Thanks for any suggestions!

John Keith
kd0gd@juno.com

 0
kd0gd (97)
5/14/2007 3:30:25 AM
excel 39879 articles. 2 followers.

5 Replies
376 Views

Similar Articles

[PageSpeed] 0

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
ThisWorkbook.Worksheets(wksName).Delete
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

John Keith wrote:
>
> 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:
>
>
> Is this a starting point I could work with? Will this method described
> above copy the formats?
>
> Thanks for any suggestions!
>
> John Keith
> kd0gd@juno.com

--

Dave Peterson

 0
petersod (12004)
5/14/2007 11:53:40 AM
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

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!

On Mon, 14 May 2007 06:53:40 -0500, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>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
>    ThisWorkbook.Worksheets(wksName).Delete
>    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
>
>
>
>John Keith wrote:
>>
>> 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:
>>
>>
>> Is this a starting point I could work with? Will this method described
>> above copy the formats?
>>
>> Thanks for any suggestions!
>>
>> John Keith
>> kd0gd@juno.com

John Keith
kd0gd@juno.com

 0
kd0gd (97)
5/15/2007 5:10:40 AM
There are things that can cause your code to break.

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

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

"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.

John Keith wrote:
>
> 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
>
> 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!
>
> On Mon, 14 May 2007 06:53:40 -0500, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
>
> >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
> >    ThisWorkbook.Worksheets(wksName).Delete
> >    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
> >
> >
> >
> >John Keith wrote:
> >>
> >> 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:
> >>
> >>
> >> Is this a starting point I could work with? Will this method described
> >> above copy the formats?
> >>
> >> Thanks for any suggestions!
> >>
> >> John Keith
> >> kd0gd@juno.com
>
> John Keith
> kd0gd@juno.com

--

Dave Peterson

 0
petersod (12004)
5/15/2007 11:29:54 AM
On Tue, 15 May 2007 06:29:54 -0500, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>There are things that can cause your code to break.

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
kd0gd@juno.com

 0
kd0gd (97)
5/17/2007 3:13:19 AM
I don't own Dan Fox's book, but I have a nice "get started" book from John
Walkenbach.
http://j-walk.com/ss/books/xlbook25.htm

(Actually, I have an earlier version)

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

You may want to check your local bookstore/internet site and you can see if any

John Keith wrote:
>
> On Tue, 15 May 2007 06:29:54 -0500, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
>
> >There are things that can cause your code to break.
>
> 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
> kd0gd@juno.com

--

Dave Peterson

 0
petersod (12004)
5/17/2007 1:06:21 PM

Similar Artilces:

Update for MS Money 2005?
I have Money 2005. Is there an update to MS Money for Canadian users? Thanks in advance for any answers. We need a bit more information! Are you having problems with M2005 or is this just a post-Christmas/New Year random query when you are trying to get away from the in-laws? -- Regards Bob Peel, Microsoft MVP - Money For unofficial FAQs see http://money.mvps.org/ or http://umpmfaq.info/ I do not respond to any emails that I have not specifically asked for. "Daniel" <Daniel@discussions.microsoft.com> wrote in message news:E86EAB89-21DE-4505-ACAD-647278D736BD@microso...

updating sheets based on data in first sheet
Another payroll question, I have a workbook that contains 26 sheets, one for each bi-weekl payroll period. I would like to set it up so when i add a new employe the rest of the sheets also update automatically with that employee name and information. I have been able to acheive this to a limited degree using th =sheet1!a1 formula, but this only updates the info in the first cel and particularly the first column. I would like to acheive this using the first sheet, since at th end of the year I would like to be able to calculate ytd figure easily. Thank -- Message posted from http://www.Exc...

Problem with Script Updating
I am using a script to update the “1099 Type” field for Master Vendor table. The script basically is a basic if-then statement. This script is run “Before Document Commit” and it’s not updating correctly, its flip-flopping the results. When the script is set to run Before Document Commit I have the “Destination mapping” field “1099 Type” set to “Use Script”. Script below: If SourceFields("Send 1099") = "N" Then DestinationFields("Options.1099 Type").Value = 1 Else DestinationFields("Options.1099 Type").Value = 4 End If I have also tried this s...

I created a newsletter yesterday - everything fine - use Publisher all the time. Then an automatic update came down this morning and I can't open the file - it says "Publisher cannot open file" Other documents in Publisher are opening - any ideas? http://support.microsoft.com/kb/972566/ -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Kim" <Kim@discussions.microsoft.com> wrote in message news:8754339B-99DC-4D09-83AD-6B34D8215274@microsoft.com... >I created a newsletter yesterday - everythin...

Microsoft Update only updates Windows Defender

Outlook data file check
Hello, I am using Outlook 2007 on XPSP2. Quite often on startup I get a message the Outlook is doing a file check and performance may be slow. I also often get on computer startup a message requesting me to send an error report to Microsoft that includes a few episodes of Outlook not responding. Any idea what this could be about and how to prevent both? -- Thanks, Bob ...

Backing Up Money Files
Do any versions of Money allow backing up data to cds, rather than floppy discs? All of them allow it in the sense that they don't prevent or preclude it. None of them allow it in the sense of enabling it by providing their own CD burning engine. See http://www.bollar.org/msmoney/#Q51 for the FAQs treatment of this incredibly FAQ. "Wade" <wstarl@bellsouth.net> wrote in message news:083201c38627$333d0770$a401280a@phx.gbl... > Do any versions of Money allow backing up data to cds, > rather than floppy discs? A good strategy would be to back up to second hard disk,...

Update question
Soory if this is the wrong place to ask this, but cannot find a NG proper to Windows 7. I currently run Windows Vista and am getting tired of Vista's decision to tell me that it is going to shut down in less than a minute. So have decided to upgrade to Windows &. I note that I can purchase an upgrade versiom for 64 quid from Amazon or an apparently full version of Windows 7 Home premium for 89 quid. My question is this - If I buy the upgrade version will I only be able to load it on a new PC in the future if I already have windows Vista installed? i.e For any future cl...

can not add data to existing Excel worksheet
sullyhd, is the sheet protected? What happens when you try to add data? -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "sullyhd" <sullyhd@discussions.microsoft.com> wrote in message news:2B06E0E8-A952-461F-A01F-219AB530A2F6@microsoft.com... > ...

Outlook 2003 died mysteriously after update
Setup in a nutshell... System: Lenovo T500 laptop OS: Vista Business SP1 32-bit Software: Office Outlook 2003 SP3 Problem... So I'm using this setup for over a year, everything OK. Yesterday I did two things and now Outlook dies (quietly, sans error message) immediately after startup. It show the splash screen, displays my inbox, and *poof* disappears. What I did earlier... 1. Windows Update installed: KB9766662, KB979306, KB979099, KB975929 2. At the same time, while searching for another program to uninstall I stumbled upon (and uninstalled) Windows LIVE Toolbar and...

How update entity in post update?
I created a handler for Update post callout for Opportunity. I want update some fields of the opportunity on the PostUpdate. However, if I call the Update method of CRMOpportunity in the PostUpdate I will create a recursive post callout. Can somebody help me? Thank you for pay attention []'s Vin�cius Pitta Lima de Ara�jo You need to check the OrigObjectXML field to see what fields were updated and then act appropriately. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Wed, 4 Aug 2004 17:43:17 -0300, "Vin�cius ...

How do a import data from a text file to an excel worksheet
I have more than 66,000 lines of data to import into excel (v.2003). Can I do this without loosing data? -- mad ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=45e3503c-395d-4b02-923d-b07371...

file exist
hi, what is the command to check if file exist ? thanks. Lior Montia wrote: > hi, > > what is the command to check if file exist ? > > thanks. Dir("path to file") It will return the name of the file if it exists and "" if it does not. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com "Lior Montia" <liorm@matrix.co.il> wrote in message news:f053kh$edi$1@news2.netvision.net.il... > hi, > > what is the command to check if file exist ? > > thanks. > > Dir() will...

Cannot install update KB979906 for .NET Framework 1.1 SP1
Running : Windows XP media center edition SP3 Have tried installing KB979906 a few times and also downloaded update manually but cannot install this update. Receiving error 0x643.Is it safe to uninstall the .NET Framework 1.1 and re-install as possibly corrupt , without having to uninstall/re-install all other .NET frameworks 2 , 3 and 3.5 including the updates that go with them? .. Hello sherlockomes, you might want to look at the following kb article to see if this will help with the error 80070643. <http://windows.microsoft.com/en-US/windows-vista/Windows-Update-error...

Preferred file group
Our application records transactional events (maybe 300K to 500K rows per day) on a separate file group called HISTORY. I was wondering if there are performance considerations when creating indices on this history table. That is, do I get a performance improvement if the index resides in the default file group or if it resides in my HISTORY file group. Thanks in advance, Gary Hi ary, You can create a seprate file for the indexes, that would help as the index read/writes would be from a seperate file. Sriram www.sqllike.com > Our application records transactional event...

Pasting chart worksheet as link 2007
A chart created in 2007 and sitting in it's own sheet (not included on the data worksheet) will not display entirely if pasted as link (neither into Word nor PPT) Some of the chart seems to be "cut off" in the linked object. This seems to be connected to how much of the "canvas" is visible in Excel. Viewing the chart in Excel on a zoom so that it fills the whole screen seems to remedy this. Is there some setting in Excel that would allow us to set the worksheet chart to automatically size to window? Or else: does anyone know a way of pasting a worksheet chart as ...

Getting path from the full path and file name
Hello again, This is my second question today. The question is how to get just the path info from a string that contains path and file name information. I am looking an MFC or WinAPI function to do that because I don't want to do it manually. Thanks, "msnews.microsoft.com" <fwr> schrieb im Newsbeitrag = news:uzyJa\$q1DHA.1700@TK2MSFTNGP12.phx.gbl... : Hello again, :=20 : This is my second question today. The question is how to get just the = path : info from a string that contains path and file name information. I am : looking an MFC or WinAPI function to do that becaus...

latest update too MSCFV2
Hi, I have MSCFV2 version 6.5.7825.0. Could someone inform me if this is the latest download? Looks like you have 6.5.7825.0 from 05/21/2006, but there is a newer version - 6.5.7831.0 from 06/01/2006. C. Smith Enso Technologies, Incorporated http://www.ensotech.com On Tue, 13 Jun 2006 04:25:02 -0700, Paul <Paul@discussions.microsoft.com> wrote: >Hi, > >I have MSCFV2 version 6.5.7825.0. Could someone inform me if this is the >latest download? Christopher Smith csmith@ensotech.com Enso Technologies, Incorporated http://www.ensotech.com Also - meant to post this in t...

Print record once, update Yes/No field verifiying print
I would like to print a group of records, then have a Yes/No field [Printed] updated in my Jobs table with an update query showing the records were printed. Then next time the report runs, In my query criteria I will test for True values on the Yes/No field. Then only the records with the Yes/No field marked No will print. Any suggestions? Thanks Tommyboy,there's more to this question than meets the eye. For an explanation of what's involved, see: Has the record been printed? at: http://allenbrowne.com/ser-72.html The article includes a free sample database that dem...

Command to reference previous worksheet
Hi, I have a macro whereby I copy the workbook and create a new file. There are many sheets, a, b, c....and I'd like the sheets in cell A1 to reference the previous sheet cell A1 + 1. Ex: in sheet "a", A1 = 30 therefore in sheet "b", cell A1 should show 31 and sheet "c", A1 = 32.... Some of the sheets will be deleted when it comes to the end of the billing cycle but I still want the subsequent sheets to continue with the sequence, ie, if "c" gets deleted, "d" should show A1 = 32. (A1 in "a" will always be changed upon a...

How to call another file in your xml document.
Greetings. I have an app which calls "text.xml", which is stored on a network share. Each user has different text requirements. what I want is for "text.xml" which is stored on the network, to read a file locally on the user's macine (i.e. c:\localtext\text.txt) and populate it with the local contents and display it. So for each user the content would be customized to his liking. Please let me know if this is possible and if so, how. Thank You Hemang "Hemang Shah" <v-hshah@microsoft.com> wrote: > Please let me know if this is possible and if...