Replies

Cool. Thanks, guys.

Cool. Thanks, guys.

Whoops! Found that I could return the array by using the Advanced Filter and choosing unique items. But could you write a custom function in VBA or an Array function to return those unique items?

Whoops! Found that I could return the array by using the Advanced Filter and choosing unique items. But could you write a custom function in VBA or an Array function to return those unique items?

Thanks, Hans. I’ll take look.

Thanks, Hans. I’ll take look.

Hi Hans,

Good to hear from you. Eventually I would need to know both. You can get that information from Format Menu/Tabs. Let me explain what I’m trying to do.

We have a Word document that looks like it is in tab-delimited format, but not quite. This file was arranged using tabs, but it didn’t include a tab where a null value occurred, it just moved the tab over to the next column! Hence, if you try to copy/paste and convert text to table and so on, the information doesn’t align properly. Where a null value occurs, the data shifts to the left.

The only solution I have right now is to create a table and place the information in the corresponding columns. I would do this by first building an array that has all the tab stop positions from the very first row, since no null values exist. Then loop through the data and copy information between the tabs and paste in the appropriate cells. I would have the macro compare the current tab stop position to the position that I’m expecting in the array, which would be incremented by the loop. If the current tab position is greater than the tab position in the array, I know I have a null value. I would then move over one cell within the table and increment the array by one. At least, it sounds plausible.

So to take your example, let’s pretend that I’m showing marks on the page, an left tab on the ruler is the first row “L”, a “T” represents a tab, and a “-” represents a space the tab is extending. (There are no spaces) . And the words in parentheses is the information to be copied. The information would like this:

L L L L
-T(store)–T(Name)–T(IG)–T(AW)
-T(18054)-T(Bob)————-T(Y)

What you would get is this:

Store Name IG AW
18054 Bob Y

Which is incorrect!

I’ve included a sample. If you can figure out a different solution or how to find the actual tab position of the currently selected tab, that would be fantastic!

Thanks,

Hi Hans,

Good to hear from you. Eventually I would need to know both. You can get that information from Format Menu/Tabs. Let me explain what I’m trying to do.

We have a Word document that looks like it is in tab-delimited format, but not quite. This file was arranged using tabs, but it didn’t include a tab where a null value occurred, it just moved the tab over to the next column! Hence, if you try to copy/paste and convert text to table and so on, the information doesn’t align properly. Where a null value occurs, the data shifts to the left.

The only solution I have right now is to create a table and place the information in the corresponding columns. I would do this by first building an array that has all the tab stop positions from the very first row, since no null values exist. Then loop through the data and copy information between the tabs and paste in the appropriate cells. I would have the macro compare the current tab stop position to the position that I’m expecting in the array, which would be incremented by the loop. If the current tab position is greater than the tab position in the array, I know I have a null value. I would then move over one cell within the table and increment the array by one. At least, it sounds plausible.

So to take your example, let’s pretend that I’m showing marks on the page, an left tab on the ruler is the first row “L”, a “T” represents a tab, and a “-” represents a space the tab is extending. (There are no spaces) . And the words in parentheses is the information to be copied. The information would like this:

L L L L
-T(store)–T(Name)–T(IG)–T(AW)
-T(18054)-T(Bob)————-T(Y)

What you would get is this:

Store Name IG AW
18054 Bob Y

Which is incorrect!

I’ve included a sample. If you can figure out a different solution or how to find the actual tab position of the currently selected tab, that would be fantastic!

Thanks,

Andrew,

Your second post gave me hope! However, I noticed that position returned is not the same as the tab position. For instance, it looks like using:

MsgBox PointsToInches(Selection.Information(wdHorizontalPositionRelativeToPage))

Finds the beginning of the selection, whereas I will need to know where the tab is set.

Am I wearing out my welcome?LOL

Andrew,

Your second post gave me hope! However, I noticed that position returned is not the same as the tab position. For instance, it looks like using:

MsgBox PointsToInches(Selection.Information(wdHorizontalPositionRelativeToPage))

Finds the beginning of the selection, whereas I will need to know where the tab is set.

Am I wearing out my welcome?LOL

Thanks for your speedy response!

However, while the tab stops may be indexed, I need to know the position of the CURRENTLY selected tab, not the tab in the first position. I want to use this in conjunction with a find/execute method or MoveUntil method.

Thanks in advance for any assistance. And as always, you guys are awesome.

Best regards,

Thanks for your speedy response!

However, while the tab stops may be indexed, I need to know the position of the CURRENTLY selected tab, not the tab in the first position. I want to use this in conjunction with a find/execute method or MoveUntil method.

Thanks in advance for any assistance. And as always, you guys are awesome.

Best regards,

Correct. I won’t talk about VBA or about using the VBA Editor until possibly another class. (Up to the customer.) There is only material in the book to cover recording a macro and creating custom buttons and toolbars, but there are no examples or labs. I’ll probably use some simple print macros and the draft “watermark”. Thanks for the help.

Correct. I won’t talk about VBA or about using the VBA Editor until possibly another class. (Up to the customer.) There is only material in the book to cover recording a macro and creating custom buttons and toolbars, but there are no examples or labs. I’ll probably use some simple print macros and the draft “watermark”. Thanks for the help.

I like both ideas. Charlotte, thanks for the demo. Very Clever way of not having to recreate common command buttons over and over again. Mark, wonderful idea of using the subform name + number as an easy way to not hard code the names themselves. Brillant. Right now, I think I will try out Mark’s idea and maybe both of your ideas into my own database. Thanks, again.