Q: break link and save
 

Hello,
I am using the following code to create workbook for each sheet that I have
in my main workbook. It works fine, my problem is links. Sheets have links to
other files and I need to get rid of them by keeping the values. Is there any
way I can break links and save the current values during SaveAs below?

Sub test()
Dim a As Integer
Dim wb As Workbook
Application.ScreenUpdating = False
For a = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Sheets(a).Copy
Set wb = ActiveWorkbook
wb.SaveAs "C:\Tmp\" & wb.Sheets(1).Name & ".xls"
wb.Close False
Set wb = Nothing
Next a
Application.ScreenUpdating = True
End Sub

Thanks,
Jim.

break link and save
 

Jim -

Check out the LinkSources method and its example in the help files. It
looks like you could use that on your wb object to get the links and then
use BreakLink to break the links. The BreakLink method's example seems even
closee to what you want...

HTH

James Cox



"JIM.H." wrote in message
...
Hello,
I am using the following code to create workbook for each sheet that I
have
in my main workbook. It works fine, my problem is links. Sheets have links
to
other files and I need to get rid of them by keeping the values. Is there
any
way I can break links and save the current values during SaveAs below?

Sub test()
Dim a As Integer
Dim wb As Workbook
Application.ScreenUpdating = False
For a = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Sheets(a).Copy
Set wb = ActiveWorkbook
wb.SaveAs "C:\Tmp\" & wb.Sheets(1).Name & ".xls"
wb.Close False
Set wb = Nothing
Next a
Application.ScreenUpdating = True
End Sub

Thanks,
Jim.

break link and save
 

I am new in this. which help file has LinkSources. Excel help does not show it.


"James Cox" wrote:

Jim -

Check out the LinkSources method and its example in the help files. It
looks like you could use that on your wb object to get the links and then
use BreakLink to break the links. The BreakLink method's example seems even
closee to what you want...

HTH

James Cox



"JIM.H." wrote in message
...
Hello,
I am using the following code to create workbook for each sheet that I
have
in my main workbook. It works fine, my problem is links. Sheets have links
to
other files and I need to get rid of them by keeping the values. Is there
any
way I can break links and save the current values during SaveAs below?

Sub test()
Dim a As Integer
Dim wb As Workbook
Application.ScreenUpdating = False
For a = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Sheets(a).Copy
Set wb = ActiveWorkbook
wb.SaveAs "C:\Tmp\" & wb.Sheets(1).Name & ".xls"
wb.Close False
Set wb = Nothing
Next a
Application.ScreenUpdating = True
End Sub

Thanks,
Jim.

break link and save
 

go to the VBE and open the object browser. Search the Excel library for
LinkSources

It should return linkSources as an element of the Workbook Object. Click on
the linksources entry and hit the F1 key to get help.

--
Regards,
Tom Ogilvy

"JIM.H." wrote in message
...
I am new in this. which help file has LinkSources. Excel help does not
show it.


"James Cox" wrote:

Jim -

Check out the LinkSources method and its example in the help files. It
looks like you could use that on your wb object to get the links and
then
use BreakLink to break the links. The BreakLink method's example seems
even
closee to what you want...

HTH

James Cox



"JIM.H." wrote in message
...
Hello,
I am using the following code to create workbook for each sheet that I
have
in my main workbook. It works fine, my problem is links. Sheets have
links
to
other files and I need to get rid of them by keeping the values. Is
there
any
way I can break links and save the current values during SaveAs below?

Sub test()
Dim a As Integer
Dim wb As Workbook
Application.ScreenUpdating = False
For a = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Sheets(a).Copy
Set wb = ActiveWorkbook
wb.SaveAs "C:\Tmp\" & wb.Sheets(1).Name & ".xls"
wb.Close False
Set wb = Nothing
Next a
Application.ScreenUpdating = True
End Sub

Thanks,
Jim.