Word Macro to create Excel formatted cell

glowackt Posts 1 Registration date Tuesday March 25, 2014 Status Member Last seen March 25, 2014 - Mar 25, 2014 at 02:32 PM
 Blocked Profile - Mar 25, 2014 at 03:48 PM
I have Word Macro that I run to create an excel spreadsheet with my bookmarks and their text.
Two of my bookmarks contain dates, and one contains a number. Most contain text.

Sub PS_Output_ExportBookmarks()
'Before Running this macro, click Tools / References
'Select Microsoft Excel 14.0 Object Library
Dim bk As Bookmark
Dim appXl As Excel.Application
Dim wbk As Excel.Workbook
Dim wst As Excel.Worksheet
Dim 1Row As Long

Set appXl = CreateObject("Excel.Application")
With appXl
.Visible = True
Set wbk = .Workbooks.Add
Set wst = wbk.Worksheets(1)
1Row = 1
wst.Cells(1Row, 1) = "Bookmark name"
wst.Cells(1Row, 2) = "Bookmark text"
wst.Rows(1Row).Font.Bold = True
End With

For Each bk In ActiveDocument.Bookmarks
1Row = 1Row + 1
wst.Cells(1Row, 1) = bk.Name
wst.Cells(1Row, 2) = bk.Range.Text
Next bk

End Sub

When the excel spreadsheet is generated, the entries for the two dates and the number appear "empty". The cells are not really empty, the data is there, just not visible. The dates are 2/2/2014 and 10/19/2012, the number is 5.

When I import the spreadsheet into Access, the dates appear as 41672 and 41201 respectively. The 5 appears as a 5. All the bookmark text fields are automatically created as a memo upon import into Access.

If I modify the spreadsheet by prefixing the dates with a single quote, the dates retain their format when imported into Access.

Is there a way I can place a single quote in front of the date values so they retain their format when the spreadsheet is created by the macro?

1 reply

Blocked Profile
Mar 25, 2014 at 03:48 PM
Try converting them to text as in: =text(whateverthetextwouldbe)

The numbers are the number of days since 1/1/1900.

Are the fields in the database that the dates going into, formatted as a date, or text?

Why do you not convert the word document into a text file, and import the text file into the database, then it won't have all of the extra over head of excel formatting.

Just a thought. I used to have an admin that would print out an invoice to scan it back in, she didn't realize she could print to an image/file.

Let us now!

Have fun!