DocAElstein
07-03-2019, 01:23 PM
Some notes and tests in support of possible answer to this Thread:
http://www.excelfox.com/forum/showthread.php/2347-what-is-the-best-way-to-populate-word-specific-locations-from-Excel-Data
Related also to these posts:
https://tinyurl.com/y3j3klvo
https://tinyurl.com/y5n4x7ku
https://tinyurl.com/y4av8rer
https://tinyurl.com/y4b34a9g
Code to make Day list from Excel File and show in Pop up Word .htm File.
MakeTagList Button , Sub MakeTagList()
This post gives some Overview notes for later reference to help clarify some other more detailed excelfox Threads.
MakeTagList Button. Briefly, overview:
The code Sub MakeTagList(), primarily does two things:
_ makes a simple text file with the important details from a Daily Diet Protocol
DailyTextFileForEmailAttatchment.JPG : https://imgur.com/MqEmlDF
2053
_ makes a long string of HTML coding which if written to a text file, (but with the usually given .txt extension replaced with .htm ), is recognised by Microsoft Word which can open it in a form which looks similar to a normal Word doc
That Word File is opened temporarily to show a pretty table form of the important details from a Daily Diet Protocol:
TemporaryWordPopUp.JPG : https://imgur.com/ZFhsdaQ
2054
_.___________
_ makes a simple text file with the important details from a Daily Diet Protocol
arrNuts()
The start point is that an array is made of used food products from daily pro, arrNuts()
arrNuts() is the = Items() array from a Dictionary. So it is a one dimension array starting at arrNuts(0). Each Element is a row from the from the Daily Excel Diet Protocol File, "ProAktuellex8600x2.xlsm". It is built up from a line like pseudo
Key:=ProRow with Entry in C column, Item:=Array(FoodProduct in 1st Column A , amount eaten from 3rd Column C , Kcal total in 9th column I , Fett , Eiweiß …. Etc.
( The Key is not used currently )
Rem 5 Text File
A text file is made from the array with a pipe, _ | _ , as separator like from:
arrNuts(0)(0) & "|" & arrNuts(0)(1) & "|" & arrNuts(0)(2) ……..etc..
arrNuts(1)(0) & "|" & arrNuts(1)(1) & "|" & arrNuts(1)(2) ……..etc..
arrNuts(2)(0) & …..etc..
The final text file is stored in the same Folder as most of the files to do with daily protocols, that is to say, the currently used main Folder . It is given a name something of the form pseudo
"MonatsUebersichtAnhang " & Date in format like "mmmm yyyy" & ".txt"
As example for any day in April, 2018, it will be like
"MonatsUebersichtAnhang April 2018.txt"
The main purpose of this text file is to have a file that can be attached to an Email which at the receiving end can then be for recording and using the important daily nutrition values in a graphic
I use the standard VBA type code lines for this , pseudo like
Open C: \ __ \ _ \ ¬¬¬-___"MonatsUebersichtAnhang April 2018.txt" For __ As __
_.___________
_ makes a long string of HTML coding which if written to a text file, (but with the usually given .txt extension replaced with .htm ), is recognised by Microsoft Word and can opens it in a form which looks similar to a normal Word doc
Rem 6 .htm Word Template File
An empty or almost empty .htm Word file, "DailyProtable.htm " , ( which should be in the main currently used Folder already , - ( download from box if necessary ) ) is converted into a single very long String variable , TotalFile
I use the fairly standard VBA way often used to get this long string from a .txt file. It seems to work equally well for a .htm file
Rem 6 get Template File as long HTML string . Template file is an almost empty Word .htm file.
Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
Let PathAndFileName = ThisWorkbook.Path & "\" & "DailyProtable.htm" ' Template file never changes
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
TotalFile = Space(LOF(FileNum)) '....and wot recives it hs to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum
Rem 7 modify main body text section of the template file
Rem 7
Things start getting a bit complicated now. It may help at this point to give a summary of the overall goal:
The final aim of this code and the associated code usually done just after, Sub PetrasDailyProWay1_COM_Way() , is to get a pretty summary table in a HTML code such as it is in a form that can be used for two things:
_ Temporarily in a Word .htm file which is opened temporarily to show the current stand of a finished or almost finished protocol,
_ part of that ( the main body part ) can be isolated into a functioning variable , MyLengthyStreaming , and used in the typical LECMO (http://www.excelfox.com/forum/showthread.php/2240-VBA-referring-to-external-shared-Libraries-1)-Early-1-5)-Laterly-Early-and-2)-Late-Binding-Techniques) technology code lines of the form
CDO.Message.HTMLBody = MyLengthyStreaming
CDO.Message.Send
( In actual fact the entire Word.htm HTML string, TotalFile , could usually be used, but I do not do this as
_ TotalFile has a lot of unnecessary text, presumably needed for Word .htm file recognition
as well as
_ there are some subtle problems which need to be addresses with the string before it can be used with German Telekom, as it appears that a Bug means that the German Telekom does not quite recognise correctly that standard HTML coding ( gmail and AOL seem not to have this problem ) https://telekomhilft.telekom.de/t5/E-Mail-Center/Text-Formatieren-behalten-von-Word-wenn-Einfuegen-in-Telekom-E/m-p/3136416#M129791 )
What basically needs to be done is to modify the TotalFile at the main body part. This Part of the text is almost empty at this stage. That almost empty main body part is replaced by a made pretty HTML table
It is all done in a bit of a messy way, and it is easy to lose track of what is going on. Once again, the purpose of this code:
The code Sub MakeTagList(), primarily
_ makes a simple text file with the important details from a Daily Diet Protocol
_ makes a long string of HTML coding which if written to a text file, (but with the usually given .txt extension replaced with .htm ), is recognised by Microsoft Word and can opens it in a form which looks similar to a normal Word doc
_ makes a long string of HTML coding which if written to a text file, (but with the usually given .txt extension replaced with .htm ), is recognised by Microsoft Word and can opens it in a form which looks similar to a normal Word doc
In Rem 7 we basically embed a Table ( in HTML coding form ) by doing a simple Replace of the start part of the main body in the string from the Template .htm file which looks like this
<div class=WordSection1> ( You can see that yourself if you open up the .htm file using a simple text editor, and look towards the end MainWordSectionBody.JPG : https://imgur.com/HWjHTyz )
That existing HTML coding, <div class=WordSection1> , is Replaced by
<div class=WordSection1> & A made HTML table done in a Function
That Function is called using the arrNuts() thus:
ProTble(arrNuts())
Function ProTble()
By a bit of trial and error and learning some basic HTML coding I was able to write this function which based on the data given in arrNuts() gives a lot of HTML coding which a Browser ( or Word when it has it included in the long HTML string opened using a .htm extension file ) can understand and produce a table formatted as I want:
Rem 8
Back in the main code we now have our original full file text string of HTML coding, TotalFile , modified to include in it the pretty table
The string is printed out to a text file in the usually way but with the .htm extension instead of the usual .txt extension. This file is currently given the name
"DailyProtableFilled.htm"
Rem 8 Over write the last ( likely yesterdays ) filled file, with the modified template file which is mainly still that what Word recognises as we only modified a bit of the main text body
Dim HighwayToHelloPro As Long ' For rewrite of modified DailyProtable.htm as DailyProtable.htm2
Let HighwayToHelloPro = FreeFile(0)
Open ThisWorkbook.Path & "\" & "DailyProtableFilled.htm" For Output As #HighwayToHelloPro ' Will be made if not there, and overwritten as Output rahter than Append
Print #HighwayToHelloPro, TotalFile
Close #HighwayToHelloPro
Rem 9 get Word using Excel
Rem 9
The final part of the code, Sub MakeTagList() , which is initiated by the Button, MakeTagList , brings up the file, "DailyProtableFilled.htm" , temporarily in Microsoft Word.
_.___________
Required Files:
Daily Diet Protocol :
"ProAktuellex8600x2.xlsm" :
https://app.box.com/s/fpztob9pcp92fl6hh81zgpzumw9ntcp0 ( Current )
https://www.magentacloud.de/share/38m-zuc2y3#$/ ( April 2018 )
Main Makro File:
"NeuProAktuelleMakros.xlsm" :
https://app.box.com/s/e93u19xidygreeeenlxyupm750dxe33f ( Current )
https://www.magentacloud.de/share/38m-zuc2y3#$/ ( April 2018 )
Usually in practical use, the execution of the last coding would be followed by the sending of the EMail using the Send Pro Mail Button.
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
Ref:
http://www.eileenslounge.com/viewtopic.php?f=27&t=29556#p228710
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10528#post10528
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
http://www.excelfox.com/forum/showthread.php/2347-what-is-the-best-way-to-populate-word-specific-locations-from-Excel-Data
Related also to these posts:
https://tinyurl.com/y3j3klvo
https://tinyurl.com/y5n4x7ku
https://tinyurl.com/y4av8rer
https://tinyurl.com/y4b34a9g
Code to make Day list from Excel File and show in Pop up Word .htm File.
MakeTagList Button , Sub MakeTagList()
This post gives some Overview notes for later reference to help clarify some other more detailed excelfox Threads.
MakeTagList Button. Briefly, overview:
The code Sub MakeTagList(), primarily does two things:
_ makes a simple text file with the important details from a Daily Diet Protocol
DailyTextFileForEmailAttatchment.JPG : https://imgur.com/MqEmlDF
2053
_ makes a long string of HTML coding which if written to a text file, (but with the usually given .txt extension replaced with .htm ), is recognised by Microsoft Word which can open it in a form which looks similar to a normal Word doc
That Word File is opened temporarily to show a pretty table form of the important details from a Daily Diet Protocol:
TemporaryWordPopUp.JPG : https://imgur.com/ZFhsdaQ
2054
_.___________
_ makes a simple text file with the important details from a Daily Diet Protocol
arrNuts()
The start point is that an array is made of used food products from daily pro, arrNuts()
arrNuts() is the = Items() array from a Dictionary. So it is a one dimension array starting at arrNuts(0). Each Element is a row from the from the Daily Excel Diet Protocol File, "ProAktuellex8600x2.xlsm". It is built up from a line like pseudo
Key:=ProRow with Entry in C column, Item:=Array(FoodProduct in 1st Column A , amount eaten from 3rd Column C , Kcal total in 9th column I , Fett , Eiweiß …. Etc.
( The Key is not used currently )
Rem 5 Text File
A text file is made from the array with a pipe, _ | _ , as separator like from:
arrNuts(0)(0) & "|" & arrNuts(0)(1) & "|" & arrNuts(0)(2) ……..etc..
arrNuts(1)(0) & "|" & arrNuts(1)(1) & "|" & arrNuts(1)(2) ……..etc..
arrNuts(2)(0) & …..etc..
The final text file is stored in the same Folder as most of the files to do with daily protocols, that is to say, the currently used main Folder . It is given a name something of the form pseudo
"MonatsUebersichtAnhang " & Date in format like "mmmm yyyy" & ".txt"
As example for any day in April, 2018, it will be like
"MonatsUebersichtAnhang April 2018.txt"
The main purpose of this text file is to have a file that can be attached to an Email which at the receiving end can then be for recording and using the important daily nutrition values in a graphic
I use the standard VBA type code lines for this , pseudo like
Open C: \ __ \ _ \ ¬¬¬-___"MonatsUebersichtAnhang April 2018.txt" For __ As __
_.___________
_ makes a long string of HTML coding which if written to a text file, (but with the usually given .txt extension replaced with .htm ), is recognised by Microsoft Word and can opens it in a form which looks similar to a normal Word doc
Rem 6 .htm Word Template File
An empty or almost empty .htm Word file, "DailyProtable.htm " , ( which should be in the main currently used Folder already , - ( download from box if necessary ) ) is converted into a single very long String variable , TotalFile
I use the fairly standard VBA way often used to get this long string from a .txt file. It seems to work equally well for a .htm file
Rem 6 get Template File as long HTML string . Template file is an almost empty Word .htm file.
Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
Let PathAndFileName = ThisWorkbook.Path & "\" & "DailyProtable.htm" ' Template file never changes
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
TotalFile = Space(LOF(FileNum)) '....and wot recives it hs to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum
Rem 7 modify main body text section of the template file
Rem 7
Things start getting a bit complicated now. It may help at this point to give a summary of the overall goal:
The final aim of this code and the associated code usually done just after, Sub PetrasDailyProWay1_COM_Way() , is to get a pretty summary table in a HTML code such as it is in a form that can be used for two things:
_ Temporarily in a Word .htm file which is opened temporarily to show the current stand of a finished or almost finished protocol,
_ part of that ( the main body part ) can be isolated into a functioning variable , MyLengthyStreaming , and used in the typical LECMO (http://www.excelfox.com/forum/showthread.php/2240-VBA-referring-to-external-shared-Libraries-1)-Early-1-5)-Laterly-Early-and-2)-Late-Binding-Techniques) technology code lines of the form
CDO.Message.HTMLBody = MyLengthyStreaming
CDO.Message.Send
( In actual fact the entire Word.htm HTML string, TotalFile , could usually be used, but I do not do this as
_ TotalFile has a lot of unnecessary text, presumably needed for Word .htm file recognition
as well as
_ there are some subtle problems which need to be addresses with the string before it can be used with German Telekom, as it appears that a Bug means that the German Telekom does not quite recognise correctly that standard HTML coding ( gmail and AOL seem not to have this problem ) https://telekomhilft.telekom.de/t5/E-Mail-Center/Text-Formatieren-behalten-von-Word-wenn-Einfuegen-in-Telekom-E/m-p/3136416#M129791 )
What basically needs to be done is to modify the TotalFile at the main body part. This Part of the text is almost empty at this stage. That almost empty main body part is replaced by a made pretty HTML table
It is all done in a bit of a messy way, and it is easy to lose track of what is going on. Once again, the purpose of this code:
The code Sub MakeTagList(), primarily
_ makes a simple text file with the important details from a Daily Diet Protocol
_ makes a long string of HTML coding which if written to a text file, (but with the usually given .txt extension replaced with .htm ), is recognised by Microsoft Word and can opens it in a form which looks similar to a normal Word doc
_ makes a long string of HTML coding which if written to a text file, (but with the usually given .txt extension replaced with .htm ), is recognised by Microsoft Word and can opens it in a form which looks similar to a normal Word doc
In Rem 7 we basically embed a Table ( in HTML coding form ) by doing a simple Replace of the start part of the main body in the string from the Template .htm file which looks like this
<div class=WordSection1> ( You can see that yourself if you open up the .htm file using a simple text editor, and look towards the end MainWordSectionBody.JPG : https://imgur.com/HWjHTyz )
That existing HTML coding, <div class=WordSection1> , is Replaced by
<div class=WordSection1> & A made HTML table done in a Function
That Function is called using the arrNuts() thus:
ProTble(arrNuts())
Function ProTble()
By a bit of trial and error and learning some basic HTML coding I was able to write this function which based on the data given in arrNuts() gives a lot of HTML coding which a Browser ( or Word when it has it included in the long HTML string opened using a .htm extension file ) can understand and produce a table formatted as I want:
Rem 8
Back in the main code we now have our original full file text string of HTML coding, TotalFile , modified to include in it the pretty table
The string is printed out to a text file in the usually way but with the .htm extension instead of the usual .txt extension. This file is currently given the name
"DailyProtableFilled.htm"
Rem 8 Over write the last ( likely yesterdays ) filled file, with the modified template file which is mainly still that what Word recognises as we only modified a bit of the main text body
Dim HighwayToHelloPro As Long ' For rewrite of modified DailyProtable.htm as DailyProtable.htm2
Let HighwayToHelloPro = FreeFile(0)
Open ThisWorkbook.Path & "\" & "DailyProtableFilled.htm" For Output As #HighwayToHelloPro ' Will be made if not there, and overwritten as Output rahter than Append
Print #HighwayToHelloPro, TotalFile
Close #HighwayToHelloPro
Rem 9 get Word using Excel
Rem 9
The final part of the code, Sub MakeTagList() , which is initiated by the Button, MakeTagList , brings up the file, "DailyProtableFilled.htm" , temporarily in Microsoft Word.
_.___________
Required Files:
Daily Diet Protocol :
"ProAktuellex8600x2.xlsm" :
https://app.box.com/s/fpztob9pcp92fl6hh81zgpzumw9ntcp0 ( Current )
https://www.magentacloud.de/share/38m-zuc2y3#$/ ( April 2018 )
Main Makro File:
"NeuProAktuelleMakros.xlsm" :
https://app.box.com/s/e93u19xidygreeeenlxyupm750dxe33f ( Current )
https://www.magentacloud.de/share/38m-zuc2y3#$/ ( April 2018 )
Usually in practical use, the execution of the last coding would be followed by the sending of the EMail using the Send Pro Mail Button.
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
Ref:
http://www.eileenslounge.com/viewtopic.php?f=27&t=29556#p228710
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10528#post10528
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)