The right header reads DOC. NO &F
I need a macro to replace &F with the filename as text
(To be run on the contents of a folder one time)

(Our new data management system adds a dot numeric value to the file
name, and it is confusing the users.)

I would really appreciate any suggestions!

Michelle ;)


--
amthyst826
------------------------------------------------------------------------
amthyst826's Profile: http://www.excelforum.com/member.php...o&userid=28308
View this thread: http://www.excelforum.com/showthread...hreadid=478853

Generally, the code looks like this:

Sub Filename_in_header()
With ActiveSheet.PageSetup
.RightHeader = "Doc No. " & Mid(ActiveSheet.Name, 1, 5)
End With
End Sub

This applies the first five characters of the filename to the header-
see the MID statement. You'll need to parse your filename to suit,
maybe with LEFT or RIGHT instead of MID, depending on how the data mgt
system adds its numeric value.

The name I want to apply is the name of the workbook, not the active
sheet.

Can I get a macro to run on all of the files within a folder? I have
over four hundred of these to change.

I've taken copies out of the datamanagement system, I need to make this
change, then I'll put the updated copies back.

Michelle


--
amthyst826
------------------------------------------------------------------------
amthyst826's Profile: http://www.excelforum.com/member.php...o&userid=28308
View this thread: http://www.excelforum.com/showthread...hreadid=478853

The code that follows will ask you to select the files you want to
change. This is a one-file-at-a-time deal, unfortunately; the code to
automate the entire process is a touch trickier than I have time for at
this moment.

The code applies the new header to each tab in the document, using the
filename instead of the tab name. You'll still need to work out the
parsing- changing the file from MyFile123456.xls to MyFile.

Sub Filename_in_header()
Dim SName As Variant
Dim Response As String

Do

'select a file
Application.Dialogs(xlDialogOpen).Show

'Apply new header to each sheet in the file
For Each SName In Sheets
Sheets(SName.Name).Select
With ActiveSheet.PageSetup
.RightHeader = "Doc No. " & Mid(ActiveWorkbook.Name, 1, 5)
End With
Next SName
'save the workbook with changes; close
ActiveWorkbook.Save
ActiveWindow.Close

'Ask whether user wants to update another file
Response = MsgBox("Update another file?", vbYesNo)
Loop Until Response = vbNo

End Sub

Thanks!
I must not be making myself very clear. :(
(Can I blame it on a head cold?)

Intralink (our data manaement program) appends a dot numeric value to
the name of the file if/when you view it through a workspace (Intralink
verbiage)

5098000.xls becomes 5098000.xls.209
(209 is a random number based on the number of objects in your
workspace)

Old naming conventions had the revision of the file in the filename
(5098000_116.xls) When people are printing out of Intralink, they are
seeing the dot numeric value and assuming it is the rev of the file.

So, I have exported the files to a new folder, unrelated to Intralink
These files are named correctly (i.e. 5098000.xls)

The headers for these files utilize &F
The tabs have unrelated references, which I can not change

I need to replace &F with 5098000.xls for this file. I have 442 others
which need the same thing, replace &F with the actual file name, in
text, in the header

There must be an easier way!!
Michelle


--
amthyst826
------------------------------------------------------------------------
amthyst826's Profile: http://www.excelforum.com/member.php...o&userid=28308
View this thread: http://www.excelforum.com/showthread...hreadid=478853

Let me summarize back at you, to make sure I have it.
~ Intralink names files in a way that makes your colleagues think they
are dealing with a particular revision of the file.
~ You have moved files away from Intralink's control.
~ You need to change the header in each file, essentially hardcoding
the filename instead of using the &F function.

Is that correct?

If it is, then the program I sent earlier will work for you, albeit it
one file at a time. Try it on some backup files, not actual files, so
you don't inadvertently lose data.

Sorry to hear about the head cold!

Wrong, wrong, sorry, I finally got it: the problem with a dynamic
filename in the header is that it will rename itself to the intralink
filename. Let me work on it for a bit.

Hey Dave!
Yep, you got it. (I just got to read your last post)
Scenario, over four hundred excel workbooks, right header is coded DOC.
NO. &F
I need to replace &F with the actual file name, as text

Example
5098000.xls
right header reads DOC. NO. &F
Need to change to read DOC. NO. 5098000.xls

If I can get something that will run on the contents of a specified
folder, that would be great!
I'll take whatever I can get though!!

Thanks again!!
Michelle ;)


--
amthyst826
------------------------------------------------------------------------
amthyst826's Profile: http://www.excelforum.com/member.php...o&userid=28308
View this thread: http://www.excelforum.com/showthread...hreadid=478853