Help saving files to disk from within Access using VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Colleyville Alan - 30 Apr 2006 06:47 GMT

I have an Access app which uses Office Automation to manipulate and then
save PowerPoint files to disk. It works fine, but I need to update the code
and I am having problems with the syntax. Currently, it saves all of the
files to one main subdirectory. Now I need it to save the files to separate
directories based on the value of a new field in the database.

The new field is the name of a person assigned to an account. I have no
control over how this value is input and its format is rather inelegant,
sometimes it is just first and last name and other times it contains a
nickname. So it might be in the form of "John Doe" or it might be "William
(Bill) Doe". Anyhow, I need to strip out the last name in this field so I
can append it to the path variable.

So, my thought was to grab the value of that field, then run a for-next loop
starting with the last char and going backwards until a space is found.
Then I would use that string as the name of the directory and, if it does
not already exist, create it on-the-fly . But I am having trouble with the
syntax as it has been more than a year since I did any programming.

Does anybody know the syntax for what I am trying to accomplish?

Thanks
Alan

Reply to this Message

Lyle Fairfield - 30 Apr 2006 12:59 GMT

If you are using a modern version of Access you could try

StrReverse(Split(StrReverse("Alan Colleyville"), " ")(0))

which returns Colleyville.

Reply to this Message

Colleyville Alan - 30 Apr 2006 17:41 GMT

> If you are using a modern version of Access you could try
>
> StrReverse(Split(StrReverse("Alan Colleyville"), " ")(0))
>
> which returns Colleyville.

Thanks - that code works great for splitting out the Name. But I am having
intermittent problems creating the subdirectory.

Here is a snippet that I found on the Interenet:

Dim fso
Dim fol As String
fol = "c:\MyFolder" ' change to match the folder path
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(fol) Then
fso.CreateFolder (fol)
End If

The line "If Not fso.FolderExists(fol) Then" works with no problem. If I
put a msgbox after this line, I get a message.

But when I use the line "fso.CreateFolder (fol)", I got an error message
the first few times I tried it that the function or command was not found.
Once I tried putting in the Msgbox in place of the CreateFolder command to
test it and after I saw that it worked, I commented that out and then
removed the comment from the CreateFolder command. Now it seems to be
working. Any idea what could cause that anomaly?

Reply to this Message

Randy Harris - 30 Apr 2006 17:53 GMT

* Colleyville Alan:

>> If you are using a modern version of Access you could try
>>
[quoted text clipped - 24 lines]
> removed the comment from the CreateFolder command. Now it seems to be
> working. Any idea what could cause that anomaly?

Have you tried mkdir?

Signature

Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

Reply to this Message

Lyle Fairfield - 30 Apr 2006 19:28 GMT

>> If you are using a modern version of Access you could try
>>
[quoted text clipped - 4 lines]
> Thanks - that code works great for splitting out the Name. But I am
> having intermittent problems creating the subdirectory.

Sub SaveTheFile(ByVal SomeName As String)
Dim FolderName As String
Dim FolderPath As String
FolderName = StrReverse(Split(StrReverse(SomeName), " ")(0))
FolderPath = "c:\" & FolderName
If Len(Dir$(FolderPath, vbDirectory)) = 0 Then MkDir FolderPath
' more save the file stuff here
End Sub

Sub temp2()
SaveTheFile "Allan Colleyville"
End Sub

Signature

Lyle Fairfield

Reply to this Message

Colleyville Alan - 30 Apr 2006 22:21 GMT

>>> If you are using a modern version of Access you could try
>>>
[quoted text clipped - 17 lines]
> SaveTheFile "Allan Colleyville"
> End Sub

Thanks

Reply to this Message