473,326 Members | 2,337 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Help saving files to disk from within Access using VBA

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
5 3210
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
"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
* 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
"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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

22
by: Bryan Rickard | last post by:
I wrote a simple program in VB6 to copy all the files from a directory on a CD-ROM to my hard disk. There are about 10 files, each about 30MB. The program uses Get and Put to get data from the...
138
by: theodp | last post by:
--> From http://www.techdirt.com/articles/20040406/1349225.shtml Microsoft Patents Saving The Name Of A Game Contributed by Mike on Tuesday, April 6th, 2004 @ 01:49PM from the...
6
by: Vijay | last post by:
I need to generate HTML files based on the some codes. One HTML file per code. I have the link (ex:http://123.234.345.456/WebPages/GetTestData.aspx?SomeCode=25), by passing the code as parameter I...
0
by: Richard | last post by:
Within a macro I have coded the line:- Activeworkbook.Save Unfortunately (and this is a regular occurrance which is very annoying), when saving a workbook ordinarily (using File - Save) I get...
4
by: Otis Hunter | last post by:
I have been given an Access Database which contains a table that has an OLE object field that contains a Word document. That table contains hundreds of records. I would like to find out how I can...
1
by: terrorix | last post by:
I want to save uploaded file to disk. I have this construction: HttpPostedFile myFile = ((HttpRequest)Request).Files; if (myFile != null) { string fn =...
1
by: Rahul | last post by:
Hi Everybody I have some problem in my script. please help me. This is script file. I have one *.inq file. I want run this script in XML files. But this script errors shows . If u want i am...
5
by: Sam | last post by:
Hi, I have one table like : MyTable {field1, field2, startdate, enddate} I want to have the count of field1 between startdate and enddate, and the count of field2 where field2 = 1 between...
16
by: Harry Simpson | last post by:
I've been away from ASPNET - I open up a new Web app in VS2008 and go into properties and select to use IIS instead of the personal web server. Then I run in debug mode and it says I have to set...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.