Create filename based on cell contents

[JR Hester]

Interesting opportunity, running Excel 2000 on Windows 2000 and Excel XP on
Windows XP.

Need to update a number of cells in approximately 50 workbooks with 4 to 8
worksheets in each! Each workbook contains records for 1 calendar week. Need
to pull last weeks ending numbers into this weeks beginning number. The
formula I used would require a lot of additioanl data input into each
workbook, so I think I am looking for a custom function. CAn anyone suggest
a way to accomplish the following

particulars:
Beginning in row 4
Column "A" contains serial numbers of equipment
Column "C" holds last week's ending reading
Column "D" holds this weeks ending reading

cell C3 contains last week's date and D3 contains this week's date

Want to generate a filename using the day, month, and year from cell C3. the
filename is in the format sampleDDmonYYYY.xls where mon is teh 3 letter name
of teh month, for example "sample07Jan2007.xls"

I plan to use the results of this routine in a Vlookup function to
import(link) the ending reading for each serial number in the previous week's
worksheet into the current worksheet.

I am a VBA newbie, so any suggetsion swill be greatly appreciated.

Thanks in advance

 

[Stefi]

filename = "sample" & Format(Range("A1"), "ddmmmyyyy") & ".xls"

Regards,
Stefi

„JR Hester†ezt írta:

 

[Stefi]

Sorry, with C3
x = "sample" & Format(Range("C3"), "ddmmmyyyy") & ".xls"

Stefi

„JR Hester†ezt írta:

 

[JR Hester]

I received a #Name error when entering that formula. My version of Excel has
no "Format" function to call. Is that part of an addin function set? If yes,
which one?

 

[Peo Sjoblom]

It's not a formula, it is VBA code. You can't name any files using formulas

http://www.mvps.org/dmcritchie/excel/install.htm

 

[JR Hester]

Thanks for clarifying that for me. Guess I will have to find some other way
to solve this, because I don't understand how to properly set this up in VBA.

Thanks again for the clarification

 

[JR Hester]

After some additional research, reading, and examples I managed to get the
following code to work in one spreadsheet:
Function LWT(lwd)
LWT="[sample"&Format(lwd, "ddmmmyyyy")&"more test here"
End Function

Here is where my inexperience with VBA really shows up. This works great in
the worksheet where I first tried it. I then copied and pasted it into the
VBA code of the spreadsheet where I need it to perform; alas it does not
work. Thinking it might be in the copy&paste methodology, I deleted it from
the destination and manually typed it in. Both methods return the #Name error.

What should I look for in the worksheet/workbook to correct this?

Thanks again!

 

[JR Hester]

Found my error!

I had failed to switch to MODULE mode, when I entered the code into the VBA
window. Hopefully I won't make that mistake again!

Thanks to all who've helped me in this endeavor.

After some additional research, reading, and examples I managed to get the
following code to work in one spreadsheet:
Function LWT(lwd)
LWT="[sample"&Format(lwd, "ddmmmyyyy")&"more test here"
End Function

Here is where my inexperience with VBA really shows up. This works great in
the worksheet where I first tried it. I then copied and pasted it into the
VBA code of the spreadsheet where I need it to perform; alas it does not
work. Thinking it might be in the copy&paste methodology, I deleted it from
the destination and manually typed it in. Both methods return the #Name error.

What should I look for in the worksheet/workbook to correct this?

Thanks again!

Sorry, with C3
x = "sample" & Format(Range("C3"), "ddmmmyyyy") & ".xls"

Stefi

„JR Hester†ezt írta: