By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Help saving files to disk from within Access using VBA

P: n/a
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

Apr 30 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
If you are using a modern version of Access you could try

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

which returns Colleyville.

Apr 30 '06 #2

P: n/a
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...
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?
Apr 30 '06 #3

P: n/a
* Colleyville Alan:
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...
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?


Have you tried mkdir?

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
Apr 30 '06 #4

P: n/a
"Colleyville Alan" <no****@nospam.net> wrote in
news:IL********************@comcast.com:
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...
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.


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

--
Lyle Fairfield
Apr 30 '06 #5

P: n/a
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:Xn*********************************@216.221.8 1.119...
"Colleyville Alan" <no****@nospam.net> wrote in
news:IL********************@comcast.com:
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...
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.


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

--
Lyle Fairfield

Thanks
Apr 30 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.