Create Personal.xls programmatically? 2

[ESquared]

I know how to get Personal.xls created by user intervention, simply by recording a new macro to the "Personal Macro Workbook." But if I want to spare the user this step, is there an easy programmatic way to do it, short of just mimicking its operation?

If I have to mimic its operation, I'll need to know the location of the user's XLSTART folder. I can assume that it is at {User Documents and Settings Path}\Application Data\Microsoft\Excel\XLSTART but I don't like to assume. Is there a way in Excel to simply get the path of the current XLSTART folder for the current user?

 

[kjv1611]

I would think you could use a method from the File System Object to find that information.

See this from the help file:

FileSystemObject Object


Description

Provides access to a computer's file system.

Syntax

Scripting.FileSystemObject

Remarks

The following code illustrates how the FileSystemObject is used to return a TextStream object that can be read from or written to:

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\testfile.txt", True)
a.WriteLine("This is a test.")
a.Close

In the code shown above, the CreateObject function returns the FileSystemObject (fs). The CreateTextFile method then creates the file as a TextStream object (a), and the WriteLine method writes a line of text to the created text file. The Close method flushes the buffer and closes the file.

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

What about simply Application.StartupPath ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kjv1611]

That is a totally awesome way to find the needed info! I knew there had to be a shorter way to find it than what I knew of!

--

"If to err is human, then I must be some kind of human!" -Me

 

[ESquared]

PHV, I knew there had to be a way. Thanks. I DID search on this before posting for a fair amount of time.

 

[ESquared]

kjv1611,

To check if a file exists it's easier to just do FileLen("c:\filename.txt") or GetAttr("c:\filename.txt") using On Error Resume Next and testing for an error condition.

 

[PHV]

To check if a file exists ...
For me the simplest way is the Dir function (no need of error handler at all).

 

[ESquared]

Dir works, too. But you can't have two Dirs going at once, so if you have one going you have to have another standard way to check for a file's existence...

 

[stevexff]

Isn't the path to this (partially) held in environment variable %APPDATA% - there must be a way to pick this value up from VBA?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[ESquared]

stevexff, PHV already said it's Application.StartupPath.

Besides, it's best to not trust environment variables if at all possible, because users can mess with them so easily.

 

[kjv1611]

Hmm - that's something I've not thought of about environment variables. Of course, I've not yet used them much, either.

--

"If to err is human, then I must be some kind of human!" -Me