Can this be done in Excel?

  Chris the Ancien 15:47 26 Nov 05
Locked

Background...

I have a (successfully running) spreadsheet diary with all sorts of odds and ends of macros in it. One of the macros 'copies' just the content of the diary to a single sheet spreadsheet (without any macros or any of the other bits and pieces that the 'original user' has) for subsequent transmission to a 'remote' person. The aim is that the originator can just copy his/her diary to someone else. I call this copy of the diary (very originally) Diary for Transmission.xls

This method has been in use for a few years with no hassle.

BTW The 'original master' diary has a space for the user to enter his/her name in a cell (D3).

The task...

Because there is now the possibility that more than one 'originator' may need to send a 'Diary for Transmission.xls' to the same remote location, there will obviously be a problem at the remote end in trying to load more than one 'Diary for Transmission.xls' on to their desktop (or wherever).

What I would like to do is make a macro that automatically changes the name of the transmitted diary to include that users' name (in cell D3) and make a meaningful file name out of it such as 'Diary from Fred Bloggs.xls'.

Can this be done?

CtA

  Chris the Ancien 16:30 26 Nov 05

I've cracked it!

[code]

Sub Macro1()

Dim name As String


Range("D3").Select
name = ActiveCell

ChDir "C:\Documents and Settings\User\Desktop"
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\User\Desktop\" & name & ".xls"

End Sub

[/code]

  Number 7 16:33 26 Nov 05

As far as I know you'll have to save the file under the required name before sending it anywhere, so how about:


Option Explicit

Sub SaveThis()

Dim UserName As String

UserName = Cells(1, 1).Value

ThisWorkbook.SaveAs ("Diary From " & UserName)

End Sub


The resulting name will be Diary From UserName.xls.

  VoG II 16:42 26 Nov 05

name = Range("D3").Value

would be slightly more efficient ;o)

  Chris the Ancien 19:29 26 Nov 05

Did it VoG™'s way...

nearly...

but didn't even use the .Value qualifier!

I ended up making a 'model' that did all sorts of useful things with additional benefits of using the 'path' facility so the master and slave - sorry, owner's and transmission - copies could be anywhere on the computer - as long as they stay in the same folder.

All in all, I'm quite pleased with my afternoon's work! And I was so busy 'fiddling, I didn't notice the additions to the thread!

And, if anyone wants a peep at the full thing, yellow envelope me.

CtA

  VoG II 19:36 26 Nov 05

Sometimes you can get away with omitting .Value and sometimes you can't. It isn't easy to predict. I always use it because although it requires an additional 6 keystrokes and makes your file 6 bytes larger, I think that when the code executes the interpreter must have to decide what to do, increasing the execution time. That is purely supposition on my part.

  Chris the Ancien 20:16 26 Nov 05

An interesting philosophy!

I developed the bad habit of omitting .Value when I first started playing in Excel 5, I think it was, any way it was when the first attempts at vba were being used. (I hated Excel 4 macro writing!).

And, to be as certain as I can, I don't think that I had a problem.

But, I shall learn from the Master in case it does decrease execution time.

This thread is now locked and can not be replied to.

Amazon Fire HD 8 review: A brilliant combination of function and value – with one massive caveat

1995-2015: How technology has changed the world in 20 years

How to create an introvert-friendly workplace

Apple Watch 2 review | Apple Watch Series 2 review: New Apple Watch is faster, brighter, water-resit…