Solved: Changing e-mail "subject" line within a Macro

[JeffT]

Hi

I've written (using others code as a basis) a macro which on clicking a button on the tool bar opens a new e-mail with the reverse date in the subject line, eg 070530 would be todays, (it's just what we do). I can also add for instance the current contract name I'm working on as well. Then I only have to type a couple of extra descriptive words.

However others in the company have seen this, and want to do the same. The problem is they aren't capable of editing macros within VBA and so I want to know if it's possible to use a dialog box, in which they can type their subject, and then have this entered into or called from the macro.

I asked something similar earlier and had no reply. If the answer is no I'd like to know that as well.

I'm currently playing with the idea of storing the words in a spreadsheet (Which they'd be happy editing) and calling this, copying the cell value and pasting this into the subject line. Not sure if this is possible either. However using code I found here it's possible to paste words into the spreadsheet from the macro, so I'd have thought it could work the other way. However hopefully there's a more elegant way.

Hopefully someone will let me know one way or another.

Regards

Jeff T

[mvidas]

Jeff,

Are you running this from outlook, or from Excel? I'm assuming outlook, so I'll show you how to do it from there, but I can modify it to call it from excel too if you'd like:[vba]Sub JeffT()
Dim TempStr As String, iMsg As MailItem
TempStr = InputBox("Please enter subject for new mail", "Enter subject line", _
Format(Date, "yymmdd "))
If Len(TempStr) = 0 Then Exit Sub
Set iMsg = Application.CreateItem(0) '0=olMailItem
iMsg.Subject = TempStr
iMsg.Display
End Sub[/vba]Matt

[JeffT]

mvidas

Thanks for the reply, I'm running everything from outlook. The code works fine but it isn't what I'm trying to do.

I'm trying to make it possible so that whatever you type in the InputBox is memorized somewhere, and then whenever you open a new e-mail, this is used to complete the subject line.

I wondered if you can use a macro to edit another macro. From the total silence in my first post on the subject I guess not. This is when I had the idea of perhaps first pasting the subject line into a spreadsheet cell then copying this back into the subject line when running the macro, perhaps keeping it in memory for the whole outlook session.

Hopefully you can understand what I'm thinking even if the terminology isn't correct. I'm afraid I don't have a deep understanding of the mechanics of how VBA works just enjoy it!

regards

Jeff t

[mvidas]

Ahh, I understand now.

"macro to edit another macro" -- this would be possible in most VBA environments, however not in outlook VBA. There is no easy programmatic access for something like this.

However, it is possible to accomplish what you want to do. There are 3 ways I can think of at the moment, I'll go from least recommended to most recommended.

1) Storing the subject line during the outlook session. This is possible using a global variable (among other things). You could have it look in a specific cell of a specific workbook, or even just have an Inputbox popup when outlook is first opened using the application_startup event of ThisOutlookSession.

2) Store the desired subject line in the registry. The code could check a specific spot of the registry (one created just for this). If the entry exists, use that as the subject line. If not, an inputbox can pop up asking for it (then subsequently store it for future use).

3) Store the desired subject line in a text file on the users hard drive. This would act similarly to #2, however it would give the user the ability to edit it easier (they would simply have to open the text file in notepad or something).

Whichever route you decide to take (I can help you through any of them), you would use the following shell code:[vba]'*** Begin ThisOutlookSession CODE ***
Option Explicit

Sub Application_Startup()
vSubjectLine = GetSubjectLine
End Sub
'*** End ThisOutlookSession CODE ***[/vba]
[vba]'*** BEGIN MODULE CODE ***
Option Explicit
Public vSubjectLine As String

Function GetSubjectLine() As String
'choice of subject retrieval goes here
End Function

Sub CreateNewMessage()
With Application.CreateItem(0) '0=olMailItem
.Subject = Format(Date, "yymmdd ") & vSubjectLine
.Display
End With
End Sub
'*** END MODULE CODE ***[/vba]

[JeffT]

Thanks mvidas

Unfortunately I can't get the macro to work. If I run the CreateNewMessage macro from within VBA but if paste this into the button .OnAction = "CreateNewMessage" the button doesn't carry out any action.

I did have a problem with ThisOutlookSession as I already had a sub Application_Startup which acivates the toolbar buttons. (This is in fact a private sub, don't know why I copied it from elsewhere). I have put vSubjectLine = GetSubjectLine in here.

Next what am I supposed to put in 'choice of subject retrieval goes here?
I've tried to put the path to the text file in here but it just gives an error.

I was thinking of a spreadsheet as I wanted to put paths to different folders in different cells then reference buttons on a form to these cells for saving e-mails to different places. If a Text file could be used this would be fine but I don't know how to reference the buttons to different lines. this may be possible if so it would also be ok.

Hopefully this will go I've typed thsi 3 times but keep getting logged out for some reason.

Thanks again

Jeff T

[JeffT]

Thanks mvidas

Unfortunately I can't get the macro to work. If I run the CreateNewMessage macro from within VBA it works, but if paste this into the button .OnAction = "CreateNewMessage" the button doesn't carry out any action.

Sorry missed some important words

[mvidas]

Unfortunately I can't get the macro to work. If I run the CreateNewMessage macro from within VBA it works but if paste this into the button .OnAction = "CreateNewMessage" the button doesn't carry out any action.

Hmmm.. is it possible you have more than one sub named CreateNewMessage?
Actually, after just trying to recreate the same thing I noticed that when manually adding a macro as a menu item it uses the project codename as well in it, like "Project1.CreateNewMessage". Try adding it manually (close VBE, right-click menu bar, Customize, Categories: Macros, then look in the available commands for the macro. If you have multiple macros of the same name you'll need to add the object name too. Looking in the customize section should help. In my experience, I've only added menu options via COM Addins, and use a custom event class rather than the .onaction (to call code from the addin rather than vbaproject.otm)

I did have a problem with ThisOutlookSession as I already had a sub Application_Startup which acivates the toolbar buttons. (This is in fact a private sub, don't know why I copied it from elsewhere). I have put vSubjectLine = GetSubjectLine in here.

Thats fine, as long as the vSubjectLine variable gets filled at startup, adding it in your existing sub is perfect.

Next what am I supposed to put in 'choice of subject retrieval goes here?
I've tried to put the path to the text file in here but it just gives an error.

I just put that as a 'shell' function, so you could decide which of the 3 options above you wanted to use.

For example, if you wanted to pull the subject line from a text file, you could use something like:[vba]Function GetSubjectLine() As String
Dim vFile As String, vFF As Long, tempStr As String
vFile = "C:\subject line.txt" 'file containing subject line
If Len(Dir(vFile)) = 0 Then 'file doesnt exist yet
'get subject from user
tempStr = InputBox("Please enter default subject line", "Enter subject")
'create text file and put subject into it for future uses
vFF = FreeFile
Open vFile For Output As #vFF
Print #vFF, tempStr;
Close #vFF
Else 'file exists, read subject from it
vFF = FreeFile
Open vFile For Binary Access Read As #vFF
tempStr = Space$(LOF(vFF))
Get #vFF, , tempStr
Close #vFF
End If
GetSubjectLine = tempStr
End Function[/vba]
Using the registry would be basically the same thing, though would use a bit more code (no runtime difference really). I'll post that code if you're interested, but save thread space until then.

If you wanted to simply ask the user each time outlook is opened for their choice of subjects:[vba]Function GetSubjectLine() As String
GetSubjectLine = InputBox("Please enter default subject line", "Enter subject")
End Function[/vba]

And to get it from an excel cell (though this may need to be modified a bit depending on how you want to do it, like have the file open all the time or whatever):[vba]Function GetSubjectLine() As String
Dim xlApp As Object, xlWB As Object, AppOpen As Boolean
Dim tempStr As String, xlFile As String
xlFile = "C:\excel file.xls"
On Error Resume Next
Set xlApp = GetObject(, "excel.application")
On Error GoTo 0
If xlApp Is Nothing Then
AppOpen = False
Set xlApp = CreateObject("excel.application")
Else
AppOpen = True
End If
xlApp.ScreenUpdating = False
Set xlWB = xlApp.workbooks.Open(xlFile)
tempStr = xlWB.sheets("Sheet1").Range("A1").Text 'cell with subject
xlWB.Close False
xlApp.ScreenUpdating = True
If Not AppOpen Then
xlApp.Quit
Set xlApp = Nothing
End If
End Function[/vba]

I was thinking of a spreadsheet as I wanted to put paths to different folders in different cells then reference buttons on a form to these cells for saving e-mails to different places. If a Text file could be used this would be fine but I don't know how to reference the buttons to different lines. this may be possible if so it would also be ok.

Not sure that I'm following you here.. do you want to list different folders and have your form automatically create buttons to save to different locations based on the list of folders? Or do you have a list of folders that you want to use based on the email sender or subject or something similar?

[JeffT]

Wow mvidas, I'm afraid this'll take a bit of time for me to digest. I'm completely self taught, with lots of help from you guys. (I need to buy a book solely on VBA to progress!) The # thing is completely new to me though I think I can follow the logic of most of your code. I'll use F8 to step through it to understand more fully what it does.

Re your queries. I don't think I'll go near the registry, to much to mess up I believe.

I will have button for the user to choose a path if all the others buttons are allocated to folders not required for a particular e-mail. I was going to try and bring up the Save or SaveAs dialog box, hopefully with it remembering where the last path went to during any outlook session.

The Form I've made has 5 tabbed pages each with 20 buttons. We use a 1 -30 folder structure on the server for each contract, and each of these has subfolders. Eg folder 10 is for subcontractors and we may have say 50 of these. The idea is to click on a button and the e-mail will be saved to a particular folder represented by that button. (Getting the button names from the spreadsheet would be useful as well!)

I want the form to pop up whenever an e-mail is closed or sent thus making it easy to file. However as different people, work on different contracts, we need to be able to easily change the path, so a default list won't work. Also using rules doesn't work cause the same subcontractor for instance may be used on different contracts.

To do this I was hoping to use a spreadsheet with 100 cells containing the different paths, linked to different buttons. I may use the Save box or similar so these could be added by selecting the path, but that'll be later.

Hopefully this isn't too long winded. I'll try out your code over the next few days, thank you very much for spending the time on my problem.

Regards

Jeff T

[mvidas]

Hi Jeff,

It definitely sounds possible. Before I make any stabs at implimenting this for you, I thought i'd expand on your "(Getting the button names from the spreadsheet would be useful as well!)" comment, to make it easiest for you.

Also, don't worry about being long-winded, as you can probably tell I do the same thing. More details = better, IMO. I'm self-taught too, so don't hesitate to ask if you have any questions

Anyways, you have a form with 5 tabs, and want 20 buttons each.
What would you say to having an excel sheet (or even just a text file, excel isnt necessary, just usually easy for most people to work with) with columns for "Tab name", "Button name", "Folder". The VBA could look through this, and dynamically add buttons (though if you will always have 20 buttons each, this isn't necessary) for each one listed per tab. With/without the dynamic buttons, the code can loop through the list, change the button name to the one specified, and change the linked directory as specified as well. I won't have the code look at that spreadsheet every time a button is clicked, but instead probably when the form is loaded and just store the folder data in memory. I'll think a little about that though

I have to admit that I have very little experience with outlook forms, only delving into them while helping others. Could cause a bit of a delay in helping you as i try and bring myself up to speed when needed

Do you have a network drive that you could store a 'master' spreadsheet that everyone's forms can pull from? I suppose we could get into that more later, just a thought

[JeffT]

mvidas

If I ever reach 1/100th of your knowlege I'll be happy!

What you say is exactly what I'm intending to do. I thought I'd have in column A the Tab names every 20th cell, Column B the button names, Column C the button Paths. Then on the spreadsheet I'd put a sort button which sorted columns B & C (& perhaps other Buttons which only sorted the individual Tab blocks of 20. This is because some people work on multiple contract and may want each tab to give their 20 "favorites" for each contract.) The sort function is so that the buttons can remain in alpha numeric order when some are added and some removed. (As a subcontractors completes the initial phase of construction they leave so their e-mail traffic dries up, and the finishing trades start so their e-mail traffic gets heavier.)

I was also going to add a cell, say near the top, which had the default path where the "Save As" button starts E.g N:\Contracts\3301.....

I was hoping that the links etc could be loaded at the start of each session. My intention is to open the spreadsheet using code in Outlook, and hope it opened quick enough for the links to be picked up, then it could close. If not I'd just tell everybody to open the spreadsheet first with a Yes No dialog box as Outlook opened. I've found some code on VBAX which can store a spreadsheet (or other file type) as though it were an e-mail within outlook itself so thought I'd put it here if the auto opening worked.

We do have a network N: drive so I could put the master there but IT / QA are very protective of what goes where. At first I'd just load it at the same time as I copy the various Modules etc into the correct places on each computer. (I have no idea how to write a programme to self install, I just want to get this out into use at first so that people start filing e-mails instead of them remaining on individual computers.)

Thank's for your continued interest.

Jeff

[mvidas]

Unfortunately I don't have the time today to look any more at this, but I should have a little more time tomorrow (~17 hours from now). Posting a message here just so it pops up on my new thread list wouldn't be a bad idea (keep me honest ). A couple quick things though

I can write it so the excel file never actually opens in excel, using ADO (treating the excel file like a database). This should eliminate any two users having it open at the same time (of course, it could always just be opened read-only to prevent this too).

To have the Save As button start in a particular directory, the ChDir command in VBA can be used (ie ChDir "N:\Contracts" )

If you have access to VB6 you can make an outlook add-in, which will prevent the user from having to enable macros and also prevent you from having to copy the module/etc onto each.

Storing the spreadsheet in an email in Drafts isn't a bad idea, though I think having a master would be easiest overall (except, it does sound like your users will want the buttons in different orders).

Lastly, is this form you have all this on just a userform in VBA, or is it a 'custom outlook form'?

[JeffT]

Any time you have will be gratefully received, I'll just continue to play with what you've already given me thanks.

I've just Googled ADO having no idea what this is, now I'm a tiny bit wiser. If the Excel file is never opened how would people be able to populate it? I presume some type of form opens allowing the data to be entered. This is getting beyond my capability. I was thinking of using something like screenupdating = False .... = True, though I realise this may not work crossing between different applications. I don't think I can have 2 people using the same Excel file as different people will want different folder options.

I'll have a look at ChDir, however again different people may want to start with different folders. Some may want N:\Tenders some N:\Contracts (& we have more!). Perhaps ChDir can accomodate this I'll try.

I'm afraid I don't have VB6. I may try it if it helps but would probably need to be more proficient at VBA first.

The form I have is a userform in VBA, I'm afraid I don't know any other though will look at this as well if it'll help.

Jeff

[mvidas]

Re: ADO, heres a sub I made while exploring how it worked, and I use it as a basis of what I need to do anytime I want to interface excel/ado. Should give you a decent footing of what you can do, at least to read the file:[vba]Sub XLAdo()
'An example of how to use ADO to access an excel file.
' First it debug.print's the sheet names,
' Then debug.print's each cell value from the header row of the first sheet
' Then debug.print's each cell's value from every other cell in the first sheet
Dim xlConn As Object 'ADODB.Connection
Dim xlRS As Object 'ADODB.Recordset
Dim xlSheets As Object 'ADODB.Recordset
Dim xlFld As Object 'ADODB.Field
Dim vFile As String
Dim vSheet As String

'file location
vFile = "C:\ado.xls"

'connect to the file
Set xlConn = CreateObject("ADODB.Connection")
With xlConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0;IMEX=1"
.Open vFile
End With

'see sheet names in the immediate window
Set xlSheets = xlConn.OpenSchema(20) '20=adSchemaTables
While Not xlSheets.EOF
vSheet = xlSheets.Fields("TABLE_NAME").Value
Debug.Print vSheet
xlSheets.MoveNext
Wend
xlSheets.Close
Stop

'open recordset to get access to worksheet data
strSQL = "SELECT * From [" & vSheet & "]"
Set xlRS = CreateObject("ADODB.Recordset")
xlRS.Open strSQL, xlConn, 1, 1 '1,1=adOpenKeyset, adLockReadOnly

'see column names in the immediate window (first row with data)
For Each xlFld In xlRS.Fields
Debug.Print xlFld.Name
Next
Stop

'see cell values in the immediate window
While Not xlRS.EOF 'starts at 2nd row of data
For Each xlFld In xlRS.Fields
If Not IsNull(xlFld.Value) Then
Debug.Print xlFld.Value
End If
Next
xlRS.MoveNext
Wend

xlRS.Close
xlConn.Close
Set xlConn = Nothing
Set xlRS = Nothing
Set xlFld = Nothing
Set xlSheets = Nothing
End Sub[/vba]As for populating it, it would still need to be done manually. It can be done programmatically, but in this scenario it isn't necessary.

If different people want different spreadsheets, then maybe this kind of thing isn't necessary, since you want to store the last directory looked at. Creating a separate instance of excel and keep it invisible might not be a bad option to have this functionality. But using ChDir and the last used directory's cell would work fine for different people if they all have their own 'helper' spreadsheet.

A VBA userform is what I know too, so keeping it that way is a good idea so I won't have to delve into custom forms again

Would you be able to export and attach the form you currently have, so I can just work with that instead of creating my own form to try and match yours?

[JeffT]

Mvidas thanks for the ADO code, I'll give it a go shortly to see what it does.

I managed to pick up the path from my spreadsheet using the last of your code posted on the 4th. I had to set tempStr to Public at the start of the Module to get the value to move between Modules (probably obvious to you but a learning point for me ). It doesn't seem as though it'll keep the value as looking at the value at the start of running the cde for a second time it shows "". I've looked at help and it seems I may have to change the variable to Static, I'm not sure but that's my next thing to try.

Is there a way to get a name on a button / Tab from the spreadsheet?

As requested I attach my form (I hope it's attached it's my first time trying). It seems that 2 files are created when I exported and as the file types aren't supported I've zipped them.

Hope that's correct

Regards

Jeff T

[mvidas]

Ok, I think you're gonna like this

I'm attaching "QuickFormFiles.zip", containing:
"clsQuickFormButton.cls" - Import this into your Outlook VBA project
"Quick_Form.frm" - Replace your current form with this
"Quick_Form.frx" - used automatically with .frm button
"SaveFormGuide.xls" - aforementioned spreadsheet

If you want to see it work right off the bat, save the SaveFormGuide.xls to your C:\ or change the FormGuideLocation constant to where yours is.

The _Click event for each button is located in the Class object clsQuickFormButton. Right now all I did was msgbox the 'Save To' directory so you can see it work, change this event accordingly.

Change the location of the FormGuideLocation constant in the form code, though the way this is retrieved will likely need to be changed to suit your users' needs. We can deal with that later, if you need help with it. If you already have a public variable holding it, then simply remove the Const line and change the reference to "FormGuideLocation" in the GetQuickFormData function to whatever your public variable is.

In the .xls file, you're probably going to want to hide Column A so your users dont change it (or just instruct them not to).
In a nutshell, here is what is happening:
-When the form is loaded, it looks at the .xls file and loads all the data from the first sheet.
-It then loops through the data for the button named PageXButtonY (you know what I mean) based on the PageCt and ButtonCt loops in UserForm_Initialize
-Once it finds that, assuming it is on the correct Tab name, it sets the button caption to what is listed in Column C.
-It then equates the commandbutton to a newly created instance of the class, and puts the save folder location into the class' vSaveFolder variable for use by the Click event in the class

I'm not sure if anything else in the thread above has not been mentioned, so I'll just wait to see if you have any more questions rather than read through it again and see what is unanswered.

I'm sure a lot of this is new to you, so play around with it and feel free to ask questions!

[JeffT]

Matt

This is amazing. It'll take me a while to understand it all I'll have a big play / tryout tommorow, Plus my New VBA book arrived today so I'm in heaven. Thanks so much for your help.

Thanks also replying to my other post. Once one problem's solved another always seems to crop up!! Still that's half the fun.

Jeff T

[JeffT]

Matt

This is really brilliant, how so little code can do so much. I'm still trying to understand everything about how it works but I'm gettin there.

I do have one problem. Everything has been working fine till tonight I started to tidy up all the comments and add some error catching dialog boxes. I've obviously deleted or changed something and can't figure out what.

From the Class module "clsQuickFormButton" it picks up the Path to the folder vSaveFolder. I want to pass this string to the 'Save' Module. I've got the following in the class Module:-

Path = vSaveFolder 'Both declared as Public As String

Call SaveSelectedEmails

As soon as the Module SaveSelectedEmails opens I've lost the String of the Path. I've tried amending lots of things and have probably made it worse. I guess it must be simple as it was working fine.

I've added the two modules in a Zip file in case this makes it easier to understand. I apologise in advance for the mess in the code.

Hope you can help as I was going public with this on Monday.

regards

Jeff

[mvidas]

Hi Jeff,

Since "Path" is already a public variable in your standard module, simply remove this line from the class:[vba]Public Path As String[/vba]Having it in there is making a Path property of the class.. removing that should get it to work.

Another thing you could do (and avoid having to use a public variable) is to remove both instances of the above line (in the class module and standard module), then changing your sub declaration line to be:[vba]Sub SaveSelectedEmails(Optional Path As String)[/vba]Then change the _Click event of the class to be:[vba]Private Sub vButton_Click()
Call SaveSelectedEmails(vSaveFolder)
End Sub[/vba]That will send the vSaveFolder to the SaveSelectedEmails subroutine. FYI - The example file made it very easy to understand

[JeffT]

Thanks Matt. All working again.

I didn't originally have :-

Public Path As String

in the class, just one of my clumsey attempts to get it working again.

I've another question, how do I call the SaveAs dialog box? amending the code I use in Excel doesn't seem to work & I can't find it in the help file, my new book or by searching vbax. I was going to use the .Display method and use the path in my already written SaveSelectedEmails Sub.

Thanks again for your help. I'm about to update your excel file to add a 'User page' rather than changing the code page directly.

Jeff

[mvidas]

There is no native GetOpenFilename vba function like excel or word has, but you can use some API calls to get the same thing:[vba]Option Explicit
Public Declare Function GetOpenFileNameB Lib "comdlg32.dll" Alias "GetOpenFileNameA" _
(pOpenfilename As OPENFILENAME) As Long
Public Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Sub GetOpenFilenameExample()
Dim vFile As String
vFile = GetOpenFileName()
If vFile <> "" Then MsgBox vFile
End Sub

Public Function GetOpenFileName(Optional ByVal vFileFilter As String, Optional ByVal _
vWindowTitle As String, Optional ByVal vInitialDir As String, Optional ByVal _
vInitialFileName As String) As String
Dim OFN As OPENFILENAME, retVal As Long
OFN.lStructSize = Len(OFN)
OFN.hwndOwner = 0
OFN.hInstance = 0
OFN.lpstrFile = IIf(vInitialDir = "", Space$(254), vInitialDir & Space$(254 - Len(vInitialDir)))
OFN.lpstrInitialDir = IIf(vWindowTitle = "", CurDir, vInitialDir)
OFN.lpstrTitle = IIf(vWindowTitle = "", "Select File", vWindowTitle)
OFN.lpstrFilter = IIf(vFileFilter = "", "All Files (*.*)" & Chr(0) & "*.*", _
Replace(vFileFilter, ",", Chr$(0)))
OFN.nMaxFile = 255
OFN.lpstrFileTitle = Space$(254)
OFN.nMaxFileTitle = 255
OFN.flags = 0
retVal = GetOpenFileNameB(OFN)
If retVal Then GetOpenFileName = Trim$(OFN.lpstrFile)
End Function[/vba]

For the flags (this is unnecessary), you can use the following constants. I usually use &H80000 for the flag to give it the new look with the desktop/mydoc/etc icons on the side.
[vba]Private Const OFN_READONLY = &H1
Private Const OFN_OVERWRITEPROMPT = &H2
Private Const OFN_HIDEREADONLY = &H4
Private Const OFN_NOCHANGEDIR = &H8
Private Const OFN_SHOWHELP = &H10
Private Const OFN_ENABLEHOOK = &H20
Private Const OFN_ENABLETEMPLATE = &H40
Private Const OFN_ENABLETEMPLATEHANDLE = &H80
Private Const OFN_NOVALIDATE = &H100
Private Const OFN_ALLOWMULTISELECT = &H200
Private Const OFN_EXTENSIONDIFFERENT = &H400
Private Const OFN_PATHMUSTEXIST = &H800
Private Const OFN_FILEMUSTEXIST = &H1000
Private Const OFN_CREATEPROMPT = &H2000
Private Const OFN_SHAREAWARE = &H4000
Private Const OFN_NOREADONLYRETURN = &H8000&
Private Const OFN_NOTESTFILECREATE = &H10000
Private Const OFN_NONETWORKBUTTON = &H20000
Private Const OFN_NOLONGNAMES = &H40000
Private Const OFN_EXPLORER = &H80000
Private Const OFN_NODEREFERENCELINKS = &H100000
Private Const OFN_LONGNAMES = &H200000[/vba]If you're going to put that into an existing module, make sure the Declare and Type parts above any of your own procedures.

Though I haven't redownloaded the form, I believe my code uses the first worksheet in the excel file. So if you're modifying the file, keep the code page first (I'm honestly not sure how the ADO code will work with hidden sheets, if you go that route, but I'd guess it would still work..?)