VBA to save file (downloaded from server) to specific folder

[HeenaS]

I would be grateful if someone can help me to write code to make an macro enabled excel uploaded on a webpage to be saved at specific folder when downloaded.

as of now I dont have any clue how to do this, m new to VBA... I have a macro template which is uploaded on website which I want to make it to save in C drive when downloaded... if it is saved somewhere then itshould display the msg box to make it save.

Any help will be appreciated.

 

[Marc L]

​

Start here …​

​

 

[HeenaS]

​

Start here …​

​

​

​

As I mentioned, I do not have any code written for that, I dont know how to start from where to start so I can not provide any file or anything as it does not exist as of now.. its just an excel which will be uploaded on website and will b downloaded by user .. we want to make that file to be saved at specific folder.... ​

 

[Marc L]

​

Read this !​

​

 

[Chihiro]

.... wait so you want to enforce download location when user downloads a file? That's not really VBA function. Should rather be done at GPO (group policy object managed by IT).

But then, you will need to give full detail of what your manual process is and details of your constraints and parameters. After all, we are not a mind reader and don't know anything about your project or network environment

 

[Marc L]

Hi Chihiro !​

​

It can be done in the case of a direct URL on the file to import (so not a webpage), there is a sample in this forum;​

but as you yet wrote, we are not on a mind reader forum neither on a do-my-job.com website …​

​

 

[Chihiro]

@Marc L

Oh, I took it as user had other option to download outside of direct download via VBA Your interpretation makes more sense.

 

[Marc L]

​

As a reminder : any web Excel workbook - so via a direct file URL - can be directly opened in Excel like any local workbook​

so very easy to save to a specific folder, that just needs two codelines than any beginner can get using the Macro Recorder …​

​

 

[HeenaS]

Yes that excel workbook will be at same URL.. I just want to enforce it to save at a specific folder.. would you pls help me to get the code pls as I dont know where to start with

 

[Marc L]

In this case as yet written, use the same way to open a local workbook !​

So just type this URL (or paste, whatever) in the Open file box and valid.​

If it works (only for a direct file URL) you can first activate the Macro Recorder​

then open the workbook still via its URL then save the workbook to the specific folder : you will get your own free code !​

​

See the VBA inner help of Workbooks collection for its methods Open, SaveAs & SaveCopyAs …​

​

 

[HeenaS]

.... wait so you want to enforce download location when user downloads a file? That's not really VBA function. Should rather be done at GPO (group policy object managed by IT).

But then, you will need to give full detail of what your manual process is and details of your constraints and parameters. After all, we are not a mind reader and don't know anything about your project or network environment

Yes you got it right.. I want to enforce download location for specific file. It is on Intranet but I feel there should not be any dependency about network environment...
I used below code but it is showing replica of existing file at given location and doesnt bound to open it from specified folder. I want it to be enforce to be saved while download only.

Sub SaveWorkbook()
'Saving the Workbook
ActiveWorkbook.SaveAs "C:\Locke\Inspector.xlsm"
End Sub

 

[HeenaS]

In this case as yet written, use the same way to open a local workbook !​

So just type this URL (or paste, whatever) in the Open file box and valid.​

If it works (only for a direct file URL) you can first activate the Macro Recorder​

then open the workbook still via its URL then save the workbook to the specific folder : you will get your own free code !​

​

See the VBA inner help of Workbooks collection for its methods Open, SaveAs & SaveCopyAs …​

​

I tried recording macro but I didnt get any code in that... Below is the one I got after recording.. do I need to enable any specific tools?

Sub Macro1()
'
' Macro1 Macro
'

'
End Sub

 

[Marc L]

Nope …​

​

As yet written that needs two codelines : Workbooks.Open and Workbooks.SaveAs (to see in VBA inner help) …​

​

 

[HeenaS]

I have this code.. seems fine but it is acting only when I run module from developer tab.. I want it to read it as soon as file is opened. pls suggest.



Sub SaveToDir()
'
Dim wbk As Workbook
'
CDir = ActiveWorkbook.Path
'
SaveName = "Inspector" & ".xlsm"
If CDir = "C:\Locke" Then
Resp = MsgBox("File name: " & SaveName & vbCrLf & vbCrLf & "already exists in: " & vbCrLf & vbCrLf & CDir & vbCrLf & vbCrLf & "Press Okay to continue, Cancel to abort", vbOKCancel)
If Resp = vbCancel Then
Exit Sub
End If
Else
ActiveWorkbook.SaveAs "C:\Locke\Inspector.xlsm"
End If
End Sub

 

[Chihiro]

Add it to Workbook_Open event in ThisWorkbook module (found in left pane of VBE). However, this type of distribution is best enforced via GPO/Logon Script (PowerShell etc) in my opinion.

 

[HeenaS]

Add it to Workbook_Open event in ThisWorkbook module (found in left pane of VBE). However, this type of distribution is best enforced via GPO/Logon Script (PowerShell etc) in my opinion.

Thanks .. can you pls help me with GPO?Logon script... or any further suggestions

 

[Chihiro]

That's domain of IT administrators. And this is not the right forum to discuss. I'd recommend asking in stackoverflow or PowerShell forum.