Append to Existing file

charlesdh · #1 07-29-2015, 11:18 AM

Hi,

In the code below I'm attempting to append data from existing bookmarks.
When I run the code I can see the data in each bookmark. But, when I use the bookmark info it does not populate the existing file "log.txt".
But, when I assign the the data it will populate the file "log.txt".

Can someone tell me what I'm doing wrong?

Code:

Sub Test_BookMark_Append()
Dim filenr As Long
Dim Fname As Variant
Dim Lname As Variant
Dim Mage As String
Dim mypath
filenr = FreeFile
'Fname = ThisDocument.Bookmarks("FirstName").Range.Text' not working
'Lname = ThisDocument.Bookmarks("LastName").Range.Text' not working
'Mage = ThisDocument.Bookmarks("Mage").Range.Text' not working
Lname = "Sam"'''' works
Fname = "Dootie"'''' works
Mage = "70"'''' works
mypath = ThisDocument.Path & "\log.txt"
Open mypath For Append As #filenr
Print #filenr, Fname & "," & Lname & "," & Mage
Close #filenr
End Sub

Opp's I posted this at

http://www.mrexcel.com/forum/excel-q...-document.html

And

http://forums.techguy.org/business-a...ting-file.html

Sorry!

When using the bookmarks for some reason when appending to existing file creates a new row. How can I stop it?

macropod · #2 07-29-2015, 05:52 PM

If the code is in a template, ThisDocument, will refer to the template, not to the ActiveDocument. Presumably, the template's bookmark would be empty. Also, how are you populating the bookmarks? When people do this programmatically, the text typically gets inserted after the bookmark, not in it. For code to populate a bookmark, see: https://www.msofficeforums.com/word-...html#post67058

charlesdh · #3 07-29-2015, 06:01 PM

HI,

Thank for the reply. I worked out my problem.
When I used this

Code:

Fname = ThisDocument.Bookmarks("FirstName").Range.Text' not working
Fname = ThisDocument.Bookmarks("FirstName").Range.Word(1)'' should have used this

My problem now is if I use this for Date of Birth:

Code:

DOB = ThisDocument.Bookmarks("DateOfBirth").Range.Word(1)

It will bring back a "4" being it's the first character in the Date of Birth.
Am trying to figure this out.

macropod · #4 07-29-2015, 06:11 PM

Code like:
ThisDocument.Bookmarks("FirstName").Range.Text
returns an entire bookmark's text.

Code like:
ThisDocument.Bookmarks("FirstName").Range.Words(1)
returns only the first 'word' from the bookmarked text.

Code like:
ThisDocument.Bookmarks("FirstName").Range.Word(1)
generates an error!

charlesdh · #5 07-29-2015, 06:13 PM

Not sure about the bookmarks.
I enter the "LastName" in the table where I want the bookmark to be. I clicked on the left side of "LastName" and it highlighted the field. I then open the bookmark tab and enter the name for the bookmark as "LastName".

As you can see I'm trying to learn how to use "Word"..

charlesdh · #6 07-29-2015, 06:16 PM

Thanks... I'll have to get back on this tomorrow...Sorry

macropod · #7 07-29-2015, 06:24 PM

Perhaps you could attach a copy of the document so we can see exactly what you're working with.

charlesdh · #8 07-30-2015, 11:13 AM

HI,

Per your request here's a modified file that I'm using.
I have all of the files that I'm working with in the same folder.
That includes a "Template" and "Non Template".
I attached the "Non Template".
Test both I get the same result.
If I assign the string values it will populate the "Log.txt" file.
As far as using the "Range.Text" it will populate the "log.txt" file in separate
row instead of the same row.
If I use the "Range.Words(1)" it will populate the "log.txt" file in the same row.
But, as you mentioned it will only pick the fist word and not the whole string.

I'm using word for mac so I can not use "ActiveX".

Any help you give would be appreciated.

macropod · #9 07-30-2015, 03:15 PM

The problem is that you have bookmarked entire cells, including the end-of-cell markers, that get included in the output. You need to exclude those. Try:

Code:

Sub Test_BookMark_Append()
Dim filenr As Long
Dim Ffname As String
Dim Llname As String
Dim BD As String
Dim Adrs As String
Dim mypath
Dim Rng As Range
filenr = FreeFile
With ThisDocument
  Set Rng = .Bookmarks("FName").Range
  With Rng
    .End = .End - 1
    Ffname = .Text
  End With
  Set Rng = .Bookmarks("LName").Range
  With Rng
    .End = .End - 1
    Llname = .Text
  End With
  Set Rng = .Bookmarks("BirthDate").Range
  With Rng
    .End = .End - 1
    BD = .Text
  End With
  Set Rng = .Bookmarks("StreetAddress").Range
  With Rng
    .End = .End - 1
    Adrs = .Text
  End With
End With
'Lname = "Sam" '''' works
'Fname = "Dootie" '''' works
'BD = "70" '''' works
'Adrs = "1234 Myroad" ''''works
mypath = ThisDocument.Path & "\log.txt"
Open mypath For Append As #filenr
Print #filenr, Ffname & "," & Llname & "," & BD & "," & Adrs
Close #filenr
End Sub

charlesdh · #10 07-30-2015, 03:35 PM

macropod

Thanks. That worked. I was going to delete the "Bookmarks" and then have the code look at "Cell(1,2).Text".

It's nice to know that when I need help you and your members are ready.
As you noted in my help to the forum I reply to "Excel" request.
I need to expand my knowledge to include "Word".

Thanks again.
We show this post as "Solved".

Charles

macropod · #11 07-30-2015, 04:02 PM

You could do it with cell references, but you'd still have to exclude the end-of-cell marker.

charlesdh · #12 07-30-2015, 04:31 PM

Thanks. I'll put this in my memory bank...

macropod · #13 07-30-2015, 08:21 PM

With cell referencing, you could use:

Code:

Sub Data_Append()
Dim mypath As String
mypath = ThisDocument.Path & "\log.txt"
Dim Rng As Range
Dim i As Long
Dim StrData As String
Dim filenr As Long
filenr = FreeFile
With ThisDocument.Tables(1)
  For i = 1 To 4
    Set Rng = .Cell(i, 1).Range
    With Rng
      .End = .End - 1
      StrData = StrData & "," & .Text
    End With
  Next
End With
StrData = Left(StrData, Len(StrData) - 1)
Open mypath For Append As #filenr
Print #filenr, StrData
Close #filenr
End Sub

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
import outline into existing file	eNGiNe	PowerPoint	0	02-18-2015 05:56 AM
new style for append only	thebrin	Word	0	02-03-2015 06:03 PM
How to append specific *.tif image with another tif file through Word VBA	aarun2	Word VBA	1	04-08-2014 03:20 PM
sending data from UserForm of existing excel file to a new excel file	saltlakebuffalo	Excel Programming	0	02-13-2014 10:55 PM
adding a pst file to an existing pst file	falcodriver	Outlook	2	12-14-2011 08:16 AM