Hi Everybody,

I have an excel workbook with code that generates a separate workbook
with a unique file name.

This is my code to create a new workbook:


Code:
--------------------
With Sheets("Invoice2")
Dim invName As String
Sheets("Invoice2").Select
invName = Range("L4").Value & ".xls"
Sheets("Invoice2").Copy
ChDir "C:\Users\Justin\Documents\Razza Jam"

'Will ignore the error
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=invName
On Error GoTo 0

'Will close without another alert message
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End With
End Sub
--------------------


Basically, for the Invoice Worksheet, when a button is clicked a
separate workbook is generated with just the current invoice worksheet.


Everything works fine but when I open the newly created file, I get
prompted with this message:

"This workbook contains links to other data sources:

* If you update the links, Excel will attempt to retrieve the latest
data.
* If you don't update the links, Excel will use the previous
information."

Then at the bottom of the warning box there is 'update' and 'don't
update' and 'help'.

Is there anyway to eliminate this message coming up each time I create
a new file? It's just something which is shared and to be honest I would
be happy if the opened file was read only.

Any help would be greatly appreciated.

Thank you.

Kind regards,

Polar


--
polar
------------------------------------------------------------------------
polar's Profile: http://www.thecodecage.com/forumz/member.php?userid=564
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120950

Hi Polar

The new sheet links its values to the original sheet. You can use
PasteSpecial to transform all formulas in the new sheet into values, and
then there will be no link to the original workbook.

Dim invName As String
Dim MyPath As String
MyPath = "C:\Users\Justin\Documents\Razza Jam\"
With Sheets("Invoice2")
invName = .Range("L4").Value & ".xls"
.Copy

'Will ignore the error
On Error Resume Next
ActiveSheet.UsedRange.Copy
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
ActiveWorkbook.SaveAs Filename:=MyPath & invName
On Error GoTo 0

'Will close without another alert message
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End With

Hopes this helps.
....
Per

"polar" skrev i meddelelsen
...

Hi Everybody,

I have an excel workbook with code that generates a separate workbook
with a unique file name.

This is my code to create a new workbook:


Code:
--------------------
With Sheets("Invoice2")
Dim invName As String
Sheets("Invoice2").Select
invName = Range("L4").Value & ".xls"
Sheets("Invoice2").Copy
ChDir "C:\Users\Justin\Documents\Razza Jam"

'Will ignore the error
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=invName
On Error GoTo 0

'Will close without another alert message
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End With
End Sub
--------------------


Basically, for the Invoice Worksheet, when a button is clicked a
separate workbook is generated with just the current invoice worksheet.


Everything works fine but when I open the newly created file, I get
prompted with this message:

"This workbook contains links to other data sources:

* If you update the links, Excel will attempt to retrieve the latest
data.
* If you don't update the links, Excel will use the previous
information."

Then at the bottom of the warning box there is 'update' and 'don't
update' and 'help'.

Is there anyway to eliminate this message coming up each time I create
a new file? It's just something which is shared and to be honest I would
be happy if the opened file was read only.

Any help would be greatly appreciated.

Thank you.

Kind regards,

Polar


--
polar
------------------------------------------------------------------------
polar's Profile: http://www.thecodecage.com/forumz/member.php?userid=564
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=120950

This should do what you need!

Code:
--------------------
Dim ws As Worksheet
Dim invName As String
With Sheets("Invoice2")
Sheets("Invoice2").Select
invName = Range("L4").Value & ".xls"
Sheets("Invoice2").Copy
For Each ws In ActiveWorkbook.Worksheets
With ws.UsedRange
.Copy
.PasteSpecial xlValues
End With
Application.CutCopyMode = False
Next ws
ChDir "C:\Users\Justin\Documents\Razza Jam"
'Will ignore the error
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=invName, SetAttr:=ReadOnly
On Error GoTo 0
'Will close without another alert message
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End With
--------------------


polar;435705 Wrote:
Hi Everybody,

I have an excel workbook with code that generates a separate workbook
with a unique file name.

This is my code to create a new workbook:


Code:
--------------------
With Sheets("Invoice2")
Dim invName As String
Sheets("Invoice2").Select
invName = Range("L4").Value & ".xls"
Sheets("Invoice2").Copy
ChDir "C:\Users\Justin\Documents\Razza Jam"

'Will ignore the error
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=invName
On Error GoTo 0

'Will close without another alert message
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End With
End Sub
--------------------


Basically, for the Invoice Worksheet, when a button is clicked a
separate workbook is generated with just the current invoice
worksheet.

Everything works fine but when I open the newly created file, I get
prompted with this message:

"This workbook contains links to other data sources:

* If you update the links, Excel will attempt to retrieve the latest
data.
* If you don't update the links, Excel will use the previous
information."

Then at the bottom of the warning box there is 'update' and 'don't
update' and 'help'.

Is there anyway to eliminate this message coming up each time I
create a new file? It's just something which is shared and to be
honest I would be happy if the opened file was read only.

Any help would be greatly appreciated.

Thank you.

Kind regards,

Polar


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120950

Hi Per and Simon,

Thank you for your responses. Unfortunately, I could not get the code
to work which you gave me Per. Thanks for introducing paste special code
to me though because I didn't know about that.

Simon, I used your code and I was able to save a new workbook. However,
I had to take this line out:


Code:
--------------------
SetAttr:=ReadOnly
--------------------


When I put this is on I got a message saying "Compile Error, Argument
Not Found." Is there a way to eliminate this error message as the read
only aspect is very useful.

Also, when I open the new file all the cells which were copied are
selected by highlighting. Is there a way to deselect them and just have
cell A1 selected without the highlighting?

Thank you again Simon and Per. I really appreciate all of your help.

Kind regards,

Polar


Simon Lloyd;435753 Wrote:
This should do what you need!

Code:
--------------------
Dim ws As Worksheet
Dim invName As String
With Sheets("Invoice2")
Sheets("Invoice2").Select
invName = Range("L4").Value & ".xls"
Sheets("Invoice2").Copy
For Each ws In ActiveWorkbook.Worksheets
With ws.UsedRange
.Copy
.PasteSpecial xlValues
End With
Application.CutCopyMode = False
Next ws
ChDir "C:\Users\Justin\Documents\Razza Jam"
'Will ignore the error
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=invName, SetAttr:=ReadOnly
On Error GoTo 0
'Will close without another alert message
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End With
--------------------



--
polar
------------------------------------------------------------------------
polar's Profile: http://www.thecodecage.com/forumz/member.php?userid=564
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120950

My apologies, this will work for you!

Code:
--------------------
Dim ws As Worksheet
Dim invName As String
With Sheets("Invoice2")
Sheets("Invoice2").Select
invName = Range("L4").Value & ".xls"
Sheets("Invoice2").Copy
For Each ws In ActiveWorkbook.Worksheets
With ws.UsedRange
.Copy
.PasteSpecial xlValues
End With
Range("A1").Activate
Application.CutCopyMode = False
Next ws
ChDir "C:\Users\Justin\Documents\Razza Jam"
'Will ignore the error
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=invName, _
Password:="", WriteResPassword:="password", _
ReadOnlyRecommended:=True, CreateBackup:=False
On Error GoTo 0
'Will close without another alert message
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End With
--------------------


polar;435779 Wrote:
Hi Per and Simon,

Thank you for your responses. Unfortunately, I could not get the code
to work which you gave me Per. Thanks for introducing paste special code
to me though because I didn't know about that.

Simon, I used your code and I was able to save a new workbook. However,
I had to take this line out:


Code:
--------------------
SetAttr:=ReadOnly
--------------------


When I put this is on I got a message saying "Compile Error, Argument
Not Found." Is there a way to eliminate this error message as the read
only aspect is very useful.

Also, when I open the new file all the cells which were copied are
selected by highlighting. Is there a way to deselect them and just
have cell A1 selected without the highlighting?

Thank you again Simon and Per. I really appreciate all of your help.

Kind regards,

Polar


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120950

Hi Simon,

That works very well. Thank you very much for that!

Just a quick question, if I was to open the new file as read only
without the password, is there a way to do that?


Code:
--------------------
Password:="", WriteResPassword:="password", _
ReadOnlyRecommended:=True, CreateBackup:=False
--------------------


I tried taking out the top line and changing 'ReadOnlyRecommended' to
'ReadOnly' but I had no success. When opened as 'ReadOnlyRecommended' I
also get a dialog box which I would prefer not to have.

Thanks again for your reply and code. This is a really terrific forum!


Kind regards,

Polar

Simon Lloyd;435802 Wrote:
My apologies, this will work for you!

Code:
--------------------
Dim ws As Worksheet
Dim invName As String
With Sheets("Invoice2")
Sheets("Invoice2").Select
invName = Range("L4").Value & ".xls"
Sheets("Invoice2").Copy
For Each ws In ActiveWorkbook.Worksheets
With ws.UsedRange
.Copy
.PasteSpecial xlValues
End With
Range("A1").Activate
Application.CutCopyMode = False
Next ws
ChDir "C:\Users\Justin\Documents\Razza Jam"
'Will ignore the error
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=invName, _
Password:="", WriteResPassword:="password", _
ReadOnlyRecommended:=True, CreateBackup:=False
On Error GoTo 0
'Will close without another alert message
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End With
--------------------



--
polar
------------------------------------------------------------------------
polar's Profile: http://www.thecodecage.com/forumz/member.php?userid=564
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120950

It is very difficult to set the read only attribute when you already
have another workbook open, anyway try this:

Code:
--------------------
Dim ws As Worksheet
Dim invName As String
With Sheets("Invoice2")
Sheets("Invoice2").Select
invName = Range("L4").Value & ".xls"
Sheets("Invoice2").Copy
For Each ws In ActiveWorkbook.Worksheets
With ws.UsedRange
.Copy
.PasteSpecial xlValues
End With
Range("A1").Activate
Application.CutCopyMode = False
Next ws
ChDir "C:\Users\Justin\Documents\Razza Jam"
'Will ignore the error
On Error Resume Next
SetAttr ActiveWorkbook.FullName, vbReadOnly
ActiveWorkbook.SaveAs Filename:=invName
On Error GoTo 0
'Will close without another alert message
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End With
--------------------


polar;435848 Wrote:
Hi Simon,

That works very well. Thank you very much for that!

Just a quick question, if I was to open the new file as read only
without the password, is there a way to do that?


Code:
--------------------
Password:="", WriteResPassword:="password", _
ReadOnlyRecommended:=True, CreateBackup:=False
--------------------


I tried taking out the top line and changing 'ReadOnlyRecommended' to
'ReadOnly' but I had no success. When opened as 'ReadOnlyRecommended'
I also get a dialog box which I would prefer not to have.

Thanks again for your reply and code. This is a really terrific
forum!

Kind regards,

Polar


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120950

Hi Simon,

Thank you again for your quick response.

Unfortunately, I couldn't get the read only code working. However,
it's not a huge issue and I'm grateful for all of your help. Having the
main code and just creating a new workbook is all I need.

Thanks again. Bg:)

Kind regards,

Polar

Simon Lloyd;435875 Wrote:
It is very difficult to set the read only attribute when you already
have another workbook open, anyway try this:

Code:
--------------------
Dim ws As Worksheet
Dim invName As String
With Sheets("Invoice2")
Sheets("Invoice2").Select
invName = Range("L4").Value & ".xls"
Sheets("Invoice2").Copy
For Each ws In ActiveWorkbook.Worksheets
With ws.UsedRange
.Copy
.PasteSpecial xlValues
End With
Range("A1").Activate
Application.CutCopyMode = False
Next ws
ChDir "C:\Users\Justin\Documents\Razza Jam"
'Will ignore the error
On Error Resume Next
SetAttr ActiveWorkbook.FullName, vbReadOnly
ActiveWorkbook.SaveAs Filename:=invName
On Error GoTo 0
'Will close without another alert message
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End With
--------------------



--
polar
------------------------------------------------------------------------
polar's Profile: http://www.thecodecage.com/forumz/member.php?userid=564
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120950

Glad we could be of help!


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120950