Want to add couple things here: 1) When your table header name contains a space, for example Account Name, because a data source field cannot have a space, VBA will automatically replace space with an underscore. So when you type your DataFields' name, instead of DataFields("Account Name").value, you want to use DataFields("Account_Name").value. 2) I will be working on a Mail Merge Utility with a UI with to eliminate the need to write VBA code. Also looking adding few useful features to enhance the functionality. 3) and try not use special characters in your file name. And lastly, thank you for all of your support and views. I appreciate it.
@nageshnagasubramanian1185
2 жыл бұрын
Jie Jenn, this was very useful. Can you tell how to few lines of code to combine 2 or more pdf files together in this script of yours?
@imranism
5 жыл бұрын
This is amazing, It is a generic tool which can save each record from Excel in a customized pdf or word template.
@jynxie88
2 жыл бұрын
You are a lifesaver! There were some moments where I hit a roadblock, but meddled here and there and it saved my team's time, a LOT of it! Thank you so much sir.
@jiejenn
2 жыл бұрын
Glad my video helped. Cheers.
@Ryangaming862
4 жыл бұрын
I cannot thank enough for this tutorial, very useful and extremely helpful for someone like me. hours of work in minutes. thanks once again for sharing your knowledge :)
@PURVESHPRAJAPATI-tm7ds
2 жыл бұрын
Thanks. Jin. It is a very useful and timesaving clerical job which is widely used in corporates and other sectors. One my relative is in the HR department and he used to print and save the Increment / Bonus Letters individually by each employee and send by email to them. You can imagine how painful and time-consuming job for more than 500 employees. I shared this example and he is very happy to save his time by such Automation. Thanks Jin Again for your efforts.
@RESULTCELLSR
Жыл бұрын
Thank You for a clean crisp code! It has provided me the solution in the first go!!!
@dariotedesco8652
6 ай бұрын
thanks for your video. Unfortunately I have a problem when I run the code; a table will appear to confirm data source, I click on Show all and retreive my data source file choosing "Excel files ODBC (*xlsx), then I have the SelectTable window, I click on Options, Show, and I tick everything. I select the sheet where the specified column is, the window closes and then nothing happens. Do I maybe do something wrong? Thanks
@smithlithium06
4 жыл бұрын
You have saved me a lot of time! Thank you!
@Xaphoeous
3 жыл бұрын
THANK YOU! This video, and especially the link to the VBA code, saved me hours and hours of work!
@9716367288
3 жыл бұрын
Omg..... Omg... Finaly i got vba code for mail merge for docx ans save file in docx... Thnk lost.... It very help full me.... 🙏🙏🙏
@joanaamorfajardo3056
2 жыл бұрын
This is very helpful. It helped me accomplish my task! Thank you so much!
@ceciliaknights8306
3 жыл бұрын
Dude!!! This worked so well!! You saved me hours of work, thank you so so much!
@mauriciocovarrubias8255
4 жыл бұрын
Thanks a lot for sharing this wonderful tool. As a public server I need to send legal sheets with same info to different people and it works great! I dont have great knowledge of VBA but you made it so easy. At first I had troubles but with patiente I made it work. Greetings fro México.
@ihzaraki
3 жыл бұрын
Thank you so much, it's so easy to follow your step
@soaresian
4 жыл бұрын
Thanks this is very helpful and simplified compared to all the complex threads out there.
@rajivraj6441
Жыл бұрын
Thanks for the information, what if I want to save only 1 or 2 letters from the table and not the rest. How can this be done.
@josephaggrey9972
4 жыл бұрын
This video is fantastic. It actually addressed what I was looking for. Great job. well done Jie.
@jiejenn
4 жыл бұрын
Glad my video helped!
@ramanandkashyap5167
4 жыл бұрын
Woah!! This is awesome Jie. Thank you very much. Working perfectly fine.
@ghsskomal2458
Жыл бұрын
dear sir thank you for your tutorial
@viveknakarmi3411
3 жыл бұрын
You are a legend!!!Thanks for this.
@japurba
4 жыл бұрын
This is great!! Thank you brother!
@mcclusky21
5 жыл бұрын
This was a lifesaver thank you so much for this workaround!!! I had to watch a few times, but very easy to follow. Had to remove the SQLStatement though.
@YippeeKanYe
4 жыл бұрын
Same here, thank you Sharrod
@f.p.186
4 жыл бұрын
@@YippeeKanYe i have a problem with that line as well, did you delete the sql statement after the comma or the whole line? (. Opendatasource name=....)
@YippeeKanYe
4 жыл бұрын
@@f.p.186 I deleted the whole line i.e. .Opendata...
@YippeeKanYe
4 жыл бұрын
Hi Jie, Thank you for this video, very helpful. I have a question about formatting. The data that appears in the newly created word document does not have the proper formatting, i.e. Currency/Accounting/Percentage formatting in the excel file shows up as a general number in word. 6% would show up as 6.0 etc. Any ideas on how to fix this?
@DarrylDevar
3 жыл бұрын
Thank you, saved me a lot of time. Really appreciate this, nobody has a tutorial as detailed as yours. I also get a blank page at the end of every pdf with my first-page Header and Footer page included.
@jiejenn
3 жыл бұрын
Check your document margin, could be there are some spaces extended outside the page.
@DarrylDevar
3 жыл бұрын
@@jiejenn I still have the 1st page with my letterheads appearing on a blank page at the end, is there anyway of fixing this. Or printing only a certain number of pages so that i exclude the last empty page that appears.
@DarrylDevar
3 жыл бұрын
Thank you, it was a spacing issue
@stuartsharp8071
4 жыл бұрын
Good tutorial, although not sure what I have done wrong. Managed to take a 49 page (49 individual letters) mail merged document and end up with 49 versions of the same each with the 49 pages each, instead of 49 documents with a letter each.
@mauriciocovarrubias8255
4 жыл бұрын
Happened the same to me. Any solution?
@rachitnirmal
4 жыл бұрын
You are savior! Thank you so much! This was smooth like anything!
@JOSHI-l1m
4 жыл бұрын
mind blowing, superb knowledge you holding and speed of writing macros
@nelsoncardona4910
3 жыл бұрын
Thanks man!
@eduardostewart412
4 жыл бұрын
Thank you so much. Initially had blank pages, had to import and run the module from within the project/document section, not the normal section in the VBA editor tree window on the left screen (don't know proper name right now).
@johnvanco2959
3 жыл бұрын
I have the same issue, what do you mean by running the module from within the project/document section ? Thanks for your help !
@eduardostewart412
3 жыл бұрын
@@johnvanco2959 in the vba window tree on the left. It will list a generic version and a line for the document you have open that you want to run it in. Click on the actual doc and then run/press play
@csm191
3 жыл бұрын
Thanks for posting your solution!
@nguyenthanhmy1702
4 жыл бұрын
From Vietnam, thanks for sharing! It helps a lot
@mistysword
5 жыл бұрын
Thanks so much for all the effort! This saved my day
@MalinaC
3 жыл бұрын
Awesome! Thank you!
@suescherer5491
4 жыл бұрын
Thanks so much for the tutorial. Very clear. Can you provide the macro text pls.?
@helifox
2 жыл бұрын
Thank you very much, you save a lot of my time !!!
@jiejenn
2 жыл бұрын
Glad my video helped.
@andrewmarcou6164
2 жыл бұрын
Hi, Awesome. Quick question, if my word document has a table of contents and I wish to update it is there VBA code to do so for each file?
@simonsylow1365
2 жыл бұрын
Hello mate thank you so much for the toturtial! everything works however the documents crated seems to be all empty, can you help me with this problem?
@jiejenn
2 жыл бұрын
Replied your email.
@melissaaguirre3193
2 жыл бұрын
THANK YOU SO MUUUUUUUUUUCHHH FOR THIS!!!!
@jiejenn
2 жыл бұрын
Glad my video helped.
@breodjebi
3 жыл бұрын
This is awesome. Thank you very much. Working perfectly fine
@devenderrohilla
4 жыл бұрын
Very nice...great way to explain and fully functional...thank you
@JTPedersen
4 жыл бұрын
Greatly appreciate your sharing this. Struggled to find an effective solution elsewhere. Been awhile since I coded last. Your delivery was excellent and easy to replicate. Only real hiccup I had was the SQL code. SOURCE_FILE_PATH was correct (I set a Watch) yet stepping through the line, I'd consistently get the full filename path truncated (e.g. c:\1\2\3\file.xlsx, came back as c:\1\2\file.xlsx). Solution: Replace database$ with the name of the table containing the data in the xlsx. Worked beautifully. Thanks!
@jiejenn
4 жыл бұрын
Glad my video helped.
@brianogutu723
4 жыл бұрын
Worked like a charm!Thanks alot
@TOMRYANLUKE
3 жыл бұрын
THANK YOU FOR THIS!
@maheshbabu2523
5 жыл бұрын
Thanks so much Jie for all the effort.
@undrawingend
2 жыл бұрын
Is it possible to have this make individual folders for each database reference and then have the files save in them locations ? Just out of interest as used this for batch documents in work and it’s been great however it requires moving said file into a folder for each individual letter any advice?
@jiejenn
2 жыл бұрын
Yeah, it is possible.
@curlcurllocal
3 жыл бұрын
This is fantastic thanks for sharing
@chrislopez1414
4 жыл бұрын
Genius!!! You are amazing !!!!!
@ogechinnadozie7284
4 жыл бұрын
Thank you Jie Genn, the video explains all I'm looking for. I'm practically new with VBA Codes and I'm having a hard time understanding how you went about the "destination" which bears the mail merge automation. Could you do a little explanation on that? Thanks.
@jiejenn
4 жыл бұрын
Not following your question. What do you mean by "destination"? Do you mean save your files in a different folder?
@ogechinnadozie7284
4 жыл бұрын
I meant the first line " folder_saved as string" the folder named "destination" I would like to know it's content and how to go about it? I know the second file is the Excel sheet containing the data. Can I work with an "xslm" file in this case
@lucasm.s.6118
4 жыл бұрын
Great tutorial. Thank you!!
@selfreflections3066
4 жыл бұрын
Thank You. Lots of blessings for you... This is so powerful.
@julianspb6329
5 жыл бұрын
Hello, Thank you very much for the video. When i try to run the code, a tab saying "select table" show ups and when i click okay it say that "Run-time error '5922' unable to find data source". Do you know what i am doing wrong? Thanks
@jiejenn
5 жыл бұрын
Check your code to see if there are any typos.
@parniunplugged7023
4 жыл бұрын
@@jiejenn I also got same error. Its something to do with SQL Statement.
@KSSiddhartha
3 жыл бұрын
Thank you very much!
@missyg3183
5 жыл бұрын
hi, thank you for this, i have a question - i keep getting a debugger error on the open source data line. i see your folder is in C drive, i have to use a W drive - is that the issue? also i get a pop up asking me to select a workbook. any suggestions would be really helpful. thank you!
@jiejenn
5 жыл бұрын
I don't think drive location should matter, but your error to me seems like Word cannot find the file. The pop up is caused by Word doesn't have the source file linked. If you want, you can send me your VBA code to youtube@learndataanalysis.org and I can take a look at it.
@StuartHough
5 жыл бұрын
same issue here
@StuartHough
5 жыл бұрын
I fixed this by unselecting the 'preview results' option in word. It then worked fine!
@yogeshdesai3049
4 жыл бұрын
Thanks its very helpful kindly share the another vdo for all records save in one pdf format
@ashishyadavhr
4 жыл бұрын
Thanks a lot for sharing this knowledge with us it was very useful. Please advise if we can separate individual documents from a mail merged consolidated file as well? can we use with the same method by making the source as that word file?
@jiejenn
4 жыл бұрын
Well you can, but depends on the document content organization. Are the pages number consistent for each file? or the page number varies.
@ngrant8822
4 жыл бұрын
Great video Jie Jenn, I have been struggling with the SaveAs2 comment. My Datafield has a '" | " character in the value, I want to save the file name as the 10 characters before the " | " character, can you help me?
@jiejenn
4 жыл бұрын
| is one of the characters cannot be used to create a file or folder name. You will have to use other characters that is valid.
@flagshipfleet
4 жыл бұрын
So nice. Thank you very much
@nelubadalan22
4 жыл бұрын
Great job !
@R0b3
3 жыл бұрын
Works great form me!!! Thanks a lot for your script. ;-)
@tameraziz2104
3 жыл бұрын
Perfect, thank you so much..
@robertanderson9915
3 жыл бұрын
After the OpenDataSource instruction, I get a pop-up which allows me to select the whole worksheet, but then .DataSource.RecordCount = -1. Any ideas why it is not finding my records? Mail merge works normally so I'm sure the source data is ok.
@kazisazzad9257
4 жыл бұрын
Hi, Many thanks for sharing this tutorial. It's amazing and so helpful. It's saving both in word and pdf format; is there any way to avoid saving in word format- please advise.
@jiejenn
4 жыл бұрын
To avoid saving the word file, I think you can just comment out the line that saves the word file.
@dharmeshy3622
4 жыл бұрын
Thank you, its very useful for me
@LisaLevy-fk3cv
4 ай бұрын
Thank you this is great, but I'm having a problem I hope you can help me with, when I try to run it it gives me a runtime error 4198 and points to the .opendatasource line. And suggestions?
@jiejenn
4 ай бұрын
The error is most likely due to invalid file path is provided.
@TatjanaKovalevica
4 жыл бұрын
thank you, very useful!
@m.khoerudin3816
4 жыл бұрын
thanks, very usefull, but how if want to save with two DataFields, example, name and date
@zarinamustapha8604
4 жыл бұрын
Hi, Thanks for the tutorial. It doesn't work when I followed it on a Mac, quite possibly something is different between the two platforms. The macro ran, no error, but no files have been created. I'm not an expert in VB, though
@jiejenn
4 жыл бұрын
Hi Zarina, I think the object references are a little bit different on Mac. Unfortunately I am not a Mac user nor do I own a Mac PC. Your best bet is look for someone who has a Windows PC and try the mail merge and see if it works.
@romellamarie5608
4 жыл бұрын
@@jiejenn i have the same problem with Zarina, and I'm running Windows. help?
@romellamarie5608
4 жыл бұрын
i.m using word 2013 .. is there a different?
@danwilson4860
3 жыл бұрын
I have had the same difficulties. I was able to get the script to MOSTLY work though. Line 19: The 1 to TotalRecord doesn't seem to work, but if you replace TotalRecord with the actual total number of records then it works. Line 33: I cannot get it to save PDFs. If I comment out this line, the script will create unique word files for every merge record, but my Mac chokes on wdExportFormatPDF
@condee289
4 жыл бұрын
Many thanks, it works.!!!
@capt336
5 жыл бұрын
Thank you for posting this video. The .Destination = WdSendtoNewDocument seems to make a long word file with every generated document from the Mail Merge put together. Any way how to change it so it just makes the one custom document for each line of source data?
@jiejenn
5 жыл бұрын
Not sure if it follow. Can you be more specific.
@tysutton9160
4 жыл бұрын
Hi Jie, The documents are created with the files saved with the correct names however the document itself is blank. Thoughts?
@eduardostewart412
4 жыл бұрын
same
@tysutton1417
4 жыл бұрын
I tried two things and got it to work, first I saved my “template” as a .docm instead of .docx and I moved my source excel and the .docm into the same folder. Changed paths in the Macro of course to go to the new location and it worked. Thanks again Jie Jenn, big fan of your channel!
@eduardostewart412
4 жыл бұрын
@@tysutton1417 i got it to work tonight. The module had to be imported/ran from the within the document/project not the normal section in the VBA editor tree window on the left (don't know the proper name right now).
@MrWarrentierney
4 жыл бұрын
Amazing! You are my hero!
@Master-dh3xl
3 жыл бұрын
Well done! but I have question with the sql statement if want to retrieve data from row 2 to row 10 how can I do with it? .OpenDataSource Name:=SOURCE_FILE_PATH, sqlstatement:="SELECT * FROM [databases$]" Thanks you.
@niloofarmaleki3573
4 жыл бұрын
Hello, Many thanks for the video. This was great! My question is In case I want this to be done only for a group of the rows, for example lets say on a column I have category 1,2 and 3, and I want to do this for category 3 only, how should I modify the VBA to apply the filter?
@jiejenn
4 жыл бұрын
You can actually do that directly in a SQL statement. From the OpenDataSource statement, you can include what we called WHERE clause to include criteria. For example, assuming your column name is "Product Category" and you want to filter the dataset by label "category 1", you would write (noticing the extract double quotation): .OpenDataSource Name:=SOURCE_FILE_PATH, sqlstatement:="SELECT * FROM [$] WHERE [Product Category] IN (""Category 1"")"
@niloofarmaleki3573
4 жыл бұрын
@@jiejenn Many Thanks But actually when I add this where clause, nothing happens... I sent you a video on your email, would you please kindly check?
@ScottAlexander68
3 жыл бұрын
This is great and it works perfectly. I am wanting to expand on it a bit by selecting the records from the Excel file that have not been "processed" yet. I have that figured out, but I am trying to figure out how to update the excel field to show it as processed so the next time I run the macro, it doesn't pick that record. For example, I want to change the Processed field from No to Yes. Hopefully you understand what I am trying to do.
@jiejenn
3 жыл бұрын
If you want to process rows based on a condition(s), after the loop, you can insert an IF statement to express your criteria. For example, if your field is called "Processed", the code would look something like IF .fieldname("Processed").value = "Yes" THEN [mail merge code block] END
@ScottAlexander68
3 жыл бұрын
@@jiejenn I figured that part out after some trial and error. But what I want it to do is after it finds the records and creates the new documents, update the row in Excel so it does not have a duplicate made. For example, say I have 10 rows and I have a "Processed" column. 4 of the 10 rows have No in this column and 6 have Yes. The query finds the 4 rows that were not processed, your script creates the PDF, but I want those rows to change the No to Yes so next time I run the script, these rows will not be created again. Hope that makes sense.
@jiejenn
3 жыл бұрын
@@ScottAlexander68 I think I understood your question fully now. If you want to update cells in Excel, it involves of adding Excel reference in Word VBA, which is a bit more advanced if you do not have experience in writing VBA. I will take this as an idea and to make a part 2 video in the future.
@ScottAlexander68
3 жыл бұрын
@@jiejenn Don't have much experience at all. That would be great and I am going to do some reading too lol.
@viveksinghpratap
2 жыл бұрын
Hi Jie. This really helped me a lot while I used for some letters last year and I’m really thankful for that. However, while I’m trying the same code on a new file: 1. I’m getting separate files opened along with my letter format without any file name. 2. Both word and pdf files saved in my folder read as “word found licked fields during update. Word can’t update locked fields “ Kindly help. Thanks in advance
@fongkaichian9073
Жыл бұрын
Facing a problem that once merge and the doc / PDF was empty but the PFD file name was correct, any idea?
@jiejenn
Жыл бұрын
Makes sure your PDF content is extractable. If it is an image or encrypted, then it may not work.
@kethanchordia
4 жыл бұрын
Amazing will save me a lot of time. Thank you. Subscribed for life :-)
@programming304
2 жыл бұрын
Hi. How do I get the client name to appear at the beginning of the Word document name once it is saved? The code right now always makes the client name appear at the end of the document name. Thanks.
@jiejenn
2 жыл бұрын
Once solution is to have a separate column containing the file name, and use that column as the file name instead.
@feddyblade9908
4 жыл бұрын
Is it possible to make pdf file output encrypted. Usually i do manual save as page 1, input password xxx, next save as page 2 input password yyy, and so on....
@diratanaya4889
4 жыл бұрын
Best trick, thanks very much
@susannescherer7182
2 жыл бұрын
Hello. I have used this macro in the past with good success...a timesaver. Acrobat PDF Maker plug in with Mail merge now does create separate pdf files but still can't name them uniquely so this macro still remains very useful. I however now have an issue that the .pdf files being generated are all blank contents..the name of the file matches what I have in my excel mailmerge database so that source file appears to be set up correctly but the mail merge functionality itself doesn't seem to be running properly. any insight? I am using Office 365... is there perhaps a macro update to use on Office 365?
@jiejenn
2 жыл бұрын
Hard to tell without looking at your files. One common cause is due to margin is not set correctly.
@susannescherer7182
2 жыл бұрын
@@jiejenn thanks for the reply Jie. Can you clarity how to trouble shoot a margin issue? what should I be looking for? I am unable to send you the file as it contains confidential info. I did however need to really reduce both side, top and bottom margins to maximize the space for my document. thanks in advance
@npaverico
4 жыл бұрын
What a great tutorial!! I have one question. I need to put a repeated name in the file name. For example "Approval - Project" . The word Approval has to be in all my file names. How do I include the in the macros?
@jiejenn
4 жыл бұрын
Within the TargetDoc.SaveAs2 you can concat any addition text you want to include. For example: TargetDoc.SaveAs2 FOLDER_SAVED & .DataSource.DataFields("Client_Name").value & " Approval - Project" & ".docx"
@hestertencate2980
2 жыл бұрын
@@jiejenn can you also do any additional text infront of the name? what would be the code? I've been trying but no idea...
@blogieblogger
5 жыл бұрын
Hi JJ, I getting a 'Microsoft Data Link Error' which reads as 'Test connection failed because of an error in initializing provider. Cannot start your application. The workgroup information file is missing or opened exclusively by another user'. while I have not opened the excel file nor it is being used/accessed by anyother program. Please help.
@samanthawakefield8831
2 жыл бұрын
Hi. I’ve tried this and when I press F5 it runs like it’s working but it’s not opening a new document? Can you help? Thank you. The code I used is exactly the same as yours.
@jiejenn
2 жыл бұрын
Hi Samantha, I charge a fee of $10 to troubleshoot. If you are interested please let me know.
@tomdenapoli5313
3 жыл бұрын
Will this work on a Mac (Office 16). So far, no love.
@kamiwasielewski1119
2 жыл бұрын
Hi, I am able to produce word and PDF but both file are empty ) no words insite. Can someone help.
@catlovers776
3 жыл бұрын
Hello sir this is super amazing, when I run the macro I’m getting run time error 5922. The error is showing up in line 10 where we have the open data source. Would you mind helping me out
@abdulwahedcatran8349
5 жыл бұрын
Thank you so much please how to add a picture to the file
@jiejenn
5 жыл бұрын
I'm not following your question.
@abdulwahedcatran8349
5 жыл бұрын
@@jiejenn Let's say we have a logo folder for each client and we want to put this image on the customer's message
@beatricezhang7630
5 жыл бұрын
Hi Jie, thank you so much for posting the tutorial. It is definitely what I need recently. I am new to VBA, and I tried to follow your video to make my mail merge works. There is an error "438" in the line of ".OpenDataSource Name:=source_file_path, SQLStatement:="SELECT * FROM [XXX$]", as i only change the sheet name you created to mine. Can you help me on this? Thanks!
@demodrift1
5 жыл бұрын
I have the same issue. Please help us!
@mcclusky21
5 жыл бұрын
I had the same issue. Remove SQLStatement:="Select * From [Database$] and it should work
@dharmeshy3622
4 жыл бұрын
instead of -database- statement use the sheet name of your data
@thomaskenny6393
4 жыл бұрын
The code gets to the "TargetDoc.Close False" line and then stops to ask me if I would like to save the document. I have to press yes, otherwise it does not continue. Do you know how to automate the "yes" clicking? Thank you in advance.
@thomaskenny6393
4 жыл бұрын
I solved this problem by adding "If ActiveDocument.Saved = False Then ActiveDocument.Save" directly above "TargetDoc.Close False"
@keithng6480
4 жыл бұрын
Hi! Great tutorial and helped me greatly. However, the macro seems to create an extra empty file after its done with everything... any way for me to correct this?
@jiejenn
4 жыл бұрын
If you want, you can send me your Word file and your Excel data file to KZitem@LearnDataAnalysis.org and I can take a look.
@jeremybautista5100
4 жыл бұрын
@@jiejenn Hi was this solved? I am having the same issue
@jeremybautista5100
4 жыл бұрын
Sorry not the same issue its just all the merges are just blank
@siddhesh4040
4 жыл бұрын
Thank you so much.... :-)
@nullhas
5 жыл бұрын
Hi Jenn, Pls advise, how to select rows to merge. I do not want to delete previous data.
@Permik123
5 жыл бұрын
After you have connected to your database file on the Word Ribon choose edit recipient list. This is the Microsoft article on the subject. support.office.com/en-us/article/mail-merge-using-an-excel-spreadsheet-858c7d7f-5cc0-4ba1-9a7b-0a948fa3d7d3
@林春梅-e9c
4 жыл бұрын
I have a question, If I just need the pdf File, Can I write a code to delete the files? or can I make a pdf file without creating a word document? Thank you.
@jiejenn
4 жыл бұрын
You can directly create the PDF without creating the word doc.
@Pansoworks
5 жыл бұрын
Thank you so much
@emiliazuloaga3602
4 жыл бұрын
Hello, When I run the VBA and it gets to the .OpenDataSource line of code, another window opens up, the says “select table”, I then click my workbook, click options, then click “system tables” so that the table I am referencing shows up in the window. I then click that table, click ok then nothing happens. No error messages, nothing. Can you help? Thanks
@jiejenn
4 жыл бұрын
Not sure if I can help you given such little information you provided. If you want, you can send me your data file + Word document (must contain the VBA code) to KZitem@LearnDataAnalysis.org and I can take a look when I have time.
@santrisholawat5526
5 жыл бұрын
how to create with button of source_file & file save_as (dinamic path)? folder & filename picker
@MrMarho
4 жыл бұрын
Nice tutorial! I have some questions though. What if you already have a conditional statement (like skip record if mergefield Field_name is ""). This means the loop will still try to run the total number of records. What is the workaround? Can be filename also be dynamic? i.e. a function of text Manipulation?
@jiejenn
4 жыл бұрын
Give me an example.
@MrMarho
4 жыл бұрын
@@jiejenn Let's say from Excel you have 2 fields; Name and DOB. Now when doing mailmerge in Word, you put a conditional saying skip record if DOB = "". Also In Excel, the Name field is a combination of Title, Fname, Mname, and Lname from an original database. So your name field in excel will come as "Miss Jane A. Doe" or "Mr. John Doe" if there's no Mname. This means when you do mailmerge you have just two fields name and DOB in word. From your tutorial, the code will go through the total number of records and save as .docx or export to .pdf regardless of what is in the field. This means it will even do a merge for empty fields as far as you have set up your mergefields in word as you would like them to be outputted. So this is what I would like to do, use pagecount to know the number of pages it needs to loop through OR use and IF statement in the VBA code to know what records to actually save indiviudual documents for. It will also extract just the first and last name from each record.
@cupricrafts
4 жыл бұрын
Do I have to change folder_saved and source_file_path everytime I have a new database and a new document?
@jiejenn
4 жыл бұрын
Just source file path. Folder saved is where you want your output files to be sact.
@cupricrafts
4 жыл бұрын
@@jiejenn And could I set up the saved path to be always the folder of the active document?
@jiejenn
4 жыл бұрын
@@cupricrafts Sure. Follow the steps below. 1. Remove the line "Const FOLDER_SAVED As String" 2. Inside MailMerge_Automation macro, copy paste the following two lines: Dim FOLDER_SAVED As String FOLDER_SAVED = ThisDocument.Path & "\"
@cupricrafts
4 жыл бұрын
@@jiejenn thanks you so much! I'm gonna try the code today 😎
@cupricrafts
4 жыл бұрын
@@jiejenn the files are saved in microsoft templates folder, unrelated to the active document.
@lashadolenjashvili9775
4 жыл бұрын
Hey Jie Jenn, thanks for this tutorial. I am trying to do the same with Publisher, but I can't figure out what to do. Could you please somehow look at my code and give me an advice?
@jiejenn
4 жыл бұрын
Do you mean Windows publisher?
@lashadolenjashvili9775
4 жыл бұрын
@@jiejenn I am very glad you answered. Yes, Microsoft Publisher. I have Mail Merge, it is connected to excel sheet. And I want to convert each record to separate PDF. I wrote a simple script, it generates PDFs with correct names, but for some reason each PDF contains only the second record from excel.
@michaelfaw
5 жыл бұрын
I do follow the step that you give. But why the result is blank document and pdf? any idea about this issue?
@jiejenn
5 жыл бұрын
Without looking at your code, your file, and the error you get, it is nearly impossible to help. I would suggest you post your question on Microsoft Word Forum.
Пікірлер: 262