Macro to attach file to document

mrhoden

New Member
Joined
Mar 5, 2016
Messages
10
Hi,

I'm new to VBA and I'm attempting to find a macro that will allow me to attach a file to my workbook. I found this code on this forum, but it appears to only link to a file path, rather than actually attaching the document.

Code:
Sub Button14_Click()

Dim strFilename As String
Dim strShortName As String

strFilename = Application.GetOpenFilename("All Documents (*.*), *.*")

If strFilename = "False" Then
    Exit Sub ' user cancelled
End If

strShortName = InputBox("What do you want to call this link?", "Short Text", strFilename)

ActiveSheet.Hyperlinks.Add Anchor:=Range("D36"), Address:=strFilename, TextToDisplay:=strShortName

End Sub


Any help would be greatly appreciated!

(By the way, did I put the correct tags around the VB??)

Regards,

Marty
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
Hi and welcome to the forum. You got the code tags right :).

AFAIK Excel doesn't support attaching a file to a workbook.

Although the file format (post xl2007) is really a ZIP container it enforces the content quite strictly so I don't think you could even embed the file in the container.

If you really need the file to be accessed from a workbook sent to an outside location then I suggest using a hyperlink to the file shared from a cloud storage site (e.g.Box).
 

mrhoden

New Member
Joined
Mar 5, 2016
Messages
10
Thanks for the reply, Teeroy.

That's a shame. I'm trying to create a userform where teachers can leave work for replacement teachers. I've created all of the other fields, but if the teacher wants to leave a worksheet to be printed, copied and handed out, they currently have to leave this in a tray somewhere... I even had a macro to auto email the students, so this really would have solved all of our problems! Oh well...

I don't suppose you could suggest anything??

Thanks again.
 

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
If you have SharePoint available you can put the files there and hyperlink directly to them, noting that you can't directly hyperlink from a UserForm (as you can from a WorkSheet), but you can create an element that feels similar.

Otherwise I'd probably set up an account on Box with logon information made generally available to your staff.

Connect this account to a mapped drive on the PC's using WebDAV so that you can drag and drop files onto it and hyperlink to it easily. A WebDAV drive is a bit slower than a physical drive but it's very user friendly.

Connecting the account can be easily automated using either a BAT(ch) file or VBScript (which is not VBA but shares some similar features).

I hope this helps.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,078
Messages
5,622,548
Members
415,907
Latest member
Walters87

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top