|
I am attempting to write a macro so that within an Excel Worksheet I call it, and it saves the .xlsm, saves as a web page and then opens the ftp (in this case filezilla).
I am now stuck.
Where I want to go with this is that the ftpclient automatically opens on the correct website.
(We have several)
After that I am happy to select the files to upload and close.
I have embedded a hyperlink in the file at cell A1.
I suspect that I may need to somehow put the host/username/password into the macro somewhere and get it called on the call of the ftpclient.
Can anybody suggest where I am going wrong?
Sub Macro2()
ActiveWorkbook.SaveAs Filename:="C:\Users\David\Desktop\Book1.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = False
With ActiveWorkbook.PublishObjects.Add(xlSourceWorkbook, _
"C:\Users\David\Desktop\Book1.htm", , , xlHtmlStatic, "Book1_31795", "")
.Publish (True)
.AutoRepublish = True
Application.DisplayAlerts = True
End With
Application.Left = 161.5
Application.Top = 91.75
Range("a1").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End Sub
------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
CCC League Table Link
CCC Link[ ^]
|
|
|
|
|
Hoi,
this[^] suggests one can invoke FileZilla client with a specific target website in mind, so if your VBA powers allow you to execute an arbitrary "DOS command", I'd go for it.
Alternatively, you could use a different FTP client, e.g. the one provided by (some versions of?) Windows, see here[^].
I expect those FTP clients will still prompt you for username and password.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
OK, as far as I can tell I need to open a dos shell and call a batch file.
Is that right?
Call the shell as:
Shell ftp.bat",vbnormalfocus
Batch file to look something like:
filezilla ftp://username:password@server:port
As far as I can tell this will open the command shell, then run the batch file ftp.bat which will call FileZilla and apply the necessary data to expose the website.
Is this right?
------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
CCC League Table Link
CCC Link[ ^]
|
|
|
|
|
I don't know the details, as I avoid VB6 and VBA as much as possible. I don't think you need a batch file, although it could make sense to have it anyway.
I would read up on, and experiment with, things like this:
Call Shell("cmd /K FileZilla whatever_constant_arguments_you_want_to_provide" & or_variable_arguments, vbNormalFocus)
or maybe simply:
Call Shell("FileZilla whatever_constant_arguments_you_want_to_provide" & or_variable_arguments, vbNormalFocus)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
A word of caution:
Make sure filezilla.exe is in the PATH variable.
Or
You provide absolute path to the filezilla exe
Or
You place the bat file in the correct location with relation to how you have called the filezilla exe.
Shreekar
|
|
|
|
|
Thanks for the advice.
------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
CCC League Table Link
CCC Link[ ^]
|
|
|
|
|
So i have dll project. In its code, i have a bunch of functions i use very often in many of my forms applications. One of them is a Bug Reporter class. When called, a dialog appears with some controls. When the user has filled all the textboxes and made valid combobox selections, you click the Submit button which submits the information to my email address. But this only seems to work sometimes. I can't figure out why.
I've made a simple forms application for others to try and test the Bug Reporter. I've posted it on another website and it only seems to work for some of my testers, others get errors. Here are some screenshots:
http://entrod.sp-website.net/images/bug.jpg[^]
http://img638.imageshack.us/img638/5442/96651911.png[^]
So im baffled. What can i do to make this work everytime? I mean, what's the point of having a bug reporter if the bug reporter itself doesn't work properly? 
Here my email code, if it's needed:
Private Sub bgwSender_DoWork() Handles bgwSender.DoWork
mErrorOccured = False
Try
mm = New MailMessage
With mm
.From = New MailAddress(tbEmail.Text)
.To.Add("My email address goes here")
.Subject = Me.Text
.Body = _
tbProblem.Text & ControlChars.NewLine & ControlChars.NewLine & _
"Bug type: " & cbbBugType.SelectedItem & ControlChars.NewLine & _
"Windows version: " & cbbWindowsVersion.SelectedItem & ControlChars.NewLine
.IsBodyHtml = False
End With
smtp = New SmtpClient
smtp.Host = "My SMTP server goes here"
smtp.Send(mm)
Catch ex As Exception
MessageBox.Show(ex.Message)
mErrorOccured = True
mErrorMessage = ex.ToString
End Try
End Sub
Virtual Space Shuttle Astronaut
|
|
|
|
|
Hi,
if by "it works sometimes" you mean it works all the time on some machines, and never on some other machines, then IMO the most likely cause is something is blocking your app's access to the mail server. It could be an AntiVirus/Firewall, or a (corporate) proxy server. Those would need extra settings, I don't have the details handy.
I expect you will never get it solved 100%, so what I would recommend is a two-way approach:
1.
try and solve it as much as possible. Figure out what needs to be done on Firewalls and proxy servers and document it.
2.
provide an alternate approach, i.e. when sending the mail fails, show a dialog telling the user your app created a file that he should mail to you.
FYI: each particular problem can be tackled by pasting the error message into the Google search box; whatever the error is, you're not the first to get it!
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Yeah it always works on some machines and never on others.
Usefull tips. I'll see what i can do.
But the email code itself, is that alright? Im not missing anything? Should i enable ssl? What about credentials/default credentials?
Virtual Space Shuttle Astronaut
|
|
|
|
|
I'm not an e-mail expert, so far I get specific problems solved, but I don't have the universal answer (unless it is 42). Your code looks fine to me, however I know there are many things that may interfere, credentials would be one of them. e.g. it may work better (or take less code) when the e-mail client happens to be running.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Maybe adding some credentials would help, it's a common problem.
smtp.Credentials = new System.Net.NetworkCredential(credentialUser, credentialPassword);
Cheers
sorry it's C#, translate to VB
If you can read this, you don't have Papyrus installed
|
|
|
|
|
It's the same isn't it?
------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
CCC League Table Link
CCC Link[ ^]
|
|
|
|
|
The semicolon will cause errors
If you can read this, you don't have Papyrus installed
|
|
|
|
|
oooh, how did I miss that! :shame:
------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
CCC League Table Link
CCC Link[ ^]
|
|
|
|
|
CodeProject even has a smiley for you in your current situation: : - O yielding 
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
You didn't miss that, it was VB's mistake not to understand semicolons.
|
|
|
|
|
This code will only work if the computer where it is running can use the port 25. If this port is closed, then it will not work.
|
|
|
|
|
Thanks people.
One more question: should i use my username and password for credentials or should that be the username and password of the sender? That part confuses me.
Virtual Space Shuttle Astronaut
|
|
|
|
|
the sender's since he is logging into his mail provider, not yours. And he'd normally provide a separate username/password for that particular purpose, as they aren't going to be replaced regularly and really should be independent of his normal, interactive ones. Which is similar to how an app should access a database really.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
normally the user-name and password for the email account that you will send through
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
Won't DefaultCrendentials be enough then? If i understand correctly, DefaultCrendentials get the username and password of the sender's email client, right? Then there's not need for them to enter it?
Virtual Space Shuttle Astronaut
|
|
|
|
|
I don't know anything about the language any more, but I despise Outlook with a passion, and Thunderbird is a long forgotten memory, so I have no "default credentials" that I know of on file... Could that not be the promble?
|
|
|
|
|
Further to _Erik_'s message, and by way of an FYI, a lot of ISPs in the UK block access to port 25 other than to their own smtp servers. e.g. I am on O2 broadband and I can NOT run my own smtp server; the only servers I can access via port 25 belong to O2. As you can imagine, this is a measure to stop spam.
|
|
|
|
|
mail is an ugly thing and should be avoided at all costs 
First of: it is highly unreliable. It's not beause it was transferred to the mail server that it will actually ever arive at it's final destination.
Secondly, as pointed out by others; the odds that the client machine can actually connect to your mailserver are against you and diminishing every year due to it being such a popular medium to harass people with commercials
- the client must be connected to the internet
- it must be allowed to access your mail server, which on a lan it usually isn't.
- Recently a lot of anti-virus software and personal firewalls started blocking
programs from sending out mail due to the high number of spambot malware.
A better sollution would be to store your bug report locally first and then try to send it to a webserver via http.
This method is used by all the big players and preferred over smtp (mail), because :
- you have proof of delivery at the client side (so you can keep it stored as long as it was not delivered correctly)
- http is more likely to be allowed (outgoing) from a corporate lan. If they can surf the internet,
they can send you a bugreport
- if antivirus would start blocking programs from using http calls a lot of things wouldn't work any more so chances of
that happening in the future are slim. This increases the reliability othis sollution
- you have standard sollutions at your disposal to handle this (web services, ajax, etc...)
|
|
|
|
|
Your code looks fine. My suggestion is, try to assign Body content in a string and assign to .Body. I had similar problem years ago (in CDONT) and it resolved when I changed this way. Since then I am always careful. Hope this helps.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
