VBA FTP
-
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()
'
' Macro2 Macro
''
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[^]
-
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()
'
' Macro2 Macro
''
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.
-
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",vbnormalfocusBatch file to look something like:filezilla ftp://username:password@server:portAs 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[^]
-
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",vbnormalfocusBatch file to look something like:filezilla ftp://username:password@server:portAs 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.
-
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",vbnormalfocusBatch file to look something like:filezilla ftp://username:password@server:portAs 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[^]
-
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