Finding name of file

[Francis Hookham]

Finding name of file

I need to find the name of the active XL workbook (let us call it by it¹s
Job and Drawing No. - "000237_047") and assign that name to variable
"OriginalWorkbook". From this I shall name a second variable, "NewWorkbook",
by adding an underscore and the date in the format "dmmmyy" and using this
to name a new Read Only* workbook.

NewWorkbook will contain two of the worksheets from OriginalWorkbook so I
thought it would most easily be automatically generated by moving the first
worksheet to a new workbook, naming it NewWorkbook and the going back to
OriginalWorkbook to copy the second sheet

I am stumped over obtaining the name of the original workbook.

Finally I must "Save As" the new workbook as "Read Only".

Simply saving the original workbook under a different name would mean having
to remove several unwanted worksheets as well as macros - as it is I shall
have to remove some buttons on the copied sheets but they are named and that
should be easy

I think I can do all of this once you have shown me how to generate the two
variables but please let me have any tips - thanks once again.

Francis Hookham

* I have assumed "Read Only" is what I want but I need to look at
"Protecting" each sheet to understand which does what and which is best in
this case.

 

[Bernard Rey]

Hello Francis,

Basically, the lines hereunder should perform what you ask.

OriginalWorkbook = ActiveWorkbook.Name ' Stores the name
TodaysDate = Format(Now, "dmmmyy")
NewWorkbook = OriginalWorkbook & "_" & TodaysDate

Sheets("MySheet1").Copy ' Creates new workbook
ActiveWorkbook.SaveAs FileName:=NewWorkbook

Workbooks(OriginalWorkbook).Sheets("MySheet2").Copy _
After:=Workbooks(NewWorkbook).Sheets(1) ' Copying the 2nd sheet

If you're not too familiar with macros, you can try and record macros, but
it usually need some "cleaning" afterwards, as every default setting is
being recorded though it is useless.

As to protecting sheets, the needed line would be:
Sheets("MySheet").Protect "MyPassword"

Or, if "read only" is required:
Workbooks("MyWorkbook").SaveAs WriteResPassword:="MyPassword"

Now, if this is not clear enough or goes somewhat wrong, post again,


--
Bernard Rey - Toulouse / France
MVP - Macintosh


Francis Hookham wrote :

 

[Bernard Rey]

NewWorkbook = NewWorkbook & "_" & TodaysDate

Well, to have it totally "cross-platform", you might change this line for:

NewWorkbook = NewWorkbook & "_" & TodaysDate & ".xls"

It's not that it wouldn't work but, this way, the names will be strictly
identical wherever you run your macro. I hadn't checked this. So here it is.



Please reply to the newsgroup, and within the same thread.
Merci de répondre au groupe, et dans l'enfilade.

--
Bernard Rey - Toulouse / France
MVP - Macintosh


Bernard Rey wrote :