asked on

How do I create a button on my Outlook form which creates a new Excel workbook using a template?

I would like to click the "New Form" button on my form and have it go to \\server\share, copy the Excel template to a new file with the user's name and then open that file so i can fill it in. I've attached the code that i have so far. When i try it, i get 'expected end of statement" on this line:

Dim xlApp As New Excel.Application

I thought this was because i did not have the references for excel set but I did that (I think, not sure how) and it's still giving the error.

Thanks!

'Button to create a new Account Request form
Sub cbuttonAttach_Click()
	' Insert hyperlink to the new form and change the subject on the task
	Dim AcctName
	Set AcctName = Item.UserProperties("Requestor/Caller")
	item.body="<\\hcterm\pub\" & AcctName & ".xls>" 
	item.subject="Process Account Request Form" 
	item.save
 
	Dim xlApp As New Excel.Application
	Dim xlWB As New Excel.Workbook
	Dim oSheet As Excel.Worksheet
	Set xlApp = CreateObject("Excel.Application")
	xlApp.Visible = True
 
	Dim sSourceFile' As String
	Dim sDestDir' As String
	sSourceFile = "\\HCTERM\PUB\ACCOUNTREQUESTFORMS\COMPUTERACCOUNTREQUESTFORM.XLT"
	sDestDir = "\\HCTERM\PUB\ACCOUNTREQUESTFORMS"
 
	'Check source file exists
	If Dir$(sSourceFile) = "" Then
	MsgBox "Source File Not Found"
	Else
	'Make dest dir If required
	If Dir(sDestDir, vbDirectory) = "" Then
	MkDir sDestDir
	End If
	'Copy the template file to new empty form with the user's name	
	FileCopy sSourceFile, sDestDir & "\" & AcctName & ".xls"
	End If
End Sub

Open in new window

hartwellcorp

ASKER

I think I'm making this too complicated. the first step should be simple, copy a file by clicking the button on the outlook form. when i try this, it says "type mismatch: FileCopy".

Sub cbuttonAttach_Click()
Set fs = CreateObject("Scripting.FileSystemObject")
sSourceFile = "\\HCTERM\PUB\ACCOUNTREQUESTFORMS\COMPUTERACCOUNTREQUESTFORM.XLT"
sDestDir = "\\HCTERM\PUB\ACCOUNTREQUESTFORMS"
FileCopy sSourceFile, sDestDir
End Sub

Patrick Matthews

Hello hartwellcorp,

Is this an Outlook form, or a VBA UserForm? If the former, those use VBScript, and in VBScript
you cannot Dim a variable "as" any particular type. Instead you would use:

Dim xlApp
Set xlApp = CreateObject("Excel.Application")

Regards,

Patrick

hartwellcorp

ASKER

okay, i fixed that but now i get: type mismatch filecopy

Patrick Matthews

Replace:

FileCopy sSourceFile, sDestDir

with:

Set fil = fs.GetFile(sSourceFile)
fil.Move sDestDir & "\" & fil.Name

TWBit

Sorry I couldn't help much before, but I took some time to look into it. This should do everything except write the file as the user's name. I tested it locally with a local file and local folder. Not sure if UNC will thow it off. Let me look into that a bit more, or maybe someone else knows how to get the user.

Sub CommandButton1_Click()
 
	Dim objExcel
	Dim objWorkbook
 	Dim objFSO
	Dim sSourceFile
	Dim sDestDir
 
	sSourceFile = "\\HCTERM\PUB\ACCOUNTREQUESTFORMS\COMPUTERACCOUNTREQUESTFORM.XLT"
	sDestDir = "\\HCTERM\PUB\ACCOUNTREQUESTFORMS" 
 
	Set objFSO = CreateObject("Scripting.FileSystemObject")
 
	'Check source file exists
	If not objFSO.fileexists(sSourceFile) Then
		MsgBox "Source File Not Found"
	Else
		'Make dest dir If required
		If not objFSO.FolderExists(sDestDir) Then
			objFSO.CreateFolder sDestDir
		End If
 
		'Copy the template file to new empty form with the user's name	
		objFSO.CopyFile sSourceFile, sDestDir & "\Test.xls"
	End if
 
	Set objExcel = CreateObject("Excel.Application")
	objExcel.Workbooks.Open sDestDir & "\Test.xls"
	objExcel.Visible = true
 
End Sub

Open in new window

hartwellcorp

ASKER

that's excellent! However, the file that opens in Excel says test1.xls for some reason. the file test.xls is created as expected, but then the file that opens is test1 and if i change it and click save, it brings up the save as dialog as though it was not already saved in that location.

TWBit

I got that too because it is a Template (XLT) and not an XLS. But when using XLS it is fine.

hartwellcorp

ASKER

awesome. it's working. do you know how to prompt for a name before copying the file and use that as the file name?

TWBit

This should be working now. Just remember that since the source file is an XLT and you are saving it as an XLS, the file type is still a Template and when saving, you will see the Save As dialog box because you are now saving it as an XLS. To avoid this, keep it as an XLT during the copy, or change the source file to an XLS.

Sub CommandButton1_Click()
 
	Dim objExcel
	Dim objWorkbook
 	Dim objFSO
	Dim sSourceFile
	Dim sDestDir
 
	'Get user name that is signed on. Might not work if W2k user not member of Administrator group
	strComputer = "."
	Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
	Set colItems = objWMIService.ExecQuery("Select * From Win32_ComputerSystem")
	For Each objItem in colItems
	    arrName = Split(objItem.UserName, "\")
	Next
 
	'Set source file and destination folder
	sSourceFile = "\\HCTERM\PUB\ACCOUNTREQUESTFORMS\COMPUTERACCOUNTREQUESTFORM.XLT"
	sDestDir = "\\HCTERM\PUB\ACCOUNTREQUESTFORMS"
 
	'Set up FileScriptingObject
	Set objFSO = CreateObject("Scripting.FileSystemObject")
 
	'Check source file exists
	If not objFSO.fileexists(sSourceFile) Then
		MsgBox "Source File Not Found"
	Else
		'Make dest dir If required
		If not objFSO.FolderExists(sDestDir) Then
			objFSO.CreateFolder sDestDir
		End If
 
		'Copy the template file to new empty form with the user's name	
		objFSO.CopyFile sSourceFile, sDestDir & "\" & arrName(1) & ".XLS"
	End if
 
	'Open Excel with the new file
	Set objExcel = CreateObject("Excel.Application")
	objExcel.Workbooks.Open sDestDir & "\" & arrName(1) & ".XLS"
	objExcel.Visible = true
 
End Sub

Open in new window

TWBit

If you wanted to prompt the user for a filename, add the following two line:

dim fname
fname=InputBox("Enter a name (without extension) for the new file:")

Then change the arrName(1) occurances to fname.

But that could lead to some errors - you never know what they will type in.

hartwellcorp

ASKER

that almost works. when i am prompted, i type the name "test" and i get an error:

object required : [string: "test"]

TWBit

On what line? Please re-attach your latest code.

hartwellcorp

ASKER

It doesnt say what line, but i get that error when i enter "test" at the name prompt.

Sub cbuttonAttach_Click()
 	' Insert hyperlink to the new form and change the subject on the task
	Dim AcctName
	Dim objExcel
	Dim objWorkbook
 	Dim objFSO
	Dim sSourceFile
	Dim sDestDir
 
	Set AcctName = InputBox("Enter a name (without extension) for the new file:") 
'	Set AcctName = Item.UserProperties("Requestor/Caller")
	Set objFSO = CreateObject("Scripting.FileSystemObject")
 	sSourceFile = "\\HCTERM\PUB\ACCOUNTREQUESTFORMS\COMPUTERACCOUNTREQUESTFORM.XLT"
	sDestDir = "\\HCTERM\PUB\ACCOUNTREQUESTFORMS"
	
	'Check source file exists
	If Not objFSO.fileexists(sSourceFile) Then
		MsgBox "The template file was not found at:  " & sSourceFile
	Else
	End If
	'Copy the template file to new empty form with the user's name	
	objFSO.CopyFile sSourceFile, sDestDir & "\" & AcctName & ".xls"
 	item.body=" Please fill out this Account Request Form for " & AcctName & ": <\\hcterm\pub\accountrequestforms\" & AcctName & ".xls>" & "... Once you're done filling out the form, reply to this message so we can get started on it." 
	item.subject="Process Account Request Form" 
	item.save
	End If	
End Sub

Open in new window

TWBit

There is no Set statement.

hartwellcorp

ASKER

Do you mean that the Set statement is missing from my code or that i should not have put a Set statement on the prompt line?

Set AcctName = InputBox("Enter a name (without extension) for the new file:")

TWBit

You shouldn't have put it in on line 10. I didn't have it in the code I asked you to use.

hartwellcorp

ASKER

okay, here's my code again. i removed the Set statement on the prompt line and now it works, thanks for that! I also changed the code so that it's copying an XLS file instead of XLT file but it still brings up the save as dialog when I'm closing it.

Sub cbuttonAttach_Click()
 	' Insert hyperlink to the new form and change the subject on the task
	Dim AcctName
	Dim objExcel
	Dim objWorkbook
 	Dim objFSO
	Dim sSourceFile
	Dim sDestDir
 
	AcctName = InputBox("Enter a name (without extension) for the new file:") 
'	Set AcctName = Item.UserProperties("Requestor/Caller")
	Set objFSO = CreateObject("Scripting.FileSystemObject")
 	sSourceFile = "\\HCTERM\PUB\ACCOUNTREQUESTFORMS\COMPUTERACCOUNTREQUESTFORM.XLS"
	sDestDir = "\\HCTERM\PUB\ACCOUNTREQUESTFORMS"
	
	'Check source file exists
	If Not objFSO.fileexists(sSourceFile) Then
		MsgBox "The template file was not found at:  " & sSourceFile
	Else
	End If
	'Copy the template file to new empty form with the user's name	
	objFSO.CopyFile sSourceFile, sDestDir & "\" & AcctName & ".xls"
 	item.body=" Please fill out this Account Request Form for " & AcctName & ": <\\hcterm\pub\accountrequestforms\" & AcctName & ".xls>" & "... Once you're done filling out the form, reply to this message so we can get started on it." 
	item.subject="Process Account Request Form" 
	item.save
	'Open Excel with the new file
	Set objExcel = CreateObject("Excel.Application")
	objExcel.Workbooks.Open sDestDir & "\" & AcctName & ".XLS"
	objExcel.Visible = True
 
End Sub

Open in new window

TWBit

Did you re-save the original SourceFile as an XLS, or just change the extension? As I said, changing the extension doesn't change the file type. Saving a Template type workbook prompts you to SaveAs.

hartwellcorp

ASKER

You got it. Sorry, I missed that previously. Thanks for all your help. I just realized something though. if i name the file by the user's name, it will get overwritten the next time that name is used. can i append the file name with a serial number, like todays date or something like that?

Sub cbuttonAttach_Click()
 	' Insert hyperlink to the new form and change the subject on the task
	Dim AcctName
	Dim objExcel
	Dim objWorkbook
 	Dim objFSO
	Dim sSourceFile
	Dim sDestDir
 
	AcctName = InputBox("Enter the user's name") 
'	Set AcctName = Item.UserProperties("Requestor/Caller")
	Set objFSO = CreateObject("Scripting.FileSystemObject")
 	sSourceFile = "\\HCTERM\PUB\ACCOUNTREQUESTFORMS\COMPUTERACCOUNTREQUESTFORM.XLS"
	sDestDir = "\\HCTERM\PUB\ACCOUNTREQUESTFORMS"
	
	'Check source file exists
	If Not objFSO.fileexists(sSourceFile) Then
		MsgBox "The template file was not found at:  " & sSourceFile
	Else
	End If
	'Copy the template file to new empty form with the user's name	
	objFSO.CopyFile sSourceFile, sDestDir & "\" & AcctName & ".xls"
 	item.body=" Please fill out this Account Request Form for " & AcctName & ": <\\hcterm\pub\accountrequestforms\" & AcctName & ".xls>" & "... Once you're done filling out the form, reply to this message so we can get started on it." 
	item.subject="Process Account Request Form" 
	item.save
	'Open Excel with the new file
	Set objExcel = CreateObject("Excel.Application")
	objExcel.Workbooks.Open sDestDir & "\" & AcctName & ".XLS"
	objExcel.Visible = True
 
End Sub

Open in new window

hartwellcorp

ASKER

also, when it prompts me for a name and i hit cancel, it errors out. i think i need to tell it what to do when nothing is entered right?

ASKER CERTIFIED SOLUTION

TWBit

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hartwellcorp

ASKER

That's perfect, thank you! I'll accept your last comment as the solution, thanks again!

TWBit

If want to learn more about vbscript, search within EE or look at http://www.w3schools.com/vbscript/