Problem with FTP commands in Win7+Excel2010 (VBA)

[Lars Thomsen]

In Excel2010 and Win7 I get error in my macro when I try to upload. The same
Excel file works fine on PC with Excel2010 and WinXP. The problem comes when
I type the following command:

Set FTP = New Inet

Then I get the error "Run-time error '429 'ActiveX component can not create
object ".

I have both machines set reference "Microsoft Internet Transfer Control 6.0
"in VBE editor

There should be a work-around described by MS
(http://support.microsoft.com/kb/281848), but when trying to create the
reference to "comdlg32.ocx". Through the Toolbox on a UserForm I right click
on the "toolbox" and adds "MS Common Dialog Controls ", but when I want the
new control in UserForm I get this error, which I also can not solve:

"The Controller Could not be created because it is not properly licensed"

I have tried the following script:

regsvr32% Systemroot% \ System32 \ comdlg32.ocx

I am told that it was registered, but I still get the license error :-(

Can anyone help me or do you alternatively, know other code that can be used
for uploading files to the server via VBA?

Best regards

Lars

 

[GS]

Lars Thomsen laid this down on his screen :

In Excel2010 and Win7 I get error in my macro when I try to upload. The same
Excel file works fine on PC with Excel2010 and WinXP. The problem comes when
I type the following command:

Set FTP = New Inet

Then I get the error "Run-time error '429 'ActiveX component can not create
object ".

I have both machines set reference "Microsoft Internet Transfer Control 6.0
"in VBE editor

I don't see this listed on my XP SP3 machine. I use this machine for
development as it has MS Office 9 through 12 installed, plus Visual
Studio 6.0 and Visual Studio 2008. How did this come to be on your
machine?

There should be a work-around described by MS
(http://support.microsoft.com/kb/281848), but when trying to create the
reference to "comdlg32.ocx". Through the Toolbox on a UserForm I right click
on the "toolbox" and adds "MS Common Dialog Controls ", but when I want the
new control in UserForm I get this error, which I also can not solve:

"The Controller Could not be created because it is not properly licensed"

I have tried the following script:

regsvr32% Systemroot% \ System32 \ comdlg32.ocx

I am told that it was registered, but I still get the license error :-(

Can anyone help me or do you alternatively, know other code that can be used
for uploading files to the server via VBA?

Best regards

Lars

Now, since you read the part about the design time license.., what that
means is if you do not have any of the listed products installed that
give you that license then you can't use the comdlg32.ocx control. If
it happens to be installed on the Win7 machine and properly registered,
you can reference it in the VBE but you can't use it without the design
time license that ships with those listed products. Note, however, that
there is a MS support statement issued
(http://msdn.microsoft.com/en-us/vstudio/ms788708) that says many of
the controls that were shipped with those listed products would NOT be
included in Vista/Win7/Win8. (However, contrary to the support
statement, I see in my Win7 machine that the controls are there in both
System32 and SysWOW64)

Fortunately, I happen to have all of the listed products and so I have
developer licenses up the wazu! The only way you can get use of this
ocx is to buy and install one of the listed products. Fortunately
they're available cheap on ebay for early versions. I paid through the
nose ($1400) for Microsoft Office 2000 Developer Edition but I was able
to buy Visual Studio 6.0 and Visual Studio 2008 really cheap (each
under $200) on ebay.

HTH

 

[Lars Thomsen]

Now, since you read the part about the design time license...

Thank you for your answer Garry. I can however not say that I was pleased
reading it. Actually I have VS2008 (remembering paying much more than
$200...), but I haven't installed it yet on my PC. It might help me on my
PC, but I'm used to distributing the Excel spreadsheet, and that won't work
if the receiver has Win7+Excel2010 :-(

So, I'm glad for your explanation, but I was however hoping for a workaround
that would work in VBA.

I have heard that I could use DOS prompt to upload files, but haven't quite
figured that out yet. That could maybe work through VBA with the
Shell-command. Does anybody have experience with that?

Can somebody otherwise tell me of another way of uploading files. Maybe a
FTP-program (hopefully freeware since I need several users to install it),
that accepts commandlines, which then again can be controlled by VBA.

Lars

 

[GS]

Lars Thomsen was thinking very hard :

Thank you for your answer Garry. I can however not say that I was pleased
reading it. Actually I have VS2008 (remembering paying much more than
$200...), but I haven't installed it yet on my PC. It might help me on my PC,
but I'm used to distributing the Excel spreadsheet, and that won't work if
the receiver has Win7+Excel2010 :-(

So, I'm glad for your explanation, but I was however hoping for a workaround
that would work in VBA.

I have heard that I could use DOS prompt to upload files, but haven't quite
figured that out yet. That could maybe work through VBA with the
Shell-command. Does anybody have experience with that?

Can somebody otherwise tell me of another way of uploading files. Maybe a
FTP-program (hopefully freeware since I need several users to install it),
that accepts commandlines, which then again can be controlled by VBA.

Lars

If you install VS2008 then you'll be able to distribute whatever
components your project needs. Just be sure your installer (or project)
properly registers the distributed components on the target machine.

You might find a pure VB solution in Classic VB forums, but even that
will require a ref to an inet interface of some sort.

Good luck!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Lars Thomsen]

After Lars Thomsen has been thinking very hard about what Garrys wrote he

cam up with this:

Hi Garry and other readers (if any...)

I gave up with using the build in Inet etc. in Excel. Instead I have made my
own FTP-client working with VBA through the Shell-command.

In VBA I write the following command in a sub:

Shell "ftp -v -i -s:c:\TempFolder\TempFtpFilePut.txt"
Shell "ftp -v -i -s:c:\TempFolder\TempFtpFileGet.txt"

Before using the shell command I have used VBA to create the txt-files which
looks like this:

*** Send files to server - TempFtpFilePut.txt ***
open hostname
Username
Password
cd subdir
cd subsubdir
put "c:\Temp\file1.ext"
put "c:\Temp\file2.ext"
put "c:\Temp\file3.ext"
Quit


*** Recieve files from server - TempFtpFileGet.txt***
open hostname
Username
Password
cd subdir
cd subsubdir
get file1.ext
get file2.ext
get file3.ext
Quit

I know that I can use mput and mget, but it is just to show the example. It
works for me and solves my problem.

You can read more about the possible commands here:

http://www.nsftools.com/tips/MSFTP.htm
http://www.dostips.com/DtTipsFtpBatchScript.php

 

[GS]

Lars,
What makes this possible is that your FTP client supports command lind
args. This is doable with any app that supports command line args.

Note also that the same can be done using ShellExecute().

Glad you found a solution. Big thanks for the feedback...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion