I have a real estate database in Access 2003. The client wants to upload
photos to an FTP site then have a hyperlink field in the table to link to a
folder. I have created automated hyperlinks when a client saves an individual
file using a standatrd naming convention and common folder. This involves
sending multiple photos of each property to a folder on the FTP site. I'm not
too concerned about VBA for the hyperlink after the fact. What I don't know
is how to send the photos to the FTP site with a folder name as a variable.
Can I create a notepad file with VBA inserting the variable into the text
and then run the file as a batch file?

I have successfully used the free Chilkat FTP addin for copying files to an
FTP site:
http://www.chilkatsoft.com/ChilkatFtp.asp

It's pretty easy to install on your PC and to use in your ACCESS VBA code.
--

Ken Snell
<MS ACCESS MVP>


"Chip" <Chip@discussions.microsoft.com> wrote in message
news:42303127-27DA-4C8C-8054-5788B5D4272C@microsoft.com...
>I have a real estate database in Access 2003. The client wants to upload
> photos to an FTP site then have a hyperlink field in the table to link to
> a
> folder. I have created automated hyperlinks when a client saves an
> individual
> file using a standatrd naming convention and common folder. This involves
> sending multiple photos of each property to a folder on the FTP site. I'm
> not
> too concerned about VBA for the hyperlink after the fact. What I don't
> know
> is how to send the photos to the FTP site with a folder name as a
> variable.
> Can I create a notepad file with VBA inserting the variable into the text
> and then run the file as a batch file?

Thanks, Ken.
Should I install the Upload ActiveX version?

"Ken Snell (MVP)" wrote:

> I have successfully used the free Chilkat FTP addin for copying files to an
> FTP site:
> http://www.chilkatsoft.com/ChilkatFtp.asp
>
> It's pretty easy to install on your PC and to use in your ACCESS VBA code.
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
> "Chip" <Chip@discussions.microsoft.com> wrote in message
> news:42303127-27DA-4C8C-8054-5788B5D4272C@microsoft.com...
> >I have a real estate database in Access 2003. The client wants to upload
> > photos to an FTP site then have a hyperlink field in the table to link to
> > a
> > folder. I have created automated hyperlinks when a client saves an
> > individual
> > file using a standatrd naming convention and common folder. This involves
> > sending multiple photos of each property to a folder on the FTP site. I'm
> > not
> > too concerned about VBA for the hyperlink after the fact. What I don't
> > know
> > is how to send the photos to the FTP site with a folder name as a
> > variable.
> > Can I create a notepad file with VBA inserting the variable into the text
> > and then run the file as a batch file?
>
>
>

Yes, that is the one. After installing it, you'll need to add it to your
References in your ACCESS db.

--

Ken Snell
<MS ACCESS MVP>


"Chip" <Chip@discussions.microsoft.com> wrote in message
news:B79010E4-5BF3-4D2F-874B-8EA8FC3F12AF@microsoft.com...
> Thanks, Ken.
> Should I install the Upload ActiveX version?
>
> "Ken Snell (MVP)" wrote:
>
>> I have successfully used the free Chilkat FTP addin for copying files to
>> an
>> FTP site:
>> http://www.chilkatsoft.com/ChilkatFtp.asp
>>
>> It's pretty easy to install on your PC and to use in your ACCESS VBA
>> code.
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
>>
>> "Chip" <Chip@discussions.microsoft.com> wrote in message
>> news:42303127-27DA-4C8C-8054-5788B5D4272C@microsoft.com...
>> >I have a real estate database in Access 2003. The client wants to upload
>> > photos to an FTP site then have a hyperlink field in the table to link
>> > to
>> > a
>> > folder. I have created automated hyperlinks when a client saves an
>> > individual
>> > file using a standatrd naming convention and common folder. This
>> > involves
>> > sending multiple photos of each property to a folder on the FTP site.
>> > I'm
>> > not
>> > too concerned about VBA for the hyperlink after the fact. What I don't
>> > know
>> > is how to send the photos to the FTP site with a folder name as a
>> > variable.
>> > Can I create a notepad file with VBA inserting the variable into the
>> > text
>> > and then run the file as a batch file?
>>
>>
>>

Thanks, I'll give it a try.

"Ken Snell (MVP)" wrote:

> Yes, that is the one. After installing it, you'll need to add it to your
> References in your ACCESS db.
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
> "Chip" <Chip@discussions.microsoft.com> wrote in message
> news:B79010E4-5BF3-4D2F-874B-8EA8FC3F12AF@microsoft.com...
> > Thanks, Ken.
> > Should I install the Upload ActiveX version?
> >
> > "Ken Snell (MVP)" wrote:
> >
> >> I have successfully used the free Chilkat FTP addin for copying files to
> >> an
> >> FTP site:
> >> http://www.chilkatsoft.com/ChilkatFtp.asp
> >>
> >> It's pretty easy to install on your PC and to use in your ACCESS VBA
> >> code.
> >> --
> >>
> >> Ken Snell
> >> <MS ACCESS MVP>
> >>
> >>
> >> "Chip" <Chip@discussions.microsoft.com> wrote in message
> >> news:42303127-27DA-4C8C-8054-5788B5D4272C@microsoft.com...
> >> >I have a real estate database in Access 2003. The client wants to upload
> >> > photos to an FTP site then have a hyperlink field in the table to link
> >> > to
> >> > a
> >> > folder. I have created automated hyperlinks when a client saves an
> >> > individual
> >> > file using a standatrd naming convention and common folder. This
> >> > involves
> >> > sending multiple photos of each property to a folder on the FTP site.
> >> > I'm
> >> > not
> >> > too concerned about VBA for the hyperlink after the fact. What I don't
> >> > know
> >> > is how to send the photos to the FTP site with a folder name as a
> >> > variable.
> >> > Can I create a notepad file with VBA inserting the variable into the
> >> > text
> >> > and then run the file as a batch file?
> >>
> >>
> >>
>
>
>

Ken,
I am trying to upload photos to a folder using the following VBA.

Dim upload As New ChilkatUpload

upload.Hostname = "www.masstechnology.com"
upload.Login = "login"
upload.Password = "password"
upload.Path = "/c21photos"

' Add one or more files to be uploaded.
upload.AddFileReference "ChipTest", "C:\Documents and Settings\Walter\My
Documents\My Pictures\chip1.jpg"
Dim success As Long
success = upload.BlockingUpload()
If (success <> 1) Then
MsgBox upload.LastErrorText
Else
MsgBox "Files uploaded!"
End If


in this example I want to take the chip1.jpg file and send it to the folder
named c21photos
I get the success message but the file is not in the folder.
What am I missing or doing wrong?



"Chip" wrote:

> Thanks, I'll give it a try.
>
> "Ken Snell (MVP)" wrote:
>
> > Yes, that is the one. After installing it, you'll need to add it to your
> > References in your ACCESS db.
> >
> > --
> >
> > Ken Snell
> > <MS ACCESS MVP>
> >
> >
> > "Chip" <Chip@discussions.microsoft.com> wrote in message
> > news:B79010E4-5BF3-4D2F-874B-8EA8FC3F12AF@microsoft.com...
> > > Thanks, Ken.
> > > Should I install the Upload ActiveX version?
> > >
> > > "Ken Snell (MVP)" wrote:
> > >
> > >> I have successfully used the free Chilkat FTP addin for copying files to
> > >> an
> > >> FTP site:
> > >> http://www.chilkatsoft.com/ChilkatFtp.asp
> > >>
> > >> It's pretty easy to install on your PC and to use in your ACCESS VBA
> > >> code.
> > >> --
> > >>
> > >> Ken Snell
> > >> <MS ACCESS MVP>
> > >>
> > >>
> > >> "Chip" <Chip@discussions.microsoft.com> wrote in message
> > >> news:42303127-27DA-4C8C-8054-5788B5D4272C@microsoft.com...
> > >> >I have a real estate database in Access 2003. The client wants to upload
> > >> > photos to an FTP site then have a hyperlink field in the table to link
> > >> > to
> > >> > a
> > >> > folder. I have created automated hyperlinks when a client saves an
> > >> > individual
> > >> > file using a standatrd naming convention and common folder. This
> > >> > involves
> > >> > sending multiple photos of each property to a folder on the FTP site.
> > >> > I'm
> > >> > not
> > >> > too concerned about VBA for the hyperlink after the fact. What I don't
> > >> > know
> > >> > is how to send the photos to the FTP site with a folder name as a
> > >> > variable.
> > >> > Can I create a notepad file with VBA inserting the variable into the
> > >> > text
> > >> > and then run the file as a batch file?
> > >>
> > >>
> > >>
> >
> >
> >

Did you refresh the FTP site after you run the code? The file often won't
show until you refresh the "window" that is showing the FTP site's contents.

--

Ken Snell
<MS ACCESS MVP>


"Chip" <Chip@discussions.microsoft.com> wrote in message
news:CCA9D6B7-6C7A-43B3-A823-52B6874AC656@microsoft.com...
> Ken,
> I am trying to upload photos to a folder using the following VBA.
>
> Dim upload As New ChilkatUpload
>
> upload.Hostname = "www.masstechnology.com"
> upload.Login = "login"
> upload.Password = "password"
> upload.Path = "/c21photos"
>
> ' Add one or more files to be uploaded.
> upload.AddFileReference "ChipTest", "C:\Documents and Settings\Walter\My
> Documents\My Pictures\chip1.jpg"
> Dim success As Long
> success = upload.BlockingUpload()
> If (success <> 1) Then
> MsgBox upload.LastErrorText
> Else
> MsgBox "Files uploaded!"
> End If
>
>
> in this example I want to take the chip1.jpg file and send it to the
> folder
> named c21photos
> I get the success message but the file is not in the folder.
> What am I missing or doing wrong?
>
>
>
> "Chip" wrote:
>
>> Thanks, I'll give it a try.
>>
>> "Ken Snell (MVP)" wrote:
>>
>> > Yes, that is the one. After installing it, you'll need to add it to
>> > your
>> > References in your ACCESS db.
>> >
>> > --
>> >
>> > Ken Snell
>> > <MS ACCESS MVP>
>> >
>> >
>> > "Chip" <Chip@discussions.microsoft.com> wrote in message
>> > news:B79010E4-5BF3-4D2F-874B-8EA8FC3F12AF@microsoft.com...
>> > > Thanks, Ken.
>> > > Should I install the Upload ActiveX version?
>> > >
>> > > "Ken Snell (MVP)" wrote:
>> > >
>> > >> I have successfully used the free Chilkat FTP addin for copying
>> > >> files to
>> > >> an
>> > >> FTP site:
>> > >> http://www.chilkatsoft.com/ChilkatFtp.asp
>> > >>
>> > >> It's pretty easy to install on your PC and to use in your ACCESS VBA
>> > >> code.
>> > >> --
>> > >>
>> > >> Ken Snell
>> > >> <MS ACCESS MVP>
>> > >>
>> > >>
>> > >> "Chip" <Chip@discussions.microsoft.com> wrote in message
>> > >> news:42303127-27DA-4C8C-8054-5788B5D4272C@microsoft.com...
>> > >> >I have a real estate database in Access 2003. The client wants to
>> > >> >upload
>> > >> > photos to an FTP site then have a hyperlink field in the table to
>> > >> > link
>> > >> > to
>> > >> > a
>> > >> > folder. I have created automated hyperlinks when a client saves an
>> > >> > individual
>> > >> > file using a standatrd naming convention and common folder. This
>> > >> > involves
>> > >> > sending multiple photos of each property to a folder on the FTP
>> > >> > site.
>> > >> > I'm
>> > >> > not
>> > >> > too concerned about VBA for the hyperlink after the fact. What I
>> > >> > don't
>> > >> > know
>> > >> > is how to send the photos to the FTP site with a folder name as a
>> > >> > variable.
>> > >> > Can I create a notepad file with VBA inserting the variable into
>> > >> > the
>> > >> > text
>> > >> > and then run the file as a batch file?
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >

I've even gone out and back in. The folder is empty.
Does my code look OK? I'm not sure about the AddFileReference line. What is
the first argument representing? I asume the name I want to give the file as
added to the FTP site.

"Ken Snell (MVP)" wrote:

> Did you refresh the FTP site after you run the code? The file often won't
> show until you refresh the "window" that is showing the FTP site's contents.
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
> "Chip" <Chip@discussions.microsoft.com> wrote in message
> news:CCA9D6B7-6C7A-43B3-A823-52B6874AC656@microsoft.com...
> > Ken,
> > I am trying to upload photos to a folder using the following VBA.
> >
> > Dim upload As New ChilkatUpload
> >
> > upload.Hostname = "www.masstechnology.com"
> > upload.Login = "login"
> > upload.Password = "password"
> > upload.Path = "/c21photos"
> >
> > ' Add one or more files to be uploaded.
> > upload.AddFileReference "ChipTest", "C:\Documents and Settings\Walter\My
> > Documents\My Pictures\chip1.jpg"
> > Dim success As Long
> > success = upload.BlockingUpload()
> > If (success <> 1) Then
> > MsgBox upload.LastErrorText
> > Else
> > MsgBox "Files uploaded!"
> > End If
> >
> >
> > in this example I want to take the chip1.jpg file and send it to the
> > folder
> > named c21photos
> > I get the success message but the file is not in the folder.
> > What am I missing or doing wrong?
> >
> >
> >
> > "Chip" wrote:
> >
> >> Thanks, I'll give it a try.
> >>
> >> "Ken Snell (MVP)" wrote:
> >>
> >> > Yes, that is the one. After installing it, you'll need to add it to
> >> > your
> >> > References in your ACCESS db.
> >> >
> >> > --
> >> >
> >> > Ken Snell
> >> > <MS ACCESS MVP>
> >> >
> >> >
> >> > "Chip" <Chip@discussions.microsoft.com> wrote in message
> >> > news:B79010E4-5BF3-4D2F-874B-8EA8FC3F12AF@microsoft.com...
> >> > > Thanks, Ken.
> >> > > Should I install the Upload ActiveX version?
> >> > >
> >> > > "Ken Snell (MVP)" wrote:
> >> > >
> >> > >> I have successfully used the free Chilkat FTP addin for copying
> >> > >> files to
> >> > >> an
> >> > >> FTP site:
> >> > >> http://www.chilkatsoft.com/ChilkatFtp.asp
> >> > >>
> >> > >> It's pretty easy to install on your PC and to use in your ACCESS VBA
> >> > >> code.
> >> > >> --
> >> > >>
> >> > >> Ken Snell
> >> > >> <MS ACCESS MVP>
> >> > >>
> >> > >>
> >> > >> "Chip" <Chip@discussions.microsoft.com> wrote in message
> >> > >> news:42303127-27DA-4C8C-8054-5788B5D4272C@microsoft.com...
> >> > >> >I have a real estate database in Access 2003. The client wants to
> >> > >> >upload
> >> > >> > photos to an FTP site then have a hyperlink field in the table to
> >> > >> > link
> >> > >> > to
> >> > >> > a
> >> > >> > folder. I have created automated hyperlinks when a client saves an
> >> > >> > individual
> >> > >> > file using a standatrd naming convention and common folder. This
> >> > >> > involves
> >> > >> > sending multiple photos of each property to a folder on the FTP
> >> > >> > site.
> >> > >> > I'm
> >> > >> > not
> >> > >> > too concerned about VBA for the hyperlink after the fact. What I
> >> > >> > don't
> >> > >> > know
> >> > >> > is how to send the photos to the FTP site with a folder name as a
> >> > >> > variable.
> >> > >> > Can I create a notepad file with VBA inserting the variable into
> >> > >> > the
> >> > >> > text
> >> > >> > and then run the file as a batch file?
> >> > >>
> >> > >>
> >> > >>
> >> >
> >> >
> >> >
>
>
>

I'm not familiar with the BlockingUpload method that you're using for the
Chilkat FTP object.

Here is code that I wrote to do FTP uploading of a file using the Chilkat
object. The FTP_Copy function is called from another VBA procedure, and that
procedure passes along the first three arguments to the function. The
FTP_Copy function will create a directory/folder if the desired folder is
not present; for the application that I used this function in, there was a
master folder and a subfolder under that master folder. The file was put
into the subfolder. This function creates the master folder if it's not
there, and also creates the subfolder if it's not there.


Private Function FTP_Copy(strParentFTPFolder As String, _
strFTPServerFolderToUse As String, _
strFilePathBeingCopied As String, _
Optional ByVal strFileNameToUseOnFTP As String = "") As Boolean

Dim strFile As String, strFileFTP As String
Dim strFilePath As String
Dim intSuccess As Integer
Dim ftpObj As ChilkatFTP

On Error Resume Next

strFile = ExtractFileName(strFilePathBeingCopied)
strFilePath = ExtractPath(strFilePathBeingCopied)
If strFileNameToUseOnFTP = "" Then strFileNameToUseOnFTP = strFile

Set ftpObj = New ChilkatFTP
With ftpObj
' replace generic strings with real values in the following three lines
.Hostname = "NameOfTheFTPServer" ' server IP or FTP address
.UserName = "UserName"
.Password = "Password"

.Passive = 1

intSuccess = .Connect
If intSuccess = 0 Then
FTP_Copy = False
Else
intSuccess = .CreateRemoteDir(strParentFTPFolder)
intSuccess = .ChangeRemoteDir(strParentFTPFolder)
If intSuccess = 0 Then
FTP_Copy = False
Else
intSuccess = .CreateRemoteDir(strFTPServerFolderToUse)
intSuccess = .ChangeRemoteDir(strFTPServerFolderToUse)
If intSuccess = 0 Then
FTP_Copy = False
Else
intSuccess = .PutFile(strFilePath & strFile,
strFileNameToUseOnFTP)
If intSuccess = 0 Then
FTP_Copy = False
Else
FTP_Copy = True
End If
End If
End If
.Disconnect
End If
End With
Set ftpObj = Nothing

Err.Clear
Exit Function
End Function

Private Function ExtractFileName(ByVal strPathFile As String) As String
'*** THIS FUNCTION EXTRACTS THE "FILE NAME" PORTION OF A STRING THAT HOLDS
'*** THE FULL PATH AND FILENAME FOR A FILE. IT DOES THIS BY DROPPING
'*** THE PATH PORTION FROM THE STRING (ALL TEXT BEFORE THE LAST
'*** "\" CHARACTER IN THE STRING, AND THAT LAST "\" CHARACTER, TOO).

'*** IF THERE IS NO "\" CHARACTER IN THE TEXT STRING, THE FUNCTION RETURNS
'*** AN EMPTY STRING AS ITS VALUE. OTHERWISE, IT RETURNS THE "FILE NAME"
PORTION
'*** OF THE TEXT STRING.

' strPathFile is string variable that contains the full path and filename
text string.

On Error Resume Next

If InStr(strPathFile, "\") = 0 Then
ExtractFileName = ""
Else
ExtractFileName = Mid(strPathFile, InStrRev(strPathFile, "\") + 1)
End If
Err.Clear
Exit Function
End Function

Private Function ExtractPath(ByVal strPathFile As String) As String
'*** THIS FUNCTION EXTRACTS THE "PATH" PORTION OF A STRING THAT HOLDS
'*** THE FULL PATH AND FILENAME FOR A FILE. IT DOES THIS BY DROPPING
'*** THE FILENAME PORTION FROM THE STRING (ALL TEXT AFTER THE LAST
'*** "\" CHARACTER IN THE STRING).

'*** IF THERE IS NO "\" CHARACTER IN THE TEXT STRING, THE FUNCTION RETURNS
'*** AN EMPTY STRING AS ITS VALUE. OTHERWISE, IT RETURNS THE "PATH" PORTION
'*** (INCLUDING THE ENDING "\" CHARACTER) OF THE TEXT STRING.

' strPathFile is string variable that contains the full path and filename
text string.

On Error Resume Next

If InStr(strPathFile, "\") = 0 Then
ExtractPath = ""
Else
ExtractPath = Left(strPathFile, InStrRev(strPathFile, "\"))
End If
Err.Clear
Exit Function
End Function


--

Ken Snell
<MS ACCESS MVP>


"Chip" <Chip@discussions.microsoft.com> wrote in message
news:E31CB129-79E0-4F4E-BE27-7F7C508621BF@microsoft.com...
> I've even gone out and back in. The folder is empty.
> Does my code look OK? I'm not sure about the AddFileReference line. What
> is
> the first argument representing? I asume the name I want to give the file
> as
> added to the FTP site.
>
> "Ken Snell (MVP)" wrote:
>
>> Did you refresh the FTP site after you run the code? The file often won't
>> show until you refresh the "window" that is showing the FTP site's
>> contents.
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
>>
>> "Chip" <Chip@discussions.microsoft.com> wrote in message
>> news:CCA9D6B7-6C7A-43B3-A823-52B6874AC656@microsoft.com...
>> > Ken,
>> > I am trying to upload photos to a folder using the following VBA.
>> >
>> > Dim upload As New ChilkatUpload
>> >
>> > upload.Hostname = "www.masstechnology.com"
>> > upload.Login = "login"
>> > upload.Password = "password"
>> > upload.Path = "/c21photos"
>> >
>> > ' Add one or more files to be uploaded.
>> > upload.AddFileReference "ChipTest", "C:\Documents and
>> > Settings\Walter\My
>> > Documents\My Pictures\chip1.jpg"
>> > Dim success As Long
>> > success = upload.BlockingUpload()
>> > If (success <> 1) Then
>> > MsgBox upload.LastErrorText
>> > Else
>> > MsgBox "Files uploaded!"
>> > End If
>> >
>> >
>> > in this example I want to take the chip1.jpg file and send it to the
>> > folder
>> > named c21photos
>> > I get the success message but the file is not in the folder.
>> > What am I missing or doing wrong?
>> >
>> >
>> >
>> > "Chip" wrote:
>> >
>> >> Thanks, I'll give it a try.
>> >>
>> >> "Ken Snell (MVP)" wrote:
>> >>
>> >> > Yes, that is the one. After installing it, you'll need to add it to
>> >> > your
>> >> > References in your ACCESS db.
>> >> >
>> >> > --
>> >> >
>> >> > Ken Snell
>> >> > <MS ACCESS MVP>
>> >> >
>> >> >
>> >> > "Chip" <Chip@discussions.microsoft.com> wrote in message
>> >> > news:B79010E4-5BF3-4D2F-874B-8EA8FC3F12AF@microsoft.com...
>> >> > > Thanks, Ken.
>> >> > > Should I install the Upload ActiveX version?
>> >> > >
>> >> > > "Ken Snell (MVP)" wrote:
>> >> > >
>> >> > >> I have successfully used the free Chilkat FTP addin for copying
>> >> > >> files to
>> >> > >> an
>> >> > >> FTP site:
>> >> > >> http://www.chilkatsoft.com/ChilkatFtp.asp
>> >> > >>
>> >> > >> It's pretty easy to install on your PC and to use in your ACCESS
>> >> > >> VBA
>> >> > >> code.
>> >> > >> --
>> >> > >>
>> >> > >> Ken Snell
>> >> > >> <MS ACCESS MVP>
>> >> > >>
>> >> > >>
>> >> > >> "Chip" <Chip@discussions.microsoft.com> wrote in message
>> >> > >> news:42303127-27DA-4C8C-8054-5788B5D4272C@microsoft.com...
>> >> > >> >I have a real estate database in Access 2003. The client wants
>> >> > >> >to
>> >> > >> >upload
>> >> > >> > photos to an FTP site then have a hyperlink field in the table
>> >> > >> > to
>> >> > >> > link
>> >> > >> > to
>> >> > >> > a
>> >> > >> > folder. I have created automated hyperlinks when a client saves
>> >> > >> > an
>> >> > >> > individual
>> >> > >> > file using a standatrd naming convention and common folder.
>> >> > >> > This
>> >> > >> > involves
>> >> > >> > sending multiple photos of each property to a folder on the FTP
>> >> > >> > site.
>> >> > >> > I'm
>> >> > >> > not
>> >> > >> > too concerned about VBA for the hyperlink after the fact. What
>> >> > >> > I
>> >> > >> > don't
>> >> > >> > know
>> >> > >> > is how to send the photos to the FTP site with a folder name as
>> >> > >> > a
>> >> > >> > variable.
>> >> > >> > Can I create a notepad file with VBA inserting the variable
>> >> > >> > into
>> >> > >> > the
>> >> > >> > text
>> >> > >> > and then run the file as a batch file?
>> >> > >>
>> >> > >>
>> >> > >>
>> >> >
>> >> >
>> >> >
>>
>>
>>