-
Frosty,
The code works perfectly, and the code is only for selecting single documents as the purpose of this is to produce a single quotation with little effort from the operator.
Do you think it's worth modifying the code as you've suggested, considering it works?
E: in fact, I'll give it a go now in the hope that it'll speed the process up.
-
I've ended up with this, which definitely isn't correct:
Code:
Sub Test() MsgBox fGetFilePath
End Sub
Public Function fGetFilePath() As String
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "My Filter", "*.dotm"
If .Show = 0 Then
Exit Function
End If
fGetFilePath = .SelectedItems(1)
End With
MailMergeFromExcel1
End Function
Sub MailMergeFromExcel1()
Dim sConnection As String
Dim strSourcePath As String
Dim fGetFilePath As String
Set fGetFilePath = strSourcePath
'your connection string, also more easily separated with line breaks and arguments
sConnection = "Provider=Microsoft.ACE.OLEDB.14.0;" & _
"User ID=Admin;" & _
"Data Source=strSourcePath;" & _
"Mode=Read;" & _
"Extended Properties=""HDR=YES;IMEX=1;"";" & _
"Jet OLEDB:System database="""";" & _
"Jet OLEDB:Registry Path="""";" & _
"Jet OLEDB:Engine Type=35;" & _
"Jet OLEDB:"
'using your conection string... with the parameter names and the passed values separated nicely
ActiveDocument.MailMerge.OpenDataSource _
Name:=strSourcePath, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
SQLStatement:="SELECT * FROM `MailMerge`", _
Connection:=sConnection, _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:=""
End Sub
-
Well, if it's working, it's because you don't need your connection string at all, because it is currently worthless. The fact that it takes 30 seconds is because you're probably finding your source doc from an alt method (30 seconds is probably the time for your bad connection string to give up and go another way).
also, have you tried selecting multiple files? You allow it in your code, someone will do it.
You don't need this loop at all-- you just happened to copy the relevant correct usage of .SelectedItems from wherever you got this code, rather than what you had before.
I know you just want it to work. But I'm more interested in you understanding why it works (or doesn't). Teach someone to fish, and all that
-
Haha fair enough! I'm about to clock off, so will give your instructions a better shot tomorrow.
I'll make sure I disable the multiple file selection - haven't tested that s yet. I did indeed just copy the correct stuff from some other code...caught me :p
Yes indeed, and I appreciate you teaching me. As I say, I'm about to clock off so I'll try tomorrow and report back :D
Thanks for your help, sir.
-
That was a good effort... here is what you're looking for. See if you can understand the main differences:
1. You call a function, so you just care about the result. You don't want to call your "main" function from within that function. Try out the TestFilePicker to see how the function works.
2. when you're trying to pass the value of a variable to a string... you need to make sure you're not passing the name of the variable, instead of the variable.... the difference between
"hello & strTemp & world"
and
"hello " & strTemp & " world"
3. You don't need to dim a variable with the same name as a function... you use the result of the function and plop it into a different variable.
[vba]
Public Sub TestFilePicker()
MsgBox fGetFilePath
End Sub
'function to return the path of a single selected file
Public Function fGetFilePath() As String
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
'.Filters.Add "My Filter", "*.dotm"
.Filters.Add "Excel Macro-Enabled Workbook", "*.xlsm"
If .Show = 0 Then
Exit Function
End If
fGetFilePath = .SelectedItems(1)
End With
End Function
Sub MailMergeFromExcel1()
Dim sConnection As String
Dim strSourcePath As String
strSourcePath = fGetFilePath
If strSourcePath = "" Then
Exit Sub
End If
'your connection string, also more easily separated with line breaks and arguments
sConnection = "Provider=Microsoft.ACE.OLEDB.14.0;" & _
"User ID=Admin;" & _
"Data Source=" & strSourcePath & ";" & _
"Mode=Read;" & _
"Extended Properties=""HDR=YES;IMEX=1;"";" & _
"Jet OLEDB:System database="""";" & _
"Jet OLEDB:Registry Path="""";" & _
"Jet OLEDB:Engine Type=35;" & _
"Jet OLEDB:"
'using your conection string... with the parameter names and the passed values separated nicely
ActiveDocument.MailMerge.OpenDataSource _
Name:=strSourcePath, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
SQLStatement:="SELECT * FROM `MailMerge`", _
Connection:=sConnection, _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:=""
End Sub
[/vba]
-
I still don't know if your connection string is correct in your environment, but this will be closer than before. I still have concerns about that portion-- but you should be able to select a file properly now.
-
Good morning, sir.
That works beautifully, now! As I understand it, there's a subtle difference in the way that you've attached the the file path to a variable. I didn't realise that you could retain that .SelectedItems(1) value outside of the with loop. But, am I right in saying that's only if it's defined as a function?
Are there clearly defined occasions on which you should use a function, as opposed to a sub? I don't understand the difference...
This works perfectly now, and is almost instantaneous, so thank you very very much!!
-
OK, oddly enough it works very quickly when standalone, but when combined with my spreadsheet is very slow. Would you mind taking a look at my excel code here, which I use to run the macro? Oddly enough, it becomes slow once Word has its part, like it was doing before you amended my code for me.
Code:
Sub MailMerge()
Dim Word As Word.Application
'Ensures workbook saved
MsgBox ("This workbook will be automatically saved before continuing.")
ThisWorkbook.Save
'Create new quotation for template
Set Word = CreateObject("Word.Application")
Word.Documents.Add Template:="Q:\AirMaster\AirMaster Quotation.dotm", NewTemplate:=True, DocumentType:=0
Word.Visible = True
With Word
.Visible = True
.ActiveDocument.SaveAs2 Filename:="Prod Quote_xxAMxHRV_ProjName " & Format(Date, "ddmmyyyy") & ".docm"
End With
Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.DisplayAlerts = True
End Sub
-
You've got four actions which can cause processing time: 2 saves. 1 open/new and a createobject. I assume you can't get around the saves and new (and speed of those obviously depends in size of document as well as network speed, since these appear to be on network locations).
CreateObject *always* creates a new instance of word. You're also not ending that instance of word in this code.
What you should do is try to use GetObjext first (to use an exising word process, if possible). And then if you do have to use CreateObject, make sure end your Word process, if appropriate (if you're just saving the document).
Have you checked your task manager? I'm guessing you have a lot of winword.exe processes in the processes tab. This will definitely slow down your computer over time too
-
I'm actually not thrilled with the structure of this... You have the code for the mail merge being triggered from an AutoNew within the Word template, but are calling the code from Excel. Why not just have all the code in your excel template? It would be easier to debug and troubleshoot, unless you also need the ability to open the word document from word and have it automatically generate.
If you can describe the actual desired workflow, there may be a better way to go about what you're doing.
-
Hi Frosty,
I don't think it's ideal either, but this was the best strategy that I could determine. Of course, I'm happy to take any advice you have about the method.
So the process is quite simple; we use an Excel spreadsheet to generate our quotations. Currently, the operators just copy an image of the quotation in excel, and drop it into a word document, which I think is unacceptable. What I want to be able to do is for them to complete the quotation in excel, as they currently do, and then just have them press a button, which will transfer the quotation into a word document.
I don't need to open the word document from word and have it automatically generate, but I do need to end up with a word document, if that makes any sense.
-
They click a button in excel... Magic happens, and they end up with an active/open Word document? Which they can then cut/paste the contents of into another word doc?
i guess I'd have to see the mail merge, but I'm not sure why they start in excel... Why not have them do whatever they want in excel, then click a button in word and have the contents end up in their clipboard? Or pasted where the cursor is?
keep describing as completely as you can until I get a real picture of the process, then I'm betting you'll end up with something better than just "use GetObject before trying CreateObject" (which is the best and only advice I can give you, at the moment)
-
Haha yes, magic indeed! They just want to churn figures out, which is fine by me.
I could have them copy and paste, but really want to automate the process because, frankly, people are incompetent, and take it upon themselves to modify things etc, and may not even have a rudimentary grasp of how copy & paste work - basically I need it to be as idiot proof as possible.
OK a better break down for you, though I'm not sure I can provide that much detail:
- User fills quotation using excel program. it's basically a process of selecting the unit they want, pressing a button, options come up, they select them
- press another button, and the information that they've just generated gets transferred (using VBA) from the input sheet to a summary sheet, which is a facsimile of the quotation
- repeat this process until the quotation is filled
- at this point they can amend the quotation as they wish, as long as they only touch certain cells. I've warned them off clicking on other things because I know that if they break a formula, they won't be able to fix it. I can only lock them down so far though
- user presses transfer to quote button, and then the mail merge happens
I hope that's helpful...
-
And what should happen after the mail merge? User is left with a word document to do something with? Or do they just need the word document saved to a folder but not left open? Do they need to do anything in word? Or do they immediately go back to excel to generate another quote?
-
Also, you can protect a sheet in excel, such that only certain cells are able to be edited. This is a good way to protect formulas
-
I know of the protection, I just haven't set it up because I like them to have some ability to edit. It's the end result that concerns me. If they break the spreadsheet then they'll just have to start again, hah! (translation: I can't be bothered to protect it properly)
After the mail merge they have the word document with the filled quotation. This will then probably be copied and pasted into a letter, the contents of which I'm unsure of. I expect that they will want to save that document, but they don't necessarily need it to open up at that time. They may do another quote immediately after, but they may open the quotation to copy it into this letter...
-
Fair enough. Well, I guess my only advice is try getobject first. Sample code is easy to find, or I can post it later. If you knew what your end users wanted to do after pressing the magic button, then you could skip showing word at all, and simply give an "output to where?" dialog, which your code could use for the later SaveAs process of the mail merge result.
I'd probably recommend pulling the code out of autonew of the word template, and putting it into excel (based on your excel code, you already have references to the word object model in your excel project, so this port will be relatively easy and be easier to debug later). Launching word and waiting for an auto macro to process could be slower than explicitly telling word to open a document and then run a mail merge.
I suspect you're doing this because you think you need to use ActiveDocument to trigger the mail merge. You don't. Use a document variable combined with documents.add to set the variable to the document you're going to open.
I'd also question why you're saving the result of the mail merge as a .docm file-- you don't need macros in that file, especially if they are likely to just cut and paste the contents. Use .docx instead.
Let us know how it goes.
-
Hi Frosty,
Thanks for that. I'll have a look around for some GetObject code, though would appreciate it if you wouldn't mind showing me some code? I would be happy for the document not to open, to be honest. I think it would be best if it just had this save to where dialog.
The autonew code bit...I only did that because I didn't know what to do with integration between excel and word. If there's a way to do it in excel, and you wouldn't mind telling me how to do it, then I'd be more than happy to do that. I can't say that I like the autonew code at all.
I'll try and get it saving as docx. I'm not clear why I set it to docm in the first place, hah.
Thanks
-
I just wrote this, but it's the basics... there are a lot of different ways to do this...
[VBA]
'encapsulates getting the word application, using GetObject first, and Create obect
Public Function fGetApp(Optional bCreated As Boolean) As Object
Dim oRet As Object
'attempt to get it, ignoring any error if it isn't launched
On Error Resume Next
Set oRet = GetObject(, "Word.Application")
'if we didn't get it, then attempt to create it, but reset error trapping
On Error GoTo 0
If oRet Is Nothing Then
Set oRet = CreateObject("Word.Application")
bCreated = True
End If
Set fGetApp = oRet
End Function
[/vba]
Usage in your existing code would be to do
Set Word = fGetApp
instead of
Set Word = CreateObject("Word.Application")
HOWEVER -- you should rename your variable "Word" to "appWord" ... otherwise you're using an object library ("Word") and a variable name for an application object ("Word") in the same project. Very bad practice.
From there, just try copy and pasting all of your Word code into your Excel project, and then try to run, work out kinks... post your results, and I'll help from there.
-
Frosty,
Thanks for the code, and for the advice! I can never get over how helpful people on forums like these are.
This is my code now, for the mail merge only (all in Excel):
Code:
'encapsulates getting the word application, using GetObject first, and Create obect
Public Function fGetApp(Optional bCreated As Boolean) As Object
Dim oRet As Object
'attempt to get it, ignoring any error if it isn't launched
On Error Resume Next
Set oRet = GetObject(, "Word.Application")
'if we didn't get it, then attempt to create it, but reset error trapping
On Error GoTo 0
If oRet Is Nothing Then
Set oRet = CreateObject("Word.Application")
bCreated = True
End If
Set fGetApp = oRet
End Function
Sub MailMerge()
Dim appWord As Word.Application
MSG1 = MsgBox("Do you want to continue with the mail merge?", vbYesNo, "Confirm")
If MSG1 = vbYes Then
ElseIf MSG1 = vbNo Then
Exit Sub
End If
'Ensures workbook saved
ThisWorkbook.Save
'Create new quotation for template
Set appWord = fGetApp
Word.Documents.Add Template:="Q:\AirMaster\AirMaster Quotation1.dotm", NewTemplate:=True, DocumentType:=0
Word.Visible = True
With appWord
.Visible = True
.ActiveDocument.SaveAs2 Filename:="Prod Quote_xxAMxHRV_ProjName " & Format(Date, "ddmmyyyy") & ".docx"
End With
Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.DisplayAlerts = True
End Sub
I deleted all the code from my word document, as you advised. However, that code above asks me correctly whether I want to merge, and then asks me from which document, but it then just opens the word document and doesn't actually carry out the mail merge.
Should I be using this code also:
Code:
Sub MailMergeFromExcel()
Dim sConnection As String
Dim strSourcePath As String
strSourcePath = fGetFilePath
If strSourcePath = "" Then
Exit Sub
End If
'your connection string, also more easily separated with line breaks and arguments
sConnection = "Provider=Microsoft.ACE.OLEDB.14.0;" & _
"User ID=Admin;" & _
"Data Source=" & strSourcePath & ";" & _
"Mode=Read;" & _
"Extended Properties=""HDR=YES;IMEX=1;"";" & _
"Jet OLEDB:System database="""";" & _
"Jet OLEDB:Registry Path="""";" & _
"Jet OLEDB:Engine Type=35;" & _
"Jet OLEDB:"
'using your conection string... with the parameter names and the passed values separated nicely
ActiveDocument.MailMerge.OpenDataSource _
Name:=strSourcePath, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
SQLStatement:="SELECT * FROM `MailMerge`", _
Connection:=sConnection, _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:=""
End Sub