By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 3,407 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Print # Statement

P: 3
I wrote some VBA code to create web pages (about 100 "identical" pages) with data from Access tables (data is static). Using "Print #1, ..." is a bit onerous but, with loops for repeated table rows was not too bad. However, I did find one problem for which I have no solution.
In certain conditions (100% repeatable within a group of data but not consistent across groups of identical type data), the Print # statement adds "#" before and after a hyperlink, e.g. #http://www.yoursite.com/BigBook.pdf#. This, of course, causes the hyperlink to fail.
I have retyped the "offending" Print statements, replaced the data, changed file extensions of the linked document, tried outputting as a .txt file rather than an .htm file. All to no avail.
Documentation I have looked at warns of the Write # statement adding characters based on what it thinks you are trying to say but there is no such warning for the Print # statement.
Any thoughts? Thanks.
May 30 '08 #1
Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,638
I wrote some VBA code to create web pages (about 100 "identical" pages) with data from Access tables (data is static). Using "Print #1, ..." is a bit onerous but, with loops for repeated table rows was not too bad. However, I did find one problem for which I have no solution.
In certain conditions (100% repeatable within a group of data but not consistent across groups of identical type data), the Print # statement adds "#" before and after a hyperlink, e.g. #http://www.yoursite.com/BigBook.pdf#. This, of course, causes the hyperlink to fail.
I have retyped the "offending" Print statements, replaced the data, changed file extensions of the linked document, tried outputting as a .txt file rather than an .htm file. All to no avail.
Documentation I have looked at warns of the Write # statement adding characters based on what it thinks you are trying to say but there is no such warning for the Print # statement.
Any thoughts? Thanks.
In certain conditions (100% repeatable within a group of data but not consistent across groups of identical type data), the Print # statement adds "#" before and after a hyperlink, e.g.
Can you possibly provide a 'sampling' of this group of data as well as the code logic for writing it?
Jun 1 '08 #2

NeoPa
Expert Mod 15k+
P: 31,494
I can't help directly, but I would advise you to examine the data that DOES have the "#"s added and compare this with data which doesn't. See if you can notice any patterns in the contents.

If you find offending characters (or strings) then we can help you to get around the problem using the Replace() function.
Jun 2 '08 #3

P: 3
Can you possibly provide a 'sampling' of this group of data as well as the code logic for writing it?
LINE OF DATA:

AMR: ANL-EBS-MD-000049
AMR Version: ANL-EBS-MD-000049
AMR Hyperlink:

https://sitename.siteplace.gov/sites/mods/SIMs/sci-box/AMRs/ANL-WIS-MD-000049/ANL-WIS-MD-000049.pdf
Software: 10088-3.0s-02
Software Hyperlink: https://sitename.siteplace.gov/sites/mods/SIMs/sci-box/Software/STN10088-30s-02.doc

CODE FRAGMENT - AMR works fine; Software hyperlink is enclosed in #'s.

Expand|Select|Wrap|Line Numbers
  1. Do While Not rstTable1.EOF()
  2.  
  3.             T1_AMR_Version = rstTable1("AMR_Version") & ""
  4.             T1_AMR_Hyperlink = rstTable1("AMR_Hyperlink") & ""
  5.             T1_Software = rstTable1("Software") & ""
  6.             T1_Software_Hyperlink = rstTable1("Software_Hyperlink") & ""
  7.             T1_Software_Title = rstTable1("Software_Title") & ""
  8.  
  9.             Print #1, "<TR>"
  10.             Print #1, "<TD width=50% class=ms-rtetablecells style=" & """" & "VERTICAL-ALIGN: top" & """" & 
  11.  
  12. ">"
  13.             Print #1, "<DIV>&nbsp"
  14.  
  15.             If Len(T1_AMR_Version) > 5 Then
  16.                 Print #1, "<A href=" & """" & T1_AMR_Hyperlink & """" & "><U><FONT"
  17.                 Print #1, "color=#0000ff>" & T1_AMR_Version & "</FONT></U></A></DIV></TD>"
  18.             Else
  19.                 Print #1, "</DIV></TD>"
  20.             End If
  21.  
  22.             Print #1, "<TD width=25% class=ms-rtetablecells style=" & """" & "VERTICAL-ALIGN: top" & """" & 
  23.  
  24. ">"
  25.             Print #1, "<DIV>&nbsp"
  26.  
  27.             If Len(T1_Software) > 5 Then
  28.                 Print #1, "<A href=" & """" & T1_Software_Hyperlink & """" & "><U><FONT"
  29.                 Print #1, "color=#0000ff>" & T1_Software & "</FONT></U></A></DIV></TD>"
  30.             Else
  31.                 Print #1, "</DIV></TD>"
  32.             End If
  33.  
  34.             Print #1, "<TD width=25% class=ms-rtetablecells style=" & """" & "VERTICAL-ALIGN: top" & """" & 
  35.  
  36. ">"
  37.  
  38.             If Len(T1_Software) > 5 Then
  39.                 Print #1, "<DIV>&nbsp;" & T1_Software_Title & "</DIV></TD></TR>"
  40.             Else
  41.                 Print #1, "<DIV>&nbsp;</DIV></TD></TR>"
  42.             End If
  43.  
  44.             rstTable1.MoveNext
  45.  
  46.         Loop
Jun 2 '08 #4

ADezii
Expert 5K+
P: 8,638
LINE OF DATA:

AMR: ANL-EBS-MD-000049
AMR Version: ANL-EBS-MD-000049
AMR Hyperlink:

https://sitename.siteplace.gov/sites...-MD-000049.pdf
Software: 10088-3.0s-02
Software Hyperlink: https://sitename.siteplace.gov/sites...088-30s-02.doc

CODE FRAGMENT - AMR works fine; Software hyperlink is enclosed in #'s.

Expand|Select|Wrap|Line Numbers
  1. Do While Not rstTable1.EOF()
  2.  
  3.             T1_AMR_Version = rstTable1("AMR_Version") & ""
  4.             T1_AMR_Hyperlink = rstTable1("AMR_Hyperlink") & ""
  5.             T1_Software = rstTable1("Software") & ""
  6.             T1_Software_Hyperlink = rstTable1("Software_Hyperlink") & ""
  7.             T1_Software_Title = rstTable1("Software_Title") & ""
  8.  
  9.             Print #1, "<TR>"
  10.             Print #1, "<TD width=50% class=ms-rtetablecells style=" & """" & "VERTICAL-ALIGN: top" & """" & 
  11.  
  12. ">"
  13.             Print #1, "<DIV>&nbsp"
  14.  
  15.             If Len(T1_AMR_Version) > 5 Then
  16.                 Print #1, "<A href=" & """" & T1_AMR_Hyperlink & """" & "><U><FONT"
  17.                 Print #1, "color=#0000ff>" & T1_AMR_Version & "</FONT></U></A></DIV></TD>"
  18.             Else
  19.                 Print #1, "</DIV></TD>"
  20.             End If
  21.  
  22.             Print #1, "<TD width=25% class=ms-rtetablecells style=" & """" & "VERTICAL-ALIGN: top" & """" & 
  23.  
  24. ">"
  25.             Print #1, "<DIV>&nbsp"
  26.  
  27.             If Len(T1_Software) > 5 Then
  28.                 Print #1, "<A href=" & """" & T1_Software_Hyperlink & """" & "><U><FONT"
  29.                 Print #1, "color=#0000ff>" & T1_Software & "</FONT></U></A></DIV></TD>"
  30.             Else
  31.                 Print #1, "</DIV></TD>"
  32.             End If
  33.  
  34.             Print #1, "<TD width=25% class=ms-rtetablecells style=" & """" & "VERTICAL-ALIGN: top" & """" & 
  35.  
  36. ">"
  37.  
  38.             If Len(T1_Software) > 5 Then
  39.                 Print #1, "<DIV>&nbsp;" & T1_Software_Title & "</DIV></TD></TR>"
  40.             Else
  41.                 Print #1, "<DIV>&nbsp;</DIV></TD></TR>"
  42.             End If
  43.  
  44.             rstTable1.MoveNext
  45.  
  46.         Loop
Nothing immediately jumps up at me, but tomorrow I'll run the code on some test data, see what happens, and get back to you. See you later. It only took a few minutes, so I tested it now. I localized your code, dumped the HTML to a Text File, and it seems to work fine. The code and subsequent results are posted below:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstHTML As DAO.Recordset
  3. Dim T1_AMR_Version As String
  4. Dim T1_AMR_Hyperlink As String
  5. Dim T1_Software As String
  6. Dim T1_Software_Hyperlink  As String
  7. Dim T1_Software_Title As String
  8.  
  9. Set MyDB = CurrentDb()
  10. Set rstHTML = MyDB.OpenRecordset("tblTest", dbOpenDynaset)
  11.  
  12. Open "C:\HTMLTest.txt" For Output As #1
  13.  
  14. Do While Not rstHTML.EOF()
  15.   T1_AMR_Version = rstHTML("AMR_Version") & ""
  16.   T1_AMR_Hyperlink = rstHTML("AMR_Hyperlink") & ""
  17.   T1_Software = rstHTML("Software") & ""
  18.   T1_Software_Hyperlink = rstHTML("Software_Hyperlink") & ""
  19.   T1_Software_Title = rstHTML("Software_Title") & ""
  20.  
  21.   Print #1, "<TR>"
  22.   Print #1, "<TD width=50% class=ms-rtetablecells style=" & """" & "VERTICAL-ALIGN: top" & """" & ">"
  23.   Print #1, "<DIV>&nbsp"
  24.  
  25.     If Len(T1_AMR_Version) > 5 Then
  26.       Print #1, "<A href=" & """" & T1_AMR_Hyperlink & """" & "><U><FONT"
  27.       Print #1, "color=#0000ff>" & T1_AMR_Version & "</FONT></U></A></DIV></TD>"
  28.     Else
  29.       Print #1, "</DIV></TD>"
  30.     End If
  31.  
  32. Print #1, "<TD width=25% class=ms-rtetablecells style=" & """" & "VERTICAL-ALIGN: top" & """" & ">"
  33. Print #1, "<DIV>&nbsp"
  34.  
  35.   If Len(T1_Software) > 5 Then
  36.     Print #1, "<A href=" & """" & T1_Software_Hyperlink & """" & "><U><FONT"
  37.     Print #1, "color=#0000ff>" & T1_Software & "</FONT></U></A></DIV></TD>"
  38.   Else
  39.     Print #1, "</DIV></TD>"
  40.   End If
  41.  
  42. Print #1, "<TD width=25% class=ms-rtetablecells style=" & """" & "VERTICAL-ALIGN: top" & """" & ">"
  43.  
  44.   If Len(T1_Software) > 5 Then
  45.     Print #1, "<DIV>&nbsp;" & T1_Software_Title & "</DIV></TD></TR>"
  46.   Else
  47.     Print #1, "<DIV>&nbsp;</DIV></TD></TR>"
  48.   End If
  49.  
  50.   rstHTML.MoveNext
  51. Loop
  52.  
  53. Close #1
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. <TR>
  2. <TD width=50% class=ms-rtetablecells style="VERTICAL-ALIGN: top">
  3. <DIV>&nbsp
  4. <A href="https://sitename.siteplace.gov/sites/mods/SIMs/sci-box/AMRs/ANL-WIS-MD-000049/ANL-WIS-MD-000049.pdf"><U><FONT
  5. color=#0000ff>ANL-EBS-MD-000049</FONT></U></A></DIV></TD>
  6. <TD width=25% class=ms-rtetablecells style="VERTICAL-ALIGN: top">
  7. <DIV>&nbsp
  8. <A href="https://sitename.siteplace.gov/sites/mods/SIMs/sci-box/Software/STN10088-30s-02.doc"><U><FONT
  9. color=#0000ff>10088-3.0s-02</FONT></U></A></DIV></TD>
  10. <TD width=25% class=ms-rtetablecells style="VERTICAL-ALIGN: top">
  11. <DIV>&nbsp;</DIV></TD></TR>
Jun 3 '08 #5

P: 3
Curiouser and curiouser! When I run it, I get the following for line 8:

<A href="#https://sitename.siteplace.gov/sites/mods/SIMs/sci-box/Software/STN10088-30s-02.doc#"><U><FONT

And that was on 80 pages with between 0 and 20 software program hyperlinks.

Oh, well! Thank you.
Jun 3 '08 #6

Denburt
Expert 100+
P: 1,356
Are you pulling this from a field that is designated as a link?
I have had this issue in the past when working with similar code, all you can do is remove them on the fly...
Jun 3 '08 #7

Denburt
Expert 100+
P: 1,356
Sorry someone walked in so I was unable to complete the last post with using a mid statement or right and left etc. I can also remember scenarios where you may end up with the link twice or something wish I had more time to test/check into this further if I can find the time later i'll be back.
Jun 3 '08 #8

Denburt
Expert 100+
P: 1,356
The following link should explain how you can utilize the hyperlink parts so that it only provides what you want.
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
Jun 3 '08 #9

ADezii
Expert 5K+
P: 8,638
Curiouser and curiouser! When I run it, I get the following for line 8:

<A href="#https://sitename.siteplace.gov/sites/mods/SIMs/sci-box/Software/STN10088-30s-02.doc#"><U><FONT

And that was on 80 pages with between 0 and 20 software program hyperlinks.

Oh, well! Thank you.
Try the following code modification on Line #36. I displayed Lines 35 thru 40 for convenience only:
Expand|Select|Wrap|Line Numbers
  1. If Len(T1_Software) > 5 Then
  2.   Print #1, "<A href=" & """" & Replace(T1_Software_Hyperlink, "#", "") & """" & "><U><FONT"
  3.   Print #1, "color=#0000ff>" & T1_Software & "</FONT></U></A></DIV></TD>"
  4. Else
  5.   Print #1, "</DIV></TD>"
  6. End If
Jun 3 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.