Problems saving a worksheet with Links

Does anyone know how I can resolve this issue ...  I have a directory which
contains 129 worksheets which have links to external data (in a Master
Spreadsheet) -- I need to copy these files into a New Directory, but kee the
Master Spreadsheet (which they are linked to) in the original location.  If
I do a simple Cut & Past, the Reference Link to the Master Spreadsheet gets
moved to the New Directory (where the file does not exist), but if I open
the worksheet (in the original directory/location) and Save As to the New
Directory, the worksheet saved in the New Directory maintains its link to
the Master Spreadsheet in the original directory/location.  I hope I've
explained this clearly.

Here's my problem -- it's a bit time consuming to have to open each and
every worksheet and Save As to the New Location -- I'm not sure if a Batch
File (or Dos Command xcopy) would solve this -- Is there some code I could
use to Open each worksheet, Save As to the New directory, Close, then
perform this on each of the .xls files in the original directory?  If so,
could you please point me in the direction with an example of the code.

Example:

Files in C:\Temp (a.xls, b.xls, c.xls) Copied to C:\Budget

Many Thanks in Advance. 


0
7/7/2006 2:06:48 PM
excel 39879 articles. 2 followers. Follow

4 Replies
397 Views

Similar Articles

[PageSpeed] 24

I think that this does it:

Option Explicit
Sub testme01()
   
    Dim myNames() As String
    Dim fCtr As Long
    Dim myFile As String
    Dim myOldPath As String
    Dim myNewPath As String
    Dim MstrFileName As String
    Dim TempWkbk As Workbook
        
    'change to point at the old folder
    myOldPath = "c:\my documents\excel\"
    If Right(myOldPath, 1) <> "\" Then
        myOldPath = myOldPath & "\"
    End If
    
    'change to point at the new folder
    myNewPath = "c:\temp"
    If Right(myNewPath, 1) <> "\" Then
        myNewPath = myNewPath & "\"
    End If

    'the master file--not to be copied    
    MstrFileName = "mstr.xls"
    
    myFile = ""
    On Error Resume Next
    myFile = Dir(myOldPath & "*.xls")
    On Error GoTo 0
    If myFile = "" Then
        MsgBox "no files found"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    'get the list of files
    fCtr = 0
    Do While myFile <> ""
        fCtr = fCtr + 1
        ReDim Preserve myNames(1 To fCtr)
        myNames(fCtr) = myFile
        myFile = Dir()
    Loop
    
    If fCtr > 0 Then
        For fCtr = LBound(myNames) To UBound(myNames)
            If LCase(myNames(fCtr)) = LCase(MstrFileName) Then
                'do nothing, skip the master file
            Else
                Application.StatusBar _
                       = "Processing: " & myNames(fCtr) & " at: " & Now
                       
                Set TempWkbk = Workbooks.Open(Filename:=myOldPath _
                                  & myNames(fCtr), ReadOnly:=True)
                                
                TempWkbk.SaveAs Filename:=myNewPath & myNames(fCtr)
                TempWkbk.Close savechanges:=False
            End If
        Next fCtr
    End If
    
    With Application
        .ScreenUpdating = True
        .StatusBar = False
    End With
   
End Sub



doctorjones_md@yahoo.com wrote:
> 
> Does anyone know how I can resolve this issue ...  I have a directory which
> contains 129 worksheets which have links to external data (in a Master
> Spreadsheet) -- I need to copy these files into a New Directory, but kee the
> Master Spreadsheet (which they are linked to) in the original location.  If
> I do a simple Cut & Past, the Reference Link to the Master Spreadsheet gets
> moved to the New Directory (where the file does not exist), but if I open
> the worksheet (in the original directory/location) and Save As to the New
> Directory, the worksheet saved in the New Directory maintains its link to
> the Master Spreadsheet in the original directory/location.  I hope I've
> explained this clearly.
> 
> Here's my problem -- it's a bit time consuming to have to open each and
> every worksheet and Save As to the New Location -- I'm not sure if a Batch
> File (or Dos Command xcopy) would solve this -- Is there some code I could
> use to Open each worksheet, Save As to the New directory, Close, then
> perform this on each of the .xls files in the original directory?  If so,
> could you please point me in the direction with an example of the code.
> 
> Example:
> 
> Files in C:\Temp (a.xls, b.xls, c.xls) Copied to C:\Budget
> 
> Many Thanks in Advance.

-- 

Dave Peterson
0
petersod (12004)
7/7/2006 3:32:37 PM
Dave -- Thank you for your assistance -- the code worked brillantly!  It's 
Friday, so I hate to press my luck on this one, but is it possible to modify 
the code to allow for INPUT boxes for myOldPath & myNewPath (so that a user 
could designate the origination/destination paths without having to tinker 
with the code)?  Ideally, I'd like to placed a Command Button on the EXCEL 
worksheet, and when Pressed, display the (2) Input Boxes for myOldPath & 
myNewPath.

Any ideas on this modification?

Thanks again for your previous assistance!
================================================
"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:44AE7E95.22EEFC19@verizonXSPAM.net...
>I think that this does it:
>
> Option Explicit
> Sub testme01()
>
>    Dim myNames() As String
>    Dim fCtr As Long
>    Dim myFile As String
>    Dim myOldPath As String
>    Dim myNewPath As String
>    Dim MstrFileName As String
>    Dim TempWkbk As Workbook
>
>    'change to point at the old folder
>    myOldPath = "c:\my documents\excel\"
>    If Right(myOldPath, 1) <> "\" Then
>        myOldPath = myOldPath & "\"
>    End If
>
>    'change to point at the new folder
>    myNewPath = "c:\temp"
>    If Right(myNewPath, 1) <> "\" Then
>        myNewPath = myNewPath & "\"
>    End If
>
>    'the master file--not to be copied
>    MstrFileName = "mstr.xls"
>
>    myFile = ""
>    On Error Resume Next
>    myFile = Dir(myOldPath & "*.xls")
>    On Error GoTo 0
>    If myFile = "" Then
>        MsgBox "no files found"
>        Exit Sub
>    End If
>
>    Application.ScreenUpdating = False
>
>    'get the list of files
>    fCtr = 0
>    Do While myFile <> ""
>        fCtr = fCtr + 1
>        ReDim Preserve myNames(1 To fCtr)
>        myNames(fCtr) = myFile
>        myFile = Dir()
>    Loop
>
>    If fCtr > 0 Then
>        For fCtr = LBound(myNames) To UBound(myNames)
>            If LCase(myNames(fCtr)) = LCase(MstrFileName) Then
>                'do nothing, skip the master file
>            Else
>                Application.StatusBar _
>                       = "Processing: " & myNames(fCtr) & " at: " & Now
>
>                Set TempWkbk = Workbooks.Open(Filename:=myOldPath _
>                                  & myNames(fCtr), ReadOnly:=True)
>
>                TempWkbk.SaveAs Filename:=myNewPath & myNames(fCtr)
>                TempWkbk.Close savechanges:=False
>            End If
>        Next fCtr
>    End If
>
>    With Application
>        .ScreenUpdating = True
>        .StatusBar = False
>    End With
>
> End Sub
>
>
>
> doctorjones_md@yahoo.com wrote:
>>
>> Does anyone know how I can resolve this issue ...  I have a directory 
>> which
>> contains 129 worksheets which have links to external data (in a Master
>> Spreadsheet) -- I need to copy these files into a New Directory, but kee 
>> the
>> Master Spreadsheet (which they are linked to) in the original location. 
>> If
>> I do a simple Cut & Past, the Reference Link to the Master Spreadsheet 
>> gets
>> moved to the New Directory (where the file does not exist), but if I open
>> the worksheet (in the original directory/location) and Save As to the New
>> Directory, the worksheet saved in the New Directory maintains its link to
>> the Master Spreadsheet in the original directory/location.  I hope I've
>> explained this clearly.
>>
>> Here's my problem -- it's a bit time consuming to have to open each and
>> every worksheet and Save As to the New Location -- I'm not sure if a 
>> Batch
>> File (or Dos Command xcopy) would solve this -- Is there some code I 
>> could
>> use to Open each worksheet, Save As to the New directory, Close, then
>> perform this on each of the .xls files in the original directory?  If so,
>> could you please point me in the direction with an example of the code.
>>
>> Example:
>>
>> Files in C:\Temp (a.xls, b.xls, c.xls) Copied to C:\Budget
>>
>> Many Thanks in Advance.
>
> -- 
>
> Dave Peterson 


0
7/7/2006 6:20:57 PM
You can incorporate some of the code from one of these sites:

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

John Walkenbach has one at:
http://j-walk.com/ss/excel/tips/tip29.htm

Your main routine will be:

Option Explicit
Sub testme01()
   
    Dim myNames() As String
    Dim fCtr As Long
    Dim myFile As String
    Dim myOldPath As String
    Dim myNewPath As String
    Dim MstrFileName As String
    Dim TempWkbk As Workbook
        
    'change to point at the old folder
    myOldPath = GetDirectory("Select OLD Folder")
    If myOldPath = "" Then Exit Sub
    If Right(myOldPath, 1) <> "\" Then
        myOldPath = myOldPath & "\"
    End If
    
    'change to point at the new folder
    myNewPath = GetDirectory("Select NEW Folder")
    If myNewPath = "" Then Exit Sub
    If Right(myNewPath, 1) <> "\" Then
        myNewPath = myNewPath & "\"
    End If
    
    MstrFileName = "mstr.xls"
    
    myFile = ""
    On Error Resume Next
    myFile = Dir(myOldPath & "*.xls")
    On Error GoTo 0
    If myFile = "" Then
        MsgBox "no files found"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    'get the list of files
    fCtr = 0
    Do While myFile <> ""
        fCtr = fCtr + 1
        ReDim Preserve myNames(1 To fCtr)
        myNames(fCtr) = myFile
        myFile = Dir()
    Loop
    
    If fCtr > 0 Then
        For fCtr = LBound(myNames) To UBound(myNames)
            If LCase(myNames(fCtr)) = LCase(MstrFileName) Then
                'do nothing, skip the master file
            Else
                Application.StatusBar _
                       = "Processing: " & myNames(fCtr) & " at: " & Now
                       
                Set TempWkbk = Workbooks.Open(Filename:=myOldPath _
                                   & myNames(fCtr), ReadOnly:=True)
                                
                TempWkbk.SaveAs Filename:=myNewPath & myNames(fCtr)
                TempWkbk.Close savechanges:=False
            End If
        Next fCtr
    End If
    
    With Application
        .ScreenUpdating = True
        .StatusBar = False
    End With
   
End Sub

But in a different module, put all this code (From John Walkenbach's site):

Option Explicit
Public Type BROWSEINFO
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type

'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
  Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) _
  As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
    Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long


Function GetDirectory(Optional Msg) As String
    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim r As Long, x As Long, pos As Integer
 
'   Root folder = Desktop
    bInfo.pidlRoot = 0&

'   Title in the dialog
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder."
    Else
        bInfo.lpszTitle = Msg
    End If
    
'   Type of directory to return
    bInfo.ulFlags = &H1

'   Display the dialog
    x = SHBrowseForFolder(bInfo)
    
'   Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetDirectory = Left(path, pos - 1)
    Else
        GetDirectory = ""
    End If
End Function





doctorjones_md@yahoo.com wrote:
> 
> Dave -- Thank you for your assistance -- the code worked brillantly!  It's
> Friday, so I hate to press my luck on this one, but is it possible to modify
> the code to allow for INPUT boxes for myOldPath & myNewPath (so that a user
> could designate the origination/destination paths without having to tinker
> with the code)?  Ideally, I'd like to placed a Command Button on the EXCEL
> worksheet, and when Pressed, display the (2) Input Boxes for myOldPath &
> myNewPath.
> 
> Any ideas on this modification?
> 
> Thanks again for your previous assistance!
> ================================================
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:44AE7E95.22EEFC19@verizonXSPAM.net...
> >I think that this does it:
> >
> > Option Explicit
> > Sub testme01()
> >
> >    Dim myNames() As String
> >    Dim fCtr As Long
> >    Dim myFile As String
> >    Dim myOldPath As String
> >    Dim myNewPath As String
> >    Dim MstrFileName As String
> >    Dim TempWkbk As Workbook
> >
> >    'change to point at the old folder
> >    myOldPath = "c:\my documents\excel\"
> >    If Right(myOldPath, 1) <> "\" Then
> >        myOldPath = myOldPath & "\"
> >    End If
> >
> >    'change to point at the new folder
> >    myNewPath = "c:\temp"
> >    If Right(myNewPath, 1) <> "\" Then
> >        myNewPath = myNewPath & "\"
> >    End If
> >
> >    'the master file--not to be copied
> >    MstrFileName = "mstr.xls"
> >
> >    myFile = ""
> >    On Error Resume Next
> >    myFile = Dir(myOldPath & "*.xls")
> >    On Error GoTo 0
> >    If myFile = "" Then
> >        MsgBox "no files found"
> >        Exit Sub
> >    End If
> >
> >    Application.ScreenUpdating = False
> >
> >    'get the list of files
> >    fCtr = 0
> >    Do While myFile <> ""
> >        fCtr = fCtr + 1
> >        ReDim Preserve myNames(1 To fCtr)
> >        myNames(fCtr) = myFile
> >        myFile = Dir()
> >    Loop
> >
> >    If fCtr > 0 Then
> >        For fCtr = LBound(myNames) To UBound(myNames)
> >            If LCase(myNames(fCtr)) = LCase(MstrFileName) Then
> >                'do nothing, skip the master file
> >            Else
> >                Application.StatusBar _
> >                       = "Processing: " & myNames(fCtr) & " at: " & Now
> >
> >                Set TempWkbk = Workbooks.Open(Filename:=myOldPath _
> >                                  & myNames(fCtr), ReadOnly:=True)
> >
> >                TempWkbk.SaveAs Filename:=myNewPath & myNames(fCtr)
> >                TempWkbk.Close savechanges:=False
> >            End If
> >        Next fCtr
> >    End If
> >
> >    With Application
> >        .ScreenUpdating = True
> >        .StatusBar = False
> >    End With
> >
> > End Sub
> >
> >
> >
> > doctorjones_md@yahoo.com wrote:
> >>
> >> Does anyone know how I can resolve this issue ...  I have a directory
> >> which
> >> contains 129 worksheets which have links to external data (in a Master
> >> Spreadsheet) -- I need to copy these files into a New Directory, but kee
> >> the
> >> Master Spreadsheet (which they are linked to) in the original location.
> >> If
> >> I do a simple Cut & Past, the Reference Link to the Master Spreadsheet
> >> gets
> >> moved to the New Directory (where the file does not exist), but if I open
> >> the worksheet (in the original directory/location) and Save As to the New
> >> Directory, the worksheet saved in the New Directory maintains its link to
> >> the Master Spreadsheet in the original directory/location.  I hope I've
> >> explained this clearly.
> >>
> >> Here's my problem -- it's a bit time consuming to have to open each and
> >> every worksheet and Save As to the New Location -- I'm not sure if a
> >> Batch
> >> File (or Dos Command xcopy) would solve this -- Is there some code I
> >> could
> >> use to Open each worksheet, Save As to the New directory, Close, then
> >> perform this on each of the .xls files in the original directory?  If so,
> >> could you please point me in the direction with an example of the code.
> >>
> >> Example:
> >>
> >> Files in C:\Temp (a.xls, b.xls, c.xls) Copied to C:\Budget
> >>
> >> Many Thanks in Advance.
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12004)
7/7/2006 6:30:09 PM
Dave -- sorry the the delayed response to your follow-up assistance (with 
the Browse To Option from John Walkenbach's site) -- this works 
Brillantly"  -- many thanks to you for all your assistance! :)


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:44AEA831.72DD9B17@verizonXSPAM.net...
> You can incorporate some of the code from one of these sites:
>
> Jim Rech has a BrowseForFolder routine at:
> http://www.oaltd.co.uk/MVP/Default.htm
> (look for BrowseForFolder)
>
> John Walkenbach has one at:
> http://j-walk.com/ss/excel/tips/tip29.htm
>
> Your main routine will be:
>
> Option Explicit
> Sub testme01()
>
>    Dim myNames() As String
>    Dim fCtr As Long
>    Dim myFile As String
>    Dim myOldPath As String
>    Dim myNewPath As String
>    Dim MstrFileName As String
>    Dim TempWkbk As Workbook
>
>    'change to point at the old folder
>    myOldPath = GetDirectory("Select OLD Folder")
>    If myOldPath = "" Then Exit Sub
>    If Right(myOldPath, 1) <> "\" Then
>        myOldPath = myOldPath & "\"
>    End If
>
>    'change to point at the new folder
>    myNewPath = GetDirectory("Select NEW Folder")
>    If myNewPath = "" Then Exit Sub
>    If Right(myNewPath, 1) <> "\" Then
>        myNewPath = myNewPath & "\"
>    End If
>
>    MstrFileName = "mstr.xls"
>
>    myFile = ""
>    On Error Resume Next
>    myFile = Dir(myOldPath & "*.xls")
>    On Error GoTo 0
>    If myFile = "" Then
>        MsgBox "no files found"
>        Exit Sub
>    End If
>
>    Application.ScreenUpdating = False
>
>    'get the list of files
>    fCtr = 0
>    Do While myFile <> ""
>        fCtr = fCtr + 1
>        ReDim Preserve myNames(1 To fCtr)
>        myNames(fCtr) = myFile
>        myFile = Dir()
>    Loop
>
>    If fCtr > 0 Then
>        For fCtr = LBound(myNames) To UBound(myNames)
>            If LCase(myNames(fCtr)) = LCase(MstrFileName) Then
>                'do nothing, skip the master file
>            Else
>                Application.StatusBar _
>                       = "Processing: " & myNames(fCtr) & " at: " & Now
>
>                Set TempWkbk = Workbooks.Open(Filename:=myOldPath _
>                                   & myNames(fCtr), ReadOnly:=True)
>
>                TempWkbk.SaveAs Filename:=myNewPath & myNames(fCtr)
>                TempWkbk.Close savechanges:=False
>            End If
>        Next fCtr
>    End If
>
>    With Application
>        .ScreenUpdating = True
>        .StatusBar = False
>    End With
>
> End Sub
>
> But in a different module, put all this code (From John Walkenbach's 
> site):
>
> Option Explicit
> Public Type BROWSEINFO
>    hOwner As Long
>    pidlRoot As Long
>    pszDisplayName As String
>    lpszTitle As String
>    ulFlags As Long
>    lpfn As Long
>    lParam As Long
>    iImage As Long
> End Type
>
> '32-bit API declarations
> Declare Function SHGetPathFromIDList Lib "shell32.dll" _
>  Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As 
> String) _
>  As Long
>
> Declare Function SHBrowseForFolder Lib "shell32.dll" _
>    Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long
>
>
> Function GetDirectory(Optional Msg) As String
>    Dim bInfo As BROWSEINFO
>    Dim path As String
>    Dim r As Long, x As Long, pos As Integer
>
> '   Root folder = Desktop
>    bInfo.pidlRoot = 0&
>
> '   Title in the dialog
>    If IsMissing(Msg) Then
>        bInfo.lpszTitle = "Select a folder."
>    Else
>        bInfo.lpszTitle = Msg
>    End If
>
> '   Type of directory to return
>    bInfo.ulFlags = &H1
>
> '   Display the dialog
>    x = SHBrowseForFolder(bInfo)
>
> '   Parse the result
>    path = Space$(512)
>    r = SHGetPathFromIDList(ByVal x, ByVal path)
>    If r Then
>        pos = InStr(path, Chr$(0))
>        GetDirectory = Left(path, pos - 1)
>    Else
>        GetDirectory = ""
>    End If
> End Function
>
>
>
>
>
> doctorjones_md@yahoo.com wrote:
>>
>> Dave -- Thank you for your assistance -- the code worked brillantly! 
>> It's
>> Friday, so I hate to press my luck on this one, but is it possible to 
>> modify
>> the code to allow for INPUT boxes for myOldPath & myNewPath (so that a 
>> user
>> could designate the origination/destination paths without having to 
>> tinker
>> with the code)?  Ideally, I'd like to placed a Command Button on the 
>> EXCEL
>> worksheet, and when Pressed, display the (2) Input Boxes for myOldPath &
>> myNewPath.
>>
>> Any ideas on this modification?
>>
>> Thanks again for your previous assistance!
>> ================================================
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:44AE7E95.22EEFC19@verizonXSPAM.net...
>> >I think that this does it:
>> >
>> > Option Explicit
>> > Sub testme01()
>> >
>> >    Dim myNames() As String
>> >    Dim fCtr As Long
>> >    Dim myFile As String
>> >    Dim myOldPath As String
>> >    Dim myNewPath As String
>> >    Dim MstrFileName As String
>> >    Dim TempWkbk As Workbook
>> >
>> >    'change to point at the old folder
>> >    myOldPath = "c:\my documents\excel\"
>> >    If Right(myOldPath, 1) <> "\" Then
>> >        myOldPath = myOldPath & "\"
>> >    End If
>> >
>> >    'change to point at the new folder
>> >    myNewPath = "c:\temp"
>> >    If Right(myNewPath, 1) <> "\" Then
>> >        myNewPath = myNewPath & "\"
>> >    End If
>> >
>> >    'the master file--not to be copied
>> >    MstrFileName = "mstr.xls"
>> >
>> >    myFile = ""
>> >    On Error Resume Next
>> >    myFile = Dir(myOldPath & "*.xls")
>> >    On Error GoTo 0
>> >    If myFile = "" Then
>> >        MsgBox "no files found"
>> >        Exit Sub
>> >    End If
>> >
>> >    Application.ScreenUpdating = False
>> >
>> >    'get the list of files
>> >    fCtr = 0
>> >    Do While myFile <> ""
>> >        fCtr = fCtr + 1
>> >        ReDim Preserve myNames(1 To fCtr)
>> >        myNames(fCtr) = myFile
>> >        myFile = Dir()
>> >    Loop
>> >
>> >    If fCtr > 0 Then
>> >        For fCtr = LBound(myNames) To UBound(myNames)
>> >            If LCase(myNames(fCtr)) = LCase(MstrFileName) Then
>> >                'do nothing, skip the master file
>> >            Else
>> >                Application.StatusBar _
>> >                       = "Processing: " & myNames(fCtr) & " at: " & Now
>> >
>> >                Set TempWkbk = Workbooks.Open(Filename:=myOldPath _
>> >                                  & myNames(fCtr), ReadOnly:=True)
>> >
>> >                TempWkbk.SaveAs Filename:=myNewPath & myNames(fCtr)
>> >                TempWkbk.Close savechanges:=False
>> >            End If
>> >        Next fCtr
>> >    End If
>> >
>> >    With Application
>> >        .ScreenUpdating = True
>> >        .StatusBar = False
>> >    End With
>> >
>> > End Sub
>> >
>> >
>> >
>> > doctorjones_md@yahoo.com wrote:
>> >>
>> >> Does anyone know how I can resolve this issue ...  I have a directory
>> >> which
>> >> contains 129 worksheets which have links to external data (in a Master
>> >> Spreadsheet) -- I need to copy these files into a New Directory, but 
>> >> kee
>> >> the
>> >> Master Spreadsheet (which they are linked to) in the original 
>> >> location.
>> >> If
>> >> I do a simple Cut & Past, the Reference Link to the Master Spreadsheet
>> >> gets
>> >> moved to the New Directory (where the file does not exist), but if I 
>> >> open
>> >> the worksheet (in the original directory/location) and Save As to the 
>> >> New
>> >> Directory, the worksheet saved in the New Directory maintains its link 
>> >> to
>> >> the Master Spreadsheet in the original directory/location.  I hope 
>> >> I've
>> >> explained this clearly.
>> >>
>> >> Here's my problem -- it's a bit time consuming to have to open each 
>> >> and
>> >> every worksheet and Save As to the New Location -- I'm not sure if a
>> >> Batch
>> >> File (or Dos Command xcopy) would solve this -- Is there some code I
>> >> could
>> >> use to Open each worksheet, Save As to the New directory, Close, then
>> >> perform this on each of the .xls files in the original directory?  If 
>> >> so,
>> >> could you please point me in the direction with an example of the 
>> >> code.
>> >>
>> >> Example:
>> >>
>> >> Files in C:\Temp (a.xls, b.xls, c.xls) Copied to C:\Budget
>> >>
>> >> Many Thanks in Advance.
>> >
>> > --
>> >
>> > Dave Peterson
>
> -- 
>
> Dave Peterson 


0
doctorjones_md
7/12/2006 9:59:55 AM
Reply:

Similar Artilces:

Linked graphics into a Publisher 2000 document
I have a 400KB document (withgout graphics). I insert two JPEG images each around 200KB into the document. I figure the resulting document should be around 700KB. Result? An 11MB file. I delete the images (document is now 400KB) and instead insert the images using the "Link to File" option. Graphics Manager confirms that the images are linbked into the file. The size should not be more than 500KB. Result? An 11MB file. Question is how can I insert images into a Publisher 2000 documentr without the file blowing up? And why does it blow up? Regards, Richard. Richard wrot...

Macros for worksheet copy & paste?
I have 6 different excel files. How can I combine all 6 of them in one single excel file under individual worksheets (E.g. Worksheet1 for File1, worksheet2 for File2 ....etc)? I do not wish to copy & paste it manually. Can I use a macro to take care of it? If so, where can I find further info on how to go about it? THANKS!! Assuming (1) this is a one-time need, and (2) your workbooks have only one sheet each, this would probably be faster done by hand: 1) With all the files open choose one of the files to be the receiving file. 2) Select the sheet in the subsequent books, one at...

SUMIF function not calculating from closed worksheet
We recently upgraded from Microsoft Office 2000 to 2007. Prior to the upgrade each department would access a spreadsheet that would load data from a common lookup spreadsheet The loading process was performed as follows: The lookup file and each department file would be opened to update the link for that month and then saved and closed. When the department head would open the department file, a question would appear asking if they wanted to update the link, if they answered no the spreadsheet would open and display the calculated data. If they answered yes, the file would o...

excel, worksheet, set print area, position selection on printed pa
Office 07, Excel. Print area is set, print preview displays selection, always on far left of page. I want to choose the position on the page, eg centered or right aligned etc. Help please. Hi, To get your printout to center on the page chose Page Layout, click the Page Setup quick launch button (bottom right corner of the Page Setup group). Choose the Margins tab, click Horizontal. There is no command to right align a printout, but you can change the left margin to force the printout to the left. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Eve Al...

Embedding worksheet into Word
Has anyone had the experience where when they embed a worksheet into Microsoft Word, a portion of the worksheet gets cut off? Is there an easy fix to this? ...

Problem with systemstate backup with DPM 2010
Hi! We running DPM 2010RC on a Windows 2008 R2 The Protected computer is: Microsoft(R) Windows(R) Server 2003, Standard Edition 5.2.3790 Service Pack 2 Build 3790 When we installed the DPM agent the first backup of system state was successfully, so we have one restore point. But further backups in failing, the system state backup is in status “replica is inconsistent” and when I running a consistent check the job failing. In the error in DPM is: The replica of System Protection Computer\System Protection on sea0700smon1.xxxx.local is inconsistent with the protected data so...

Questiontest-outlookwebservices problem
hi all and thanks for the support. i'm facing a problem with test.outlookwebservices. notice that oulook side everything seems to be pretty good: the autodiscovery and auto-configuration of the account succeedes, the test email autoconfiguration suceedes too. when i launch the test, once it suceedes for certain services and after a while it fails for those services and suceedes for other. look the prints: here are failing the RPC tests Message : [EXCH] The UM is configured for this user in the AutoDiscover response received from https://mytest-EXC1. myt...

Fewer normal template problems with Word 2000 or 2002?
I have enjoyed some things about Word 2007, but I keep having problems with macros and templates. I am wondering if (a) macros increase the likelihood of corrupting the normal template in 2007, or (b) if the older versions of Word had as many template problems. I have used Word in 2000, with Office 2003, and now with Office 2007 and I don't ever recall having as many problems as I've had with 2007. What I'm thinking is that if it is simply a macro problem causing corruption, I would simply use 2007 without the macros (they're nice but I can live without them). On the...

Font problem
It seems that the Cambria Math font has recently acquired an extremely large Ascent and Descent. Has it perhaps always been that way and I've never noticed it before, or is it something recent, perhaps a Vista thing? Try the following code for example. For Arial and Times New Roman (and virtually all other fonts) I am getting pretty much exactly what I would expect (a TextHeight that is a bit larger than the point size) but for Cambria Math I am getting an extremely large TextHeight (I get exactly the same results using GDI32 methods). At this end (on my Vista Business laptop...

08 Mac Office activation problems
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Just found an article posted by michellec and I am faced with exactly the same problem - on 'activation' of the product, none of the 3 product keys seem to be correct, hence activation is unsuccessful!! Have received some help emails (and thanks Diana yours had the microsoft help number that has been good, despite the fact they can't help me and tell me I must get in touch with Digital River) but seems that what I need is another product key, and now that I'm asking for that since about 10 days, I don't get any reply ...

Error saving a Shared Workbook
I have a user using a shared workbook and gets an error everytime she does this ONE specific change. She deletes a line and then saves and gets a message that says, "EXCEL.exe has generated errors and will be shutdown by Windows... blah blah blah". If she makes anyother type of changes it saves just fine. I also had another user do the SAME change and that person also got that error message. When the file is changed to "Not Shared" then the line is deleted it saves fine also. The problem is obviously with the file itself and that is shared out. can anybody ...

Autoshapes' visible problem
When I add some Rectangle autoshapes above the embeded chart, I find it's hard to control it's visibility, They maybe hide when I select some cell or activate chart. I want to know how to make them always visible no matter what I select. Thank you in advance! ^_^ Yours,fujing You need to click on the chart before you use the drawing tool. In this way the Shape and the Chart become one. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "fujing1003" <fujing1003@gmail.com> wrote in message news:1171358292.074278.135950@v33g2000cwv...

Creating a worksheet in Spanish
Hello, Is there a way to create a worksheet on MS XP Pro running Office 2003 Pro all in Spanish? Thank You Kent Hansen Hi Kent All valid formulas -except the ones from the Analysis Toolpack- will automatically translate to the language of the Excel it's opened in. So any spanish speaking Excel will translate formulas to spanish, no matter which version it was written in. The rest, text constants and such, has to be typed or pasted. Excel will not translate this herself. HTH. Best wishes Harald "Kent" <Kent@discussions.microsoft.com> skrev i melding news:ACCCBA3A-53B4...

How do I combine two worksheets into one graph
I have two worksheets on an Excel spreadsheet. I would like to take both worksheets and place in one graph. Any suggestions? Make a chart with data from one sheet. Then copy the data on the other sheet, select the chart, and use Paste Special from the Edit menu to add the copied data as New Series. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ McPowerUser wrote: > I have two worksheets on an Excel spreadsheet. I would like to take both > worksheets and place in one graph. Any suggestions? ...

Link to Power Point
Hi, I placed a hyperlink to a powerpoint file on my web page. Upon clicking on the link the "Run/Save" window comes up. Is it possible to just have the Power Point file run without the "Run/Save" window coming up? If so, how? Thanks, -- Phil Posting a question 4 times isn't going to get you an answer any quicker. Try to be a bit more patient. Craig Phil wrote: >Hi, >I placed a hyperlink to a powerpoint file on my web page. Upon clicking on >the link the "Run/Save" window comes up. Is it possible to just have the >P...

Hyper link one column to another
I would like to have my columns hyper link one another. For example: Click on B2 would take you to N2, and vice-versa. Click on C2 would take you to O2... and so on through column j linked to V. Is this possible without having to make the link for each cell? This is a timesheet template and the columns b though J are the hours and N through V are the text comments for those hours. When I copy the template to a new sheet, there will be no data in any column. I would like to be able to enter an amount or formula (=end-start) for time spent and then be able to click on that cell and hyperlin...

linking sheets
Hi All, How do i link my drop down list in a13 sheet 1 to column f in sheet 2. Hi Jinx if using Data Validation to create your drop down list the easiest way is to range name column F in sheet 2 (go to sheet 2, click on F, click in name box (left hand side of formula bar) type name (no spaces), press enter) then click in Sheet1!A13, choose Data / Validation, choose List - click IN the big white box, press you F3 key to bring up list of names in workbook, choose the name, click OK, click OK again and it should work fine. Hope this helps Cheers JulieD "Jinx" <anonymous@discus...

Office 2004 SP 11.1.0 installation problems
I'm trying to update Office 2004 on my brand new iBook. I installed Office 2004 from the CD without incident. Then I downloaded SP 11.1.1.0 and 11.1.0 (which according to the MS website is to be installed 1st.) When I try to install, I received a message, "An error prevented the update from completing 11002:2,-14" The "Read Me" file indicates several potential explainatins/solutions none of whice are relevant except for uninstalling Office and then reinstalling it. I tried dragging the Office folder to the trash and reinstalled and am having the same problem. I've t...

RMS and/or PC Charge Problems
We are having some interesting problems with our RMS system and PC Charge: For no apparent reason RMS will have an error message after a card is swiped or keyed into the system: • DENIED (With a wide variety of error messages) • DUPLICATE use F+ to force (even when it is the first transaction) • TIMED OUT Attempted solutions: • Turned modem off and on • Disconnected the modem and plugged everything back in • Turned the server on/off • Called PC Charge only to be told that it is RMS • RMS said it is PC Charge • Confirmed that everything is set up properly in PC Charge and RMS What works:...

Macro on a protected worksheet in a shared workbook.
Hi, I have a macro in protected worksheets that can't run once the Workbook is Shared. I have wrapped the Macro code so as the worksheet is unprotected for the time the Macro runs, and unable the Autofilter. The code is as follow at the moment: Sub Newaction() Sheets("Critical Path").Unprotect ("") ' Newaction Macro ' Macro recorded 17/11/2004 by Clifford ' ' Selection.AutoFilter Field:=1, Criteria1:="=" Sheets("Critical Path").EnableAutoFilter = True Sheets("Critical Path").Protect contents:=True, userInter...

Remove format link?
Is there a way to remove the format link between fields in Modifier? Help just shows how to set it and the usual stuff doesn't seem to be working. ...

Outlook 2003 Signature Problem
I've created a signature with my company logo (.jpg file) using Outlook 2003. When the signature is attached in my email, the photo/icon file shows a box with an 'x' in place of the .jpg file. I checked the location where the files are stored and I found the .jpg file in the proper folder under signatures. Thanks in advance for your help. Mike Tools - options -security tab, click on change automatic download settings. Change to what you want. "Mikeul" <Mikeul@hotmail.com> wrote in message news:1132112656.7e1c9b39fdb73a3c7ed44fdbd3e3fbc4@fe5.teranews.com......

Problem in restricting users from sending/receving internet email
I have found a helpful articel here: http://www.msexchange.org/tutorials/MF009.html I read it through couple times but I would like to ask some questions before I start the work on our Exchange 2000 server. 1. The "group" I am going to create for adding users into the restricting list, will it be a Security group or Distribution group? What group scope should I give it? 2. Under the section of "Restricting Users from Sending Internet Base Email", the article tells us to create a *new* SMTP connector and add the group into the "Dilivery Restriction" page. Will ...

Events template not saving entries
I am attempting to build an events database using the Events template in Access 2007. I have made no modifications to the template. It is not saving the information I add into the Event List table. I first tried entering information directly into the table. Then I tried entering information using the New Event in the Link bar. I can find no way to get the entries to save. Has anyone else had this problem? Is there a fix somewhere? Does anyone have anything to suggest? Thanks. -- M'Lou *:) On Sun, 7 Mar 2010 05:05:01 -0800, M''Lou Wilson <MLouWilson@discussions....

Outlook Draft Email Print Problem
I have emails saved in draft but when i go to print them it doesnt show who it is being sent to and the fact it hasnt been sent yet, i have played with the setting but to no avail. I am using outlook 2003 any help would be appriciated. Turn off WordMail before printing the draft (Tools | Options | Mail Format) or wait to print until after you send it. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx <kpmail76@yahoo.co.uk> wrote in message news:...