How to automate File Uploads using excel macros
ฝัง
- เผยแพร่เมื่อ 24 ก.ย. 2024
- In this tutorial we would learn:
1. How to automate File uploads using excel macro.
2. Calling vb script from excel macro
For pre-requisites to this tutorial please refer below tutorial:
• How to Automate Web Sc...
Hello Sir, For the past 7 days, I was going crazy to find a way to automate the file upload process. I was stuck passing the file name in the file input box. Your tutorial is clean and helped me a lot. Thanks a lot.
Glad to hear that
This was amazing, im a civil engineer and im learning vba myself from past few years, this was a new trick that i learned, thanks.
Glad you liked it
Very underrated video. I have been trying to find this solution for far too long. Thank you! I am able to modify the code for my use and it works perfectly!
Glad it helped!
OMG!! Thank you so much!!!
I wasted too much time to search this solution!
No problem!
Thanks a tonne my friend ! Wonder why google doesnt throw up your video first when you search for how to upload a file in a VBA script - especially - when doing WebForms. Brilliant video Sir !
Glad it helped!
Very Helpful!! I find my cursor is not always in the same place in the select file window (probably a new feature since video posted). A suggestion is to replace the "{tab}" section of the shell script with:
WshShell.SendKeys "%{n}"
WScript.Sleep 2000
WshShell.SendKeys "^{v}"
WScript.Sleep 2000
WshShell.SendKeys "%{o}"
This is alt-n, ctrl-v, alt-o -- alt-n takes you directly to the filename input field, ctrl-v pastes, alt-o activates the 'Open' btn.
Your welcome
Sin mentir, pase mas de 12 horas buscando alternativas para poder realizar esto, muchas gracias por este truco super genial.
Saludos.
thank you
Love you buddy! Thanks for sharing your amazing inputs!! Cheers to you!
My pleasure!
Amazing ...much benefited from these tutorials. Thank you
Happy to hear that!
Thanks a lot ! It's very helpful 😁
You're welcome!
Amazing, neat and clear explanation even I am new to excel macro able to understand the concept clearly. Started with edge browser and performed few operation(sendkeys, click, drop downs, check box) r but i am unable to automate file upload.
Kindly provide steps to automate File Uploads in edge browser which helps me a lot.
Thanks in advance.
Code written in vbs file must be written in the vba macro itself for edge. No need to use separate vbs file for file uploads on edge and chrome browsers.
@@AutomationMadeEasy Thanks a lot . kindly provide the code to automate file upload in edge browser which helps me a lot.
For you i got the appreciation. You are magical
thank you
Hi,can u upload some videos on VBA with Desktop application through API. It will be really great.
Hello sir,
nice video... I have tried to upload images in my wordress website but I am getting run time error. can you please make a video how can upload image in WordPress with macro ?
thank you
Really awesome, thanks for sharing.
Thanks for watching!
Excellent Work !! Congratulations, Where can i have to source code ? Thanks !
Hi, thanks for this wonderful video - really cool, just if u could also help with the loop - actually i want to upload 50files so looking for loop code to copy the path from excel file and do the upload and come back again to copy next one till last, any help will be appreciated
Hi thanks for the codes, seems good.
I’m trying the same example file of yours but once the upload window opens “windows script host settings” nothing happens later.
Then excel is waiting for “ to complete an ole action
Great Video, and how can I upload with VBA on google Drive ?
Hi, I can go into the website using macro but I keep getting prompt with there is no script engine for the extension ".xlsm". The file I want to upload is an Excel File btw. Do you know what's causing this?
thanks sir for this video. i requesting you to please prepare video for download file with dynamic path.
welcome
Nice one. Iam looking for something similar. Have you uploaded video for multiple files upload for multiple webpages?
Basically I have 50files and each file goes to each tab on a same website.
Hi Bro, i am your subscriber and loved all your video's. is this possible to upload multiple file by using the same macro? Please advise.
Have u been able to figure this out. I am facing similar challenge
Thank you so much, this was very helpful! But I have run into an issue. When I run this, sometimes the script pastes the correct file path into the file name box, but sometimes the script just types in "v" into the file name box, so it seems the script is missing the ^ (Ctrl) sometimes when it runs. Do you know why this would happen?
Try adding some more delay befor ctrl c and ctrl v
Very nice
Thanks
I have an error' out of range because WsScript.arguments(0), how to solve it
how do we automate in case if there are 100s of files created everyday? and we have submit 1 file only once and we have to keep on uploading newer files to the upload link. i am asking this for a manufacturing company quality lab.
Thanks
filenames with folder locations must be provided in rows of excel sheet. Using a loop the files will be read and uploaded one by one
Hi, I'm getting an "Invalid procedure call or argument" on the Shell line of code. Any idea what could be causing this??
try selecting microsoft shell reference from Tools/Reference
When using chrome for this, is there a good way to use a Do While loop like with IE.busy & .Readystate?
we need to wait for the element to be visible or in some cases the element to get enabled.
Hi,
Can this code take the file path from excel worksheet and upload multiple files ?
Yes, you can
Shell"wscript.exe " & strFile & " " & strUploadFile (showing compile error and syntax error) please help
When my upload file path contain space in it then I am facing issue in pasting the path. Can you please help.
Eg path : E://New Folder/file.txt
In this above scenario while pasting the url only "E://New" is getting pasted
yes it does not work with spaces
I am getting the error 462 which says The remote server machine does not exist or is unavailable. Ay idea how to resolve this?
I checked and followed everything and also took note of comments added everything.
can you share your code?
@@AutomationMadeEasy Thanks for getting back. Really appreciate it. The page opens but then the upload is not working there is when i get the error.
The below is the code i copied from your provided space. But in turn i am trying to upload a folder from desktop to Dropbox and then use those uploaded image files back into excel and then then convert them into URL's.
The below is the vbs file:
Set WshShell = CreateObject("WScript.Shell")
Do
ret = WshShell.AppActivate("Choose File to Upload")
Loop Until ret = True
WScript.Sleep 1000
ret = WshShell.AppActivate("Choose File to Upload")
If ret = True Then
WshShell.Run "cmd.exe /c echo " & WScript.Arguments(0) & "| clip", 0, True
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "^{v}"
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "{ENTER}"
End If
WScript.Sleep 1000
Set WshShell = nothing
The next is the module code:
Sub uploadFiles()
Set IE = CreateObject("InternetExplorer.Application")
IE.navigate "www.automationandagile.com/p/sample-form.html"
IE.Visible = True
Application.Wait DateAdd("s", 5, Now)
strFile = "C:\Users\user\Desktop\UploadFile.vbs" '--Mention path where you stored the .vbs file in Step 3
strUploadFile = "C:\Users\user\Desktop\EUebGt1XkAYe9N81.png" '--Mention path of the file which you wish to upload
Shell "wscript.exe " & strFile & " " & strUploadFile
IE.document.getElementsByName("myFile")(0).Click
Application.Wait DateAdd("s", 2, Now)
End Sub
I will be working to automate my scenario once your works. So would be great if you could help!
go to tools/references
Select Microsoft Shell Controls and Automation
Save then re try
@@AutomationMadeEasy Already did that.
can we do the same from google chrome rather than IE ? , if so please help us by providing the code for the same.. because most of the company which we are working has started using chrome than IE , may be for the speedness ..
so request you to make a saparate video or you can do the continuation of this same with chrome.. pls ..
Refer below tutorial for web automation using chrome:
th-cam.com/video/-GUw22ecRwI/w-d-xo.html
For file upload automation use the same techhique as explained or copy the vbs script code in moacro module. It will work
how can i do the same thing but with another browser, maybe chrome or firefox?? thanks! And can you share the files from the video?
For chrome or edge refer below tutorial:
th-cam.com/video/AEp0Yeis9AM/w-d-xo.html
Apply the sam logic for file upload. The script is provided in the blog itself.
This code was fabulous.but when it always takes second Items in the folder path any solution ?
didnt get your question. Can you explain with an example?
@@AutomationMadeEasy File Explorer box has been opening but did not paste the copied item in File name . How to give delay in vbscripts any example
I need to upload all the files in a folder. I can use
wshshell.sendkeys "^{A}"
to select all the files, but then, how to modify this line
Shell "wscript.exe " & strFile & " " & strUploadFile
Really don't want want to have to resort to uploading all the files individually.
Any help greatly appreciated.
Is there a way to do this on a rdc. It seems that the vbs file will only enter the file path of what I'm trying to upload if I'm active. Any tips?
this approach needs the window to stay in focus so it wont work if remote desktop is minimized. But on certain web pages it is possible to enter file path without opening the File upload dialog. Will need to to see your web page to assess if that approach can be used
Thanks, I after researching I did it with this FindElementByXPath(".../div[1]/span/input").SendKeys (dirFile) , which seems to be another way to do it without needing vbs
great
Hello, Once I run the VBA everything works fine, but after the VBS script sends the sendkey {enter}, my file does upload but I get a "windows script host error" saying "There is no file extension in "c:\Users\alain\onedrive\desktop\automation\loan" but my full path in VBA is "C:\Users\alain\OneDrive\Desktop\Automation\Loan Entry\Resources\FileUpload.VBS". It seems to cut off the last part of it and I don't know why? any suggestions?
if you see there is space between Loan and Entry in the 'Loan Entry' folder. This technique fails if there are spaces in the file path. Try to rename the folders without space and try
@@AutomationMadeEasy Thank you So much for the help! that fixed that issue!!! I have one final question. I am have a macro that calls this macro and sometimes instead of pasting the file path into the upload window, it just pastes as "v" as if it didn't pick up the "^" for the command +v paste sendkey. any recommendation? Overall this has been super helpful
Try to increase the delay before ^c and ^v
The execution is stopping at the below line
Shell "wscript.exe " & strVBSFile & " " & strUploadFile
saying " Run Time Error 5 - Invalid procedure call "
Not even opening the choose file to upload window as the command to click on browse is below this line. please explain how is it working for you
the steps are explained in detail with the code on the blog which was used in the tutorial. Please refer the same.
Automation Made Easy - I tried but couldn't find! Could you plz upload the blog link here for easy reference..Thanks :)
Same as me
How to do the loop then?
How can we automatically upload data particular link
How can we automatically upload multiple data particular link in one click from local machine folder
Can you give an example as to what you actually want to achieve
Automation Made Easy I want to upload multiple excel file in share point link on daily basic from local machine please suggest how can we do that
Hi , i tried the procedure but when the screen opens to select path, the path didnt got copied or paste.
can you help me to sort this out
shre ur code
Dim IE As Object
Dim strVBSFile As String
Dim strUploadFile As String
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "www.automationandagile.com/p/sample-form.html"
IE.Visible = True
Do While IE.Busy Or IE.ReadyState 4
Application.Wait DateAdd("s", 1, Now)
Loop
strVBSFile = "E:\VBA\FileUpload.vbs"
strUploadFile = "E:\VBA\Book1.xlsm"
Shell "wscript.exe " & strVBSFile & " " & strUploadFile
IE.Document.getElementsByName("myFile")(0).Click
Application.Wait DateAdd("s", 2, Now)
End Sub
this is a working code. There could be sme error in your vbs file
@@AutomationMadeEasy hello thanks for looking into the code pls look into the vba Set WshShell = CreateObject("WScript.Shell")
Do
ret = WshShell.AppActivate("Choose File to Upload")
Loop Until ret = True
WScript.Sleep 500
ret = WshShell.AppActivate("Choose File to Upload")
If ret = True Then
WshShell.Run "cmd.exe \C echo " & WScript.Arguments(0) & "| clip", 0, True
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "^{v}"
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "{ENTER}"
End If
WScript.Sleep 500
Set WshShell = nothing
बेस्ट
thank you
I've tried selenium vba
but it doesn't work
what error are u getting?
Hi , i tried the code but when the dialog box opens to select path, the path didnt got copied or paste.
share ur code
@@AutomationMadeEasy this is the code in VBA
strFile = "E:\2020\Fileupload.vbs" '--Mention path where you stored the .vbs file in Step 3
strUploadFile = "E:\2020\104.pdf" '--Mention path of the file which you wish to upload
Shell "Wscript.exe " & strFile & " " & strUploadFile
HTMLdoc.all.RawasLhpForm_fbalk.Click
and the code below (VBS) :
Set WshShell = CreateObject("WScript.Shell")
Do
ret = WshShell.AppActivate("Choose File to Upload")
Loop Until ret = True
WScript.Sleep 500
ret = WshShell.AppActivate("Choose File to Upload")
If ret = True Then
WScript.Sleep 2000
WshShell.Run "cmd.exe /c echo " & WScript.Arguments(0) & "| clip", 0, True
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "^{v}"
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "{ENTER}"
End If
WScript.Sleep 500
Set WshShell = nothing
Sometime it works but i try again it didnt work, it appears only "v"
try increasing the delay
@@AutomationMadeEasy delay on vba or in vbscript?
I am getting runtime error 7 out of memory. Can you please help me to solve this error.
share ur code
@@AutomationMadeEasy here is my code
Sub testauto()
Dim obj As New WebDriver
obj.Start "edge", ""
obj.Get "My Website Admin URL"
obj.FindElementByName("log").SendKeys ("Website USERNAME")
obj.FindElementByName("pwd").SendKeys ("Website Password")
obj.FindElementByName("wp-submit").Click
obj.Get "Website Post URL Where I want to Upload Image"
strUploadFile = "E:\AbcMall.png"
strVBSFile = "E:\UploadFile.vbs"
Shell "wscript.exe " & strVBSFile & " " & strUploadFile
obj.FindElementByXPath("//*[@class='wpsm_tax_helper_upload_image_button button']").Click
obj.FindElementById("menu-item-upload").Click
obj.FindElementById("__wp-uploader-id-1").Click
Application.Wait DateAdd("s", 2, Now)
obj.FindElementById("attachment-details-alt-text").SendKeys ("sumit")
obj.FindElementByXPath("//*[@class='button media-button button-primary button-large media-button-select']").Click
Application.Wait DateAdd("s", 2, Now)
End Sub
_______________________________________________
VBS FILE CODE
_______________________________________________
Set WshShell = CreateObject("WScript.Shell")
Do
ret = WshShell.AppActivate("Open")
Loop Until ret = True
WScript.Sleep 1000
ret = WshShell.AppActivate("Open")
If ret = True Then
WshShell.Run "cmd.exe /c echo " & WScript.Arguments(0) &"|clip", 0, True
WScript.Sleep 5000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 4000
WshShell.SendKeys "^{v}"
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "{ENTER}"
End If
WScript.Sleep 1000
Set WshShell = nothing
The Image path is copied but It was unable to paste in the dialogbox.
I am using wordpress website, Windows 10 and Microsoft Office 2019 Version and I am using selenium basic for automation.
Can you please check and tell me is there any problem ?
Thank you so much.
Since you are using selenium vba, No need to use seperate vbs file.
After clicking on browse button you can use the tab and enter keys as given below:
Set WshShell = CreateObject("WScript.Shell")
WshShell.Run "cmd.exe /c echo " & strUploadFile &"|clip", 0, True
Application.Wait DateAdd("s", 2, Now)
obj.FindElementByXPath("//*[@class='wpsm_tax_helper_upload_image_button button']").Click
Application.Wait DateAdd("s", 2, Now)
Application.SendKeys "{TAB}"
Application.Wait DateAdd("s", 2, Now)
Application.SendKeys "{TAB}"
Application.Wait DateAdd("s", 2, Now)
Application.SendKeys "^{v}"
Application.Wait DateAdd("s", 2, Now)
Application.SendKeys "{TAB}"
Application.Wait DateAdd("s", 2, Now)
Application.SendKeys "{TAB}"
Application.Wait DateAdd("s", 2, Now)
WshShell.SendKeys "{ENTER}"
@@AutomationMadeEasy Thank you so much sir, It working perfectly.
Its opening but not uploading
Hola Bro, cuando ejecuto la macro y luego de bastantes segundos, me dice : "Microsoft está esperando a que ' ' complete una acción OLE". Que puede ser esto y como lo soluciono? por favor ayuda
share your code.
@@AutomationMadeEasy
Hola
por supuesto. Aquí lo detallo. Los dos archivos están guardados en C:\CARPTEMPORAL. Por favor ayuda
'Este es el Codigo VBA
Sub uploadFile()
Set IE = CreateObject("InternetExplorer.application")
IE.navigate "girardot-cundinamarca.gov.co/Ciudadanos/Paginas/Carga-Archivo.aspx"
IE.Visible = True
Do While IE.Busy Or IE.readyState 4
Application.Wait DateAdd("s", 1, Now)
Loop
strUploadFile = "C:\CARPTEMPORAL\text1.txt"
strVBSFile = "C:\CARPTEMPORAL\FileUpload.vbs"
Shell "WScript.exe " & strVBSFile & " " & strUploadFile
IE.document.getElementById("ctl00_ctl58_g_280f7905_95ae_4ab5_b3cd_e1765b71e240_ctl00_ATXT").Click
Application.Wait DateAdd("s", 1, Now)
End Sub
'ESTE ES EL CÓDIGO DEL SCRIPT
Set WshShell = CreateObject("WScript.Shell")
Do
ret = WshShell.AppActivate("Choose File to Upload")
Loop Until ret = True
WScript.Sleep 1000
ret = WshShell.AppActivate("Choose File to Upload")
If ret = True Then
WshShell.Run "cmd.exe/c echo " & WScript.Arguments(0) &"| clip", 0, True
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "^{v}"
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "{TAB}"
WScript.Sleep 2000
WshShell.SendKeys "{ENTER}"
End If
WScript.Sleep 1000
Set WshShell = nothing
@@AutomationMadeEasy please help. i share the code
WshShell.Run "cmd.exe/c echo " & WScript.Arguments(0) &"| clip", 0, True
In above command a space is missed after cmd.exe. Correct command is given below:
WshShell.Run "cmd.exe /c echo " & WScript.Arguments(0) &"| clip", 0, True
@@AutomationMadeEasy I did it but the problem persists.
In Excel say: "Microsft excel está esperando que otra aplicación complete una acción OLE"
Please help me, how can i solve it?
How to upload file with long filename / filepath?
as long as there are no spaces in file path and file name it will work
Is it possible to handle the spaces, e.g., add under_score to file name/path? Thanks
@@AutomationMadeEasy Thanks for great video. I got another problem, when using vba to click a option button (totally 2 options) (not the default one), i got error "91". Grateful if could advise. Thanks again.
need to see your script
@@AutomationMadeEasy My script:
Set radioButtons = doc.getElementsByName(CDType6)
radioButtons(1).Click
Shell line in vba module giving syntax error. ☹
try selecting Shell Controls and automation from Tool/References
@@AutomationMadeEasy Thank you so much for guidance and such code. Now it is working well.
I was searching on internet for many months about uploading a file on html page, but yet not found such code. It is a greate help.
These are "Microsoft Shell Controls and Automation".
your welcome
A month ago it worked well. Now on same system the same code is not pasting the image path in file upload window. Recommended shell controls are also checked. Don't know whats wrong happened to it.
Tell us about that shell vba file, any ur tutorial link. Please
its there on the blog used for the tutorial
Sorry, but this code sucks. I am trying since this morning to make it working but nothing.
My pc alway goes down and i need to restart