Replacing partial text in an HTML files

I have an HTML file named myHTML.htm

It looks like this:











I need to use my Access database to pull data from a table and insert
it into the HTML file
where it reads:

Doable?

Any help is appreciated,
lq

Re: Replacing partial text in an HTML files

It should be doable... the HTML file is a text file, so you can read it with
Access VBA I/O statements, e.g., Input#, just like any other text file, and
write a new file with data merged from the original and your table, with
appropriate statements, e.g., Print#.

The only part that could be tricky is to determine just what records from
your Access table you should retrieve and insert -- my recollection is that
you are a long-time participant here, and know how to retrieve data from
tables with VBA and DAO (or ADO), so I won't offer suggestions on that part.

Larry Linson
Microsoft Access MVP

"Lauren Quantrell" wrote in message
news:5aea0ccb-dd60-475f-84b3-e1f183309cdc@s13g2000prd.google groups.com...
>I have an HTML file named myHTML.htm
>
> It looks like this:
>
>
>
>
>
>
>
>
>
>
>
> I need to use my Access database to pull data from a table and insert
> it into the HTML file
> where it reads:
>
> Doable?
>
> Any help is appreciated,
> lq

Re: Replacing partial text in an HTML files

Thanks Larry.

Anyone needing a similar solution I post the necessary code below as
two functions (plus the functions that do the string substitution)


Function GetFileText ()
On Error GoTo myErr

Dim inputFolder As String
Dim inputFileName As String
Dim f
Dim myString As String

myString = "This is the string I want to insert"
:
inputFolder = "c:\"
inputFileName = "myFileName.htm"

Dim InputString As String
Dim OutputString As String

f = FreeFile

OutputString = ""
Open inputFolder & inputFileName For Input Access Read Shared As f
Do Until EOF(f)
Line Input #f, InputString
OutputString = OutputString & InputString
Loop

OutputString = dhReplaceAll(OutputString, "the string I want to
replace", myString)

Call OutputFile(OutputString)

Close #f

myExit:
Exit Function
myErr:
msgbox Err.Number & " - " & Err.Description
Resume myExit
End Function

Function OutputFile(myOutputString As String)
On Error GoTo myErr

Dim destFolder As String
Dim destFileName As String
Dim f

destFolder = "c:\"
destFileName = "myOutputFilename.htm"

f = FreeFile

Open destFolder & destFileName For Output As f

Print #f, myOutputString

Close #f

myExit:
Exit Function
myErr:
msgbox Err.Number & " - " & Err.Description
Resume myExit
End Function

Private Const dhcNoLimit As Integer = -1

Function dhCountIn(strText As String, strFind As String, Optional
fCaseSensitive As Boolean = False) As Integer
On Error GoTo myErr

Dim intCount As Integer
Dim intPos As Integer
Dim intMode As Integer

If Len(strFind) > 0 Then
If fCaseSensitive Then
intMode = vbBinaryCompare
Else
intMode = vbTextCompare
End If
intPos = 1
Do
intPos = InStr(intPos, strText, strFind, intMode)
If intPos > 0 Then
intCount = intCount + 1
intPos = intPos + Len(strFind)
End If
Loop While intPos > 0
Else
intCount = 0
End If

dhCountIn = intCount

myExit:
Exit Function
myErr:
msgbox Err.Number & " - " & Err.Description
Resume myExit
End Function

Function dhReplaceAll( _
ByVal strText As String, _
ByVal strFind As String, _
ByVal strReplace As String, _
Optional ByVal intFirst As Integer = 1, _
Optional ByVal intCount As Integer = dhcNoLimit, _
Optional ByVal fCaseSensitive As Boolean = False) As String
On Error GoTo myErr

Dim intLenFind As Integer
Dim intLenReplace As Integer
Dim intPos As Integer
Dim intStart As Integer
Dim intI As Integer
Dim intFound As Integer
Dim intLast As Integer
Dim intMode As Integer

If Len(strText) = 0 Then GoTo myExit
If Len(strFind) = 0 Then GoTo myExit
If intFirst = 0 Then GoTo myExit
If intCount = 0 Then GoTo myExit

If intFirst < 0 Then.
intFound = dhCountIn(strText, strFind)
intFirst = intFound + intFirst + 1
If intFirst < 1 Then intFirst = 1
End If

If intCount > dhcNoLimit Then intLast = intFirst + intCount

If fCaseSensitive Then
intMode = vbBinaryCompare
Else
intMode = vbTextCompare
End If

intLenFind = Len(strFind)
intLenReplace = Len(strReplace)
intPos = 1
intI = 1

Do
intPos = InStr(intPos, strText, strFind, intMode)

If intPos > 0 Then
If (intI >= intFirst) And _
((intCount = dhcNoLimit) Or (intI < intLast)) Then
strText = Left$(strText, intPos - 1) & _
strReplace & Mid$(strText, intPos + intLenFind)

intPos = intPos + intLenReplace
Else
intPos = intPos + intLenFind
End If
intI = intI + 1
If (intCount <> dhcNoLimit And intI >= intLast) Then
Exit Do
End If
End If

Loop Until intPos = 0

myExit:
dhReplaceAll = strText
Exit Function
myErr:
msgbox Err.Number & " - " & Err.Description
Resume myExit
End Function

Re: Replacing partial text in an HTML files

On Fri, 18 Jan 2008 19:44:38 GMT, "Larry Linson"
wrote:

You could certainly treat an HTML file as a text file, but you could
do the same with a database file (OK, perhaps as a binary file). The
reason we don't is that there is a better object model available, be
it DAO in the case of an MDB file, or DOM or XML in the case of HTML.

-Tom.


>It should be doable... the HTML file is a text file, so you can read it with
>Access VBA I/O statements, e.g., Input#, just like any other text file, and
>write a new file with data merged from the original and your table, with
>appropriate statements, e.g., Print#.
>
>The only part that could be tricky is to determine just what records from
>your Access table you should retrieve and insert -- my recollection is that
>you are a long-time participant here, and know how to retrieve data from
>tables with VBA and DAO (or ADO), so I won't offer suggestions on that part.
>
> Larry Linson
> Microsoft Access MVP
>
>"Lauren Quantrell" wrote in message
>news:5aea0ccb-dd60-475f-84b3-e1f183309cdc@s13g2000prd.googl egroups.com...
>>I have an HTML file named myHTML.htm
>>
>> It looks like this:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> I need to use my Access database to pull data from a table and insert
>> it into the HTML file
>> where it reads:
>>
>> Doable?
>>
>> Any help is appreciated,
>> lq
>

Re: Replacing partial text in an HTML files

"Tom van Stiphout" wrote in message
news:3p33p3h7f48uhrom96l7gsu4qdocupar4u@4ax.com...
> On Fri, 18 Jan 2008 19:44:38 GMT, "Larry Linson"
> wrote:
>
> You could certainly treat an HTML file as a text file, but you could
> do the same with a database file (OK, perhaps as a binary file). The
> reason we don't is that there is a better object model available, be
> it DAO in the case of an MDB file, or DOM or XML in the case of HTML.
>
> -Tom.

Well, yes you can use the DOM for XML, but Lauren is not needing to
"interpret" the XML, just replace one line with already-prepared XML, so
appeared to me that treating it as a text file is the simplest way to
accomplish her purpose.

Larry

Re: Replacing partial text in an HTML files

For this purpose I must pass the complete file as an HTML file as it's
taking advantage of an external API that cannot call the XML from a
different server location.
lq


On Jan 19, 1:58=A0am, "Larry Linson" wrote:
> "Tom van Stiphout" wrote in messagenews:3p33p3h7=
f48uhrom96l7gsu4qdocupar4u@4ax.com...
>
> > On Fri, 18 Jan 2008 19:44:38 GMT, "Larry Linson"
> > wrote:
>
> > You could certainly treat an HTML file as a text file, but you could
> > do the same with a database file (OK, perhaps as a binary file). The
> > reason we don't is that there is a better object model available, be
> > it DAO in the case of an MDB file, or DOM or XML in the case of HTML.
>
> > -Tom.
>
> Well, yes you can use the DOM for XML, but Lauren is not needing to
> "interpret" the XML, just replace one line with already-prepared XML, so
> appeared to me that treating it as a text file is the simplest way to
> accomplish her purpose.
>
> =A0Larry