Saving an Excel file as a cell value?

[misterg]

Good morning all,

Just a brief question...is it possible to save a file as the value of a cell?

That is to say, i have a combo box returning text values (names) into a cell.

Can i use VBA coding to save the file as this cell value?

For example, if the combo box returns the name John Smith (and places this value in a cell - incidentally, can i get this placed into a text box? ), can the file then be saved as C:John Smith.xls?

If so...how!

p.s. you will probably notice i slipped in a quick question about populating a text box with the returned value from the combo box...any suggestions on how to do this also would be gratefully recd!

Keeping you busy?

Many thanks

G

[dk]

Morning,

If you want to save the active workbook as the value of the cell in A1 you can just use:-

ActiveWorkbook.SaveAs "C:folder name" & Range("A1").Value

As for the textbox question - is it on a userform or on a worksheet?

Regards,
Dan

[Ian Mac]

On 2002-05-21 01:47, misterg wrote:
Good morning all,

Just a brief question...is it possible to save a file as the value of a cell?

That is to say, i have a combo box returning text values (names) into a cell.

Can i use VBA coding to save the file as this cell value?

For example, if the combo box returns the name John Smith (and places this value in a cell - incidentally, can i get this placed into a text box? ), can the file then be saved as C:John Smith.xls?

If so...how!

p.s. you will probably notice i slipped in a quick question about populating a text box with the returned value from the combo box...any suggestions on how to do this also would be gratefully recd!

Keeping you busy?

Many thanks

G

For the first question use:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.SaveAs ("c:" & Range("a1").Value & ".xls")

End Sub

Change A1 to the cell that houses your value.

For the second question, what is the location of the Combobox and Textbox?

[misterg]

The text box and combo box would be on the same worksheet.

Do you thin they would work better on a userform?

G

[Ian Mac]

On 2002-05-21 02:50, misterg wrote:
The text box and combo box would be on the same worksheet.

Do you thin they would work better on a userform?

G

No I'd prefer on the sheet (Not being a very confident VBA user)

is:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

TextBox1.Value = Range("A1").Value & ".xls"

End Sub

what you need?

[misterg]

Mac,

The world is now a much brighter place.

Many thanks for your help...already the grey hairs are diminishing, the bald patch is growing back.

THANKS!!!!

G

[Ian Mac]

On 2002-05-21 03:05, misterg wrote:
Mac,

The world is now a much brighter place.

Many thanks for your help...already the grey hairs are diminishing, the bald patch is growing back.

THANKS!!!!

G

That's very nice to hear

[misterg]

Actually, just one more point on this point.

The code works fine if i want to use it at the end of the filename, that is to say if i call the file C:Blah blah & Range("A1").value...

But...how do i do this:

I need to set up individual folders on a shared drive for the respective names in the combo list.

How would i direct a file to be saved in each persons file and differentiate the file names purely by a title and date code?

For example...2 folder names on the shared drive will be John Smith and Peter Smith.

If john Smith is selected from the list I need the file to be saved as S:Sharedjohn SmithPersonal comments (and then date code taken from a cell reference).

So basically that means returning the name selected in the combo box AFTER the path s:Shared...but beforePersonal comments (date).

Does that sound confusing?

Its the same principle as before but instead of putting the name cell reference at the end of the file path, it goes in the middle.

God, im tired now...
G

[Ian Mac]

On 2002-05-21 04:20, misterg wrote:
Actually, just one more point on this point.

The code works fine if i want to use it at the end of the filename, that is to say if i call the file C:Blah blah & Range("A1").value...

But...how do i do this:

I need to set up individual folders on a shared drive for the respective names in the combo list.

How would i direct a file to be saved in each persons file and differentiate the file names purely by a title and date code?

For example...2 folder names on the shared drive will be John Smith and Peter Smith.

If john Smith is selected from the list I need the file to be saved as S:Sharedjohn SmithPersonal comments (and then date code taken from a cell reference).

So basically that means returning the name selected in the combo box AFTER the path s:Shared...but beforePersonal comments (date).

Does that sound confusing?

Its the same principle as before but instead of putting the name cell reference at the end of the file path, it goes in the middle.

God, im tired now...
G

you've pretty much answered your own question.

"S:Shared" & sheet1.range("B1").value & "Personal comments" etc

where B1 house the name you want.

[misterg]

Mac,

That works really well, thank you very much.

However, you know me by now...one question is never enough, i always push for more...

Is there anyway to get the date put in at the end of the file name (calling this from a =now()) cell?

I run into problems when i put this cell ref at the end of "Personal comments".

Any cluse how i get around this (the problem is caused by knowing whether to put it before or after the .xls

Can this be worked around?

G