Shifting data from columns and removing question mark charactersfrom columns
in [Help]
|
From: g on 2 Jul 2010 18:48 I moved data from a MS-Word Table into a MS-Access table using VBA code. Almost all of the word documents had five rows for first table in the document, but few Word documents had six rows for their first table which I did not know earlier. I created the Access table with five columns to hold data from each corresponding row of the first table of the Word document. Now, the documents which had six rows in their first table have their data flowing into sixth column of the Access table which was for storing data of table 2 from the Word document. I had something like Word Document A which has table 1 having five rows Table 1 Row 1 Row 1 Data Row 2 Row 2 Data .. .. Row 5 Row 5 Data But, Word Document B has a table 1 having six rows Table 1 Row 1 Row 1 Data Row 2 Row 2 Data .. .. Row 5 Row 5 Data Row 6 Row 6 Data Access Table "mytable" has a structure like Column for Row 1 of Table 1, Column for Row 2 of Table 1, Column for Row 3 of Table 1, Column for Row 4 of Table 1, Column for Row 5 of Table 1, Column for Row 1 of Table 2, Column for Row 2 of Table 2, .. .. .. Column for Row N of Table N, Access Table "myTable" has some rows like Column for Row 1 of Table 1 contains Row 1 Data of Table 1 Column for Row 2 of Table 1,contains Row 2 Data of Table 1 Column for Row 3 of Table 1,contains Row 3 Data of Table 1 Column for Row 4 of Table 1,contains Row 4 Data of Table 1 Column for Row 5 of Table 1,contains Row 5 Data of Table 1 Column for Row 1 of Table 2,contains Row 6 Data of Table 1 ' which is not correct Column for Row 2 of Table 2,contains Row 1 Data of Table 2 ' which is not correct 1. Is exporting the Access table data into MS-Excel, adding a column to the Access table for the sixth row and then reimporting the data back from Excel an option or are there better alternatives? 2. Also, there are some characters which appear as question marks in the Access table cells. If a column in the Word table had a blank space before the sentence started, the corresponding access table cell has a question mark character. I know I should have added some filtering in the VBA code before storing it in a Access table, but as I did not is there a quick way I can remove the question mark characters? Can I run some VBA which will traverse all columns of all rows of the Access table and remove the question mark character? I am using Access 2007. Any advice would be welcome.
From: Salad on 2 Jul 2010 19:17 g wrote: > > I moved data from a MS-Word Table into a MS-Access table using > VBA code. Almost all of the word documents had five rows for first table > in the document, but few Word documents had six rows for their first > table which I did not know earlier. I created the Access table with five > columns to hold data from each corresponding row of the first table of > the Word document. Now, the documents which had six rows in their first > table have their data flowing into sixth column of the Access table > which was for storing data of table 2 from the Word document. > > I had something like Word Document A which has table 1 having five rows > > Table 1 > > Row 1 Row 1 Data > Row 2 Row 2 Data > . > . > Row 5 Row 5 Data > > > But, Word Document B has a table 1 having six rows > > Table 1 > > Row 1 Row 1 Data > Row 2 Row 2 Data > . > . > Row 5 Row 5 Data > Row 6 Row 6 Data > > Access Table "mytable" has a structure like > > Column for Row 1 of Table 1, > Column for Row 2 of Table 1, > Column for Row 3 of Table 1, > Column for Row 4 of Table 1, > Column for Row 5 of Table 1, > Column for Row 1 of Table 2, > Column for Row 2 of Table 2, > . > . > . > Column for Row N of Table N, > > Access Table "myTable" has some rows like > > Column for Row 1 of Table 1 contains Row 1 Data of Table 1 > Column for Row 2 of Table 1,contains Row 2 Data of Table 1 > Column for Row 3 of Table 1,contains Row 3 Data of Table 1 > Column for Row 4 of Table 1,contains Row 4 Data of Table 1 > Column for Row 5 of Table 1,contains Row 5 Data of Table 1 > Column for Row 1 of Table 2,contains Row 6 Data of Table 1 ' which is > not correct > Column for Row 2 of Table 2,contains Row 1 Data of Table 2 ' which is > not correct > > > 1. Is exporting the Access table data into MS-Excel, adding a column to > the Access table for the sixth row and then reimporting the data back > from Excel an option or are there better alternatives? > > 2. Also, there are some characters which appear as question marks in > the Access table cells. If a column in the Word table had a blank space > before the sentence started, the corresponding access table cell has a > question mark character. I know I should have added some filtering in > the VBA code before storing it in a Access table, but as I did not is > there a quick way I can remove the question mark characters? Can I run > some VBA which will traverse all columns of all rows of the Access table > and remove the question mark character? > > I am using Access 2007. > > Any advice would be welcome. > > Your problem is vague to me. So my response could easily be incorrect or does not address your needs. This link may offer some iterest to you http://msdn.microsoft.com/en-us/library/aa537149%28office.11%29.aspx#officewordautomatingtablesdata_extractingdatafromatable Look for topic "Extracting Data from Word Tables Programmatically" at the link. You could do something like intFldCount = currentdb.TableDefs("YourTableName").Fields.Count to get the field count of a table. You could compare the counts of elements of the array from the link above Ubound(array) + 1 and compare to intFldCount to determine if you have enough columns. Maybe you want to alter the table. http://www.blueclaw-db.com/alter_table_ddl.htm
From: g on 2 Jul 2010 20:04 On 7/2/2010 7:17 PM, Salad wrote: > g wrote: > >> >> I moved data from a MS-Word Table into a MS-Access table using >> VBA code. Almost all of the word documents had five rows for first >> table in the document, but few Word documents had six rows for their >> first table which I did not know earlier. I created the Access table >> with five columns to hold data from each corresponding row of the >> first table of the Word document. Now, the documents which had six >> rows in their first table have their data flowing into sixth column of >> the Access table which was for storing data of table 2 from the Word >> document. >> >> I had something like Word Document A which has table 1 having five rows >> >> Table 1 >> >> Row 1 Row 1 Data >> Row 2 Row 2 Data >> . >> . >> Row 5 Row 5 Data >> >> >> But, Word Document B has a table 1 having six rows >> >> Table 1 >> >> Row 1 Row 1 Data >> Row 2 Row 2 Data >> . >> . >> Row 5 Row 5 Data >> Row 6 Row 6 Data >> >> Access Table "mytable" has a structure like >> >> Column for Row 1 of Table 1, >> Column for Row 2 of Table 1, >> Column for Row 3 of Table 1, >> Column for Row 4 of Table 1, >> Column for Row 5 of Table 1, >> Column for Row 1 of Table 2, >> Column for Row 2 of Table 2, >> . >> . >> . >> Column for Row N of Table N, >> >> Access Table "myTable" has some rows like >> >> Column for Row 1 of Table 1 contains Row 1 Data of Table 1 >> Column for Row 2 of Table 1,contains Row 2 Data of Table 1 >> Column for Row 3 of Table 1,contains Row 3 Data of Table 1 >> Column for Row 4 of Table 1,contains Row 4 Data of Table 1 >> Column for Row 5 of Table 1,contains Row 5 Data of Table 1 >> Column for Row 1 of Table 2,contains Row 6 Data of Table 1 ' which is >> not correct >> Column for Row 2 of Table 2,contains Row 1 Data of Table 2 ' which is >> not correct >> >> >> 1. Is exporting the Access table data into MS-Excel, adding a column >> to the Access table for the sixth row and then reimporting the data >> back from Excel an option or are there better alternatives? >> >> 2. Also, there are some characters which appear as question marks in >> the Access table cells. If a column in the Word table had a blank >> space before the sentence started, the corresponding access table cell >> has a question mark character. I know I should have added some >> filtering in the VBA code before storing it in a Access table, but as >> I did not is there a quick way I can remove the question mark >> characters? Can I run some VBA which will traverse all columns of all >> rows of the Access table and remove the question mark character? >> >> I am using Access 2007. >> >> Any advice would be welcome. >> >> > Your problem is vague to me. So my response could easily be incorrect or > does not address your needs. I will try explaining it again. I moved data from Word document tables into a Access Table, but some tables in the Word document had more rows than fields allocated to them in the Access table so they flowed into the field reserved for next Word Table. For e.g. lets assume my word document has two tables, Table 1 and Table 2 each having five rows. I want data from each of the five rows to be stored in a Access table(we can call it Dbtable) which has a column for each row of the table in the Word document so it has 10 columns/fields. I did that which was fine. But, there is another word document whose Table 1 has 6 rows instead of five so data from the sixth row of that table is flowing into a column 6 of Dbtable. Column 6 of Dbtable is for storing data from first row of Table 2 not for sixth row of table 1. I can increase the number of columns by doing a alter table as you suggest, but for word documents having Table 1 and Table 2 having five rows, there would be an extra column which means data from row 1 of Table 2 of the Word document will go into column 6(which is created for handling Word document whose Table 1 has 6 rows). How can i fix this issue? Another problem is when I imported data from a Word table into a field in a Access table(Dbtable) if the data in the Word table cell had a blank space before the text in it, now there is a question mark character present in the Access table cell. How can I fix this? This is similar to running some VBA code for all columns of all records and removing the leading and trailing blank spaces present in the columns. > This link may offer some iterest to you > http://msdn.microsoft.com/en-us/library/aa537149%28office.11%29.aspx#officewordautomatingtablesdata_extractingdatafromatable > > Look for topic "Extracting Data from Word Tables Programmatically" at > the link. > > You could do something like > intFldCount = currentdb.TableDefs("YourTableName").Fields.Count > to get the field count of a table. > > You could compare the counts of elements of the array from the link above > Ubound(array) + 1 > and compare to intFldCount to determine if you have enough columns. > > Maybe you want to alter the table. > http://www.blueclaw-db.com/alter_table_ddl.htm > Thanks for the links and your time. I hope my question is a bit more clearer now and you can guide me in the proper direction.
From: g on 2 Jul 2010 20:49 On 7/2/2010 8:04 PM, g wrote: > On 7/2/2010 7:17 PM, Salad wrote: > Another problem is when I imported data from a Word table into a field > in a Access table(Dbtable) if the data in the Word table cell had a > blank space before the text in it, now there is a question mark > character present in the Access table cell. How can I fix this? This is > similar to running some VBA code for all columns of all records and > removing the leading and trailing blank spaces present in the columns. This got solved. I needed to add a Replace(String, Chr(13), "") in the VBA which was moving data from the Word table to the Access table. And, now there are no question mark characters in the Access table. The leading and trailing blank spaces present in the Word table cells were carriage returns before and after the data.
From: Salad on 2 Jul 2010 21:30 g wrote: > On 7/2/2010 7:17 PM, Salad wrote: > >> g wrote: >> >>> >>> I moved data from a MS-Word Table into a MS-Access table using >>> VBA code. Almost all of the word documents had five rows for first >>> table in the document, but few Word documents had six rows for their >>> first table which I did not know earlier. I created the Access table >>> with five columns to hold data from each corresponding row of the >>> first table of the Word document. Now, the documents which had six >>> rows in their first table have their data flowing into sixth column of >>> the Access table which was for storing data of table 2 from the Word >>> document. >>> >>> I had something like Word Document A which has table 1 having five rows >>> >>> Table 1 >>> >>> Row 1 Row 1 Data >>> Row 2 Row 2 Data >>> . >>> . >>> Row 5 Row 5 Data >>> >>> >>> But, Word Document B has a table 1 having six rows >>> >>> Table 1 >>> >>> Row 1 Row 1 Data >>> Row 2 Row 2 Data >>> . >>> . >>> Row 5 Row 5 Data >>> Row 6 Row 6 Data >>> >>> Access Table "mytable" has a structure like >>> >>> Column for Row 1 of Table 1, >>> Column for Row 2 of Table 1, >>> Column for Row 3 of Table 1, >>> Column for Row 4 of Table 1, >>> Column for Row 5 of Table 1, >>> Column for Row 1 of Table 2, >>> Column for Row 2 of Table 2, >>> . >>> . >>> . >>> Column for Row N of Table N, >>> >>> Access Table "myTable" has some rows like >>> >>> Column for Row 1 of Table 1 contains Row 1 Data of Table 1 >>> Column for Row 2 of Table 1,contains Row 2 Data of Table 1 >>> Column for Row 3 of Table 1,contains Row 3 Data of Table 1 >>> Column for Row 4 of Table 1,contains Row 4 Data of Table 1 >>> Column for Row 5 of Table 1,contains Row 5 Data of Table 1 >>> Column for Row 1 of Table 2,contains Row 6 Data of Table 1 ' which is >>> not correct >>> Column for Row 2 of Table 2,contains Row 1 Data of Table 2 ' which is >>> not correct >>> >>> >>> 1. Is exporting the Access table data into MS-Excel, adding a column >>> to the Access table for the sixth row and then reimporting the data >>> back from Excel an option or are there better alternatives? >>> >>> 2. Also, there are some characters which appear as question marks in >>> the Access table cells. If a column in the Word table had a blank >>> space before the sentence started, the corresponding access table cell >>> has a question mark character. I know I should have added some >>> filtering in the VBA code before storing it in a Access table, but as >>> I did not is there a quick way I can remove the question mark >>> characters? Can I run some VBA which will traverse all columns of all >>> rows of the Access table and remove the question mark character? >>> >>> I am using Access 2007. >>> >>> Any advice would be welcome. >>> >>> >> Your problem is vague to me. So my response could easily be incorrect or >> does not address your needs. > > > I will try explaining it again. I moved data from Word document tables > into a Access Table, but some tables in the Word document had more rows > than fields allocated to them in the Access table so they flowed into > the field reserved for next Word Table. > > For e.g. lets assume my word document has two tables, Table 1 and Table > 2 each having five rows. I want data from each of the five rows to be > stored in a Access table(we can call it Dbtable) which has a column for > each row of the table in the Word document so it has 10 columns/fields. > I did that which was fine. But, there is another word document whose > Table 1 has 6 rows instead of five so data from the sixth row of that > table is flowing into a column 6 of Dbtable. Column 6 of Dbtable is for > storing data from first row of Table 2 not for sixth row of table 1. I > can increase the number of columns by doing a alter table as you > suggest, but for word documents having Table 1 and Table 2 having five > rows, there would be an extra column which means data from row 1 of > Table 2 of the Word document will go into column 6(which is created for > handling Word document whose Table 1 has 6 rows). How can i fix this issue? > > Another problem is when I imported data from a Word table into a field > in a Access table(Dbtable) if the data in the Word table cell had a > blank space before the text in it, now there is a question mark > character present in the Access table cell. How can I fix this? This is > similar to running some VBA code for all columns of all records and > removing the leading and trailing blank spaces present in the columns. > > >> This link may offer some iterest to you >> http://msdn.microsoft.com/en-us/library/aa537149%28office.11%29.aspx#officewordautomatingtablesdata_extractingdatafromatable >> >> >> Look for topic "Extracting Data from Word Tables Programmatically" at >> the link. >> >> You could do something like >> intFldCount = currentdb.TableDefs("YourTableName").Fields.Count >> to get the field count of a table. >> >> You could compare the counts of elements of the array from the link above >> Ubound(array) + 1 >> and compare to intFldCount to determine if you have enough columns. >> >> Maybe you want to alter the table. >> http://www.blueclaw-db.com/alter_table_ddl.htm >> > > Thanks for the links and your time. > > I hope my question is a bit more clearer now and you can guide me in the > proper direction. > > No. For me, a table consists of fields. In a datasheet display, the fields are the columns, the rows consist of a related set of fields. So if a table in word has 1 row or two rows of 5000 rows, it would make to difference to me...I'd simply add a record, update the fields, and update the table by committing the add. Is your word table 1 column? And each row of the word table is a column in a database field? I don't know. Did you read the link I presented? Have you used the Split() function before?
|
Next
|
Last
Pages: 1 2 Prev: Max Date in a query whilst showing all fields Next: Combo Box Sorting Issue |