Tables to Comma Delimited Text File

[redsun]

Hello Everybody,
I hope somebody can help me with my dilema.
I have been trying all sorts of ways to accomplish this task, but to no avail. :banghead:
I have a word document consisting of several pages and growing.
Each page has two tables that are bookmarked "bmTable1", bmTable2" etc..
What i would like to do is :-
From the start page perhaps have a button that when clicked runs some code or a macro that does the following -
Scans through the pages picking out the bookmarked table contents and copies them to a text file in my documents folder, appending the data as it goes and in a comma delimited format.
And if also possible, could i be able to select via a form what bookmarked tables get transfered.
Any help would be most appreciated.

[lucas]

I probably won't be much help with the tables and the csv personally but it would help those who can if you would provide a sample of the document with tables and sample data to work with.

It would also help if you could show what you have tried...code, steps, etc. You may have to post a few bogus posts in this thread to get your post count up to 5 so you can post your document...hit post reply at the bottom left of the last post and after typing your message scroll down and look for "manage attachments"

I did find one in the knowledge base that will export your tables to csv....the title says it creates an access table and it does but it creates a csv first for access to work with....maybe it will help get you started. You will have to change or note the path set up and create a blank access file to get it to work....the path and the name required for the access database can be retrieved from the first few lines of the code. Click here (http://vbaexpress.com/kb/getarticle.php?kb_id=243) to examine this article.

As for the button, I have put together a small flash demo that I will have up for a few days if you would like to take a look at it. It demonstrates how to add a menu button to your specific document to run code in the same document. Click here (http://slucas.virtualave.net/Wink/CustomMenuItem.htm) if you're interested.

[redsun]

Thanks for the prompt reply Lucas,
I will have a look at the article in the knowledge base, i must have missed that one. And also the flash demo link.
"Where there's a will, there's a way", i keep telling myself.

[redsun]

I can get the example in the knowledge base you refered to, to produce a txt file just by changing csv to txt in the first or second line.
The text it produces is in quotation marks followed by a comma.
How can i get rid of the quotation marks, and just have the comma separating the cell data.


Option Explicit
Const Direct = "C:\VBAFiles\" 'full path to files
Const CsvFile = "A22.txt"
Const DataBase = "A22.mdb" ' this needs to be a valid database
Const NewTabelName = "A22Gusting"
Sub Main()
Dim I As Integer
Dim mMyData() As String
Dim mRow As Integer
Dim mColumn As Integer
Dim mClearIt As String
If Not CheckForDirectory Then Exit Sub
mClearIt = Chr(13) & Chr(7) ' the carriage return and end of cell marker
For I = 1 To ThisDocument.Tables.Count
mRow = ThisDocument.Tables(I).Rows.Count
mColumn = ThisDocument.Tables(I).Columns.Count
' this will get all text from the table and split each cell into a variable array
' it will create an extra "column" of information this is because there will be
'2 carriage return and end of cell marker
'.ConvertToText Separator:=wdSeparateByCommas
'mMyData = Split(ThisDocument.Tables(I).ConvertToText Separator:=wdSeparateByCommas, mClearIt)
'mMyData = Split(ThisDocument.Tables(I).Range.Text, mClearIt)
WriteCSV mMyData, mColumn, I
Next
End Sub
Sub WriteCSV(WrtData() As String, ColCnt As Integer, BKcntr As Integer)
Dim I As Integer
Dim HldStr As String
Dim cntr As Long
Open Direct & CsvFile For Output As #1
For I = LBound(WrtData) To UBound(WrtData) Step ColCnt + 1
If I < UBound(WrtData) Then
'build string that will be written to a file
For cntr = 0 To ColCnt + 1
If cntr = 0 Then
HldStr = Chr(34) & CStr(I / (ColCnt + 1)) & Chr(34) & ","
Else
HldStr = HldStr & Chr(34) & WrtData(I + cntr - 1) & Chr(34) & ","
End If
Next
'write the file
Print #1, Left(HldStr, Len(HldStr) - 4)
HldStr = vbNullString
End If
Next
Close #1
End Sub

Edit: Use the VBA tags instead of code tags and it will format it the way it looks in the vbe. I have edited your post.....

[lucas]

You need to remove the Char34 references:
Option Explicit
Const Direct = "F:\Temp\" 'full path to files
Const CsvFile = "A22.txt"
Const DataBase = "A22.mdb" ' this needs to be a valid database
Const NewTabelName = "A22Gusting"
Sub Main()
Dim I As Integer
Dim mMyData() As String
Dim mRow As Integer
Dim mColumn As Integer
Dim mClearIt As String
If Not CheckForDirectory Then Exit Sub
mClearIt = Chr(13) & Chr(7) ' the carriage return and end of cell marker
For I = 1 To ThisDocument.Tables.Count
mRow = ThisDocument.Tables(I).Rows.Count
mColumn = ThisDocument.Tables(I).Columns.Count
' this will get all text from the table and split each cell into a variable array
' it will create an extra "column" of information this is because there will be
'2 carriage return and end of cell marker
mMyData = Split(ThisDocument.Tables(I).Range.Text, mClearIt)
WriteCSV mMyData, mColumn, I
Next
End Sub

[redsun]

I have removed all four instances of the Char34 reference, but the outputed text file has part of the data in the last cell of each line chopped off.
I looked in word vba help and Char() is an ansi character code, but what is the value in brackets.
Don't know whether to proceed with this code, or try another way to seek my goal inmy first post.

[lucas]

copy the sub main from my post#5...I removed the Char34 references for you and replace it in the code and it works fine....comma delimited-no quotes...change the path.

[redsun]

Did that, and it is still chopping off the end of the line.
Have attached a zip file with sample word doc and resulting txt file.
I forgot to mention, in the text file, how do iget rid of that first two digit number that is being generated..
Thanks in advance..