Posts by Bubbis Thedog

Thanks for the input, Roy and Shades! That ASAP site looks very helpful, by the way. I'm going to DL that Utility immediately. Why would that Add-In be free, you all think?

Bubbis Thedog

Thanks for the tip, JonG! That will help me narrow down the exact color that I want to use. I've documented that little tid-bit along with the others I learn on here :cool: .

Bubbis Thedog

Thomach, Dangelor: Both methods worked perfectly and will save me me a lot of time, as I have to shade in every row on two dozen tables (which would've been quite tedious and inefficient manually). Thank you both for helping me further enhance my knowledge of VBA.

Bubbis Thedog

Thanks for the list of patterns, Danglor. I'll definitely keep those for future use. Unfortunately, however, none of those are solid light gray. I suspect I need to insert a line(s) of code replacing the ".Pattern" line in order to do what I'm trying to do. I'll post back when I find out.

Bubbis Thedog

Hey there,

I've got the Macro from Microsoft's website to shade in every other row in an Excel worksheet, but it incorporates a pattern (xlGray16), and I need to shade the rows light gray. Can someone assist me in putting light gray into the following sub?

Sub ShadeEveryOtherRow()
    Dim Counter As Integer


    'For every row in the current selection...
    For Counter = 1 To Selection.Rows.Count
       'If the row is an odd number (within the selection)...
       If Counter Mod 2 = 1 Then
           'Set the pattern to xlGray16.
           Selection.Rows(Counter).Interior.Pattern = xlGray16
       End If
   Next


End Sub

Thanks so much to those who respond.

Bubbis Thedog

I think I've finally found out how to link high-level-of-data Excel sheets into Word documents. The default format in Word for Paste Special is HTML; I linked using Microsoft Excel Worksheet Object instead, and now it prints great in Word, and is not truncated (like it would be in HTML). Here's the process:

Copy the range of the Excel sheet --> Goto the Word document that the sheet is to be pasted in, and click Edit --> select Paste Special --> click the Paste link radio button --> select "Microsoft Excel Worksheet Object" --> click OK. Now the object in Word is linked, and there's no screwey printing or truncating (at least thus far :thumbcoo: ).

We'll see if this works in the long-term. I honestly can't believe that I couldn't find info on this on the Net (If it's there, it's hidden somewhere!).

Bubbis Thedog

Hey, when I Insert the Excel object --not as a link (embed)--, everything's pretty much fine. Only, I have about 20 of these tables, and sometimes during printing, one or two of the pages' (the page numbers differ) font is all screwy and the grid lines show up. I guess the memory (I have 256Meg, which I thought would be sufficient) gets bogged down or something.

Just thought I'd throw that out there for ya.

Bubbis Thedog

P.S. I've tried the Paste Special thing, too. However, upon startup, the Word document will lock up frequently using this method, I have found. Plus, I've found that you can't move the table how you want without sometimes --and I don't get this at all-- losing your links. I was moving around a Paste Special linked table yesterday, and when I tried to move the four-way arrow above the top margin, the table moved, but the links disappeared.

No, I have the vertical and horizontal centerings disabled.

I'm inserting the Excel object on a blank Word page, with the margins set at their maximums (.25 top, bottom, right, left). I've tried to resize the cell data such that they are only 7 pt. (and reduced the row height by 2), as opposed to the 9 pt. earlier, and when I try to link it on the blank Word page, it crops at line 44 (of the Excel worksheet). The object, when linked in Word, only takes up half the bloody page! I can't get Word to recognize past line 44, no matter :yikes: what :yikes: size I adjust my Excel fonts to, or what row height I choose. There are no spaces in the table either --nothing unusual at all, in fact.

I've done the Grouping thing: copying and pasting the cropped portion of the table to a blank workbook, save it, and then insert that below the cropped sheet in Word, and then Group them. That sort of works, but I have my data in the un-cropped Excel sheet linked to a workbook that the Home Office sends us that contains data that changes. My Excel sheet is linked to that ever-changing data; but the data that is cropped is not included in the Home Office's table, only mine. So, if I change something in the cropped area of the Excel table (the area with data not contained in the Home Office tables), it will not show up --unless I make the necessary change in the new workbook (containing the 'cropped' data), Ungroup the objects in Word, Update the link for the 'cropped' object, then Regroup the two. That's not exactly a process that I want to go through, and the possibility for error increases dramatically.

I'll keep you posted, and thanks for the continued effort, Dreamboat!

Bubbis Thedog

How about Window --> Unhide --> Save for the Excel workbook? That should certainly Unhide the workbook --if the screen is gray, of course (not blank, indicating that the Sheet is hidden). If you open the workbook, and then Unhide it, and then click to your Word link, and then close your Excel workbook without saving it, then the next time you return to the workbook it will remain hidden. This is the only possible scenario I can think of, unless there's a Macro in your Excel worksheet that tells it to hide it upon clicking the hyperlink. If the latter is the cause, there's some great VBAers on the Excel/VBA forum that might be able to help.

Bubbis Thedog

Thanks For the Advise...

Thanks for the advise, Dreamboat. I just cleared out the "Fit to" range in Page Setup, saved the file, and tried to link it in Word again. The bottom is cropped by a good 20 lines --even though, when I print the table from Excel, it fits the 8 1/2" x 11" page perfectly. I mean, I know Office has OLE limitations, but this is especially frustrating. I'm quite proficient in the Office environment, and I just hate it when things are out of my control because of technology constraints. Oh, and by the way, I even split-up this table (one page long originally) to cut the row amount in half, and Word is still cropping them . I've checked and rechecked all of the settings, and everything seems fine.

Anyway, thanks again for taking the time to post! If I find anything extraordinary out concerning this issue, I'll post it on here.

Bubbis Thedog

I've tried everything. My Excel worksheet, when I do a Print Preview, looks perfectly centered on the page. When I print from Excel, it looks fantastic. Then, I Insert --> Object... --> Create from File Tab --> check-mark 'Link to File' --> click 'Browse' --> Select the same Excel file that looks great in Excel --> press OK ==> the last 15 lines of the sheet are cropped. And the cropping can't be undone apparently.

I've done the Paste Special and Insert without check-marking Link to File, with Bitmap and HTML, and the sheet looks terrible when printed (the fonts are different, the gridlines show up).

Please don't tell me this is a Word quirk. Thanks to anyone in advance who can tell me how to fix this problem.

Bubbis Thedog

I'm new at this stuff, too, but I think it should read

Public Sub InputBoxMacro()

Range ("e1").Select
ActiveCell.Value = InputBox ("Please enter a date between "&Date1&" and "&Date2&"".)

End Sub

Hope that helps! :?

Bubbis Thedog

Well, I figured how to get it to work properly. But I still wonder why Excel automatically did that.

Bubbis Thedog

I have an Excel sheet with many rows and around 8 columns. The data within the cells are therefore very small (to still fit on one printed page), so they want me to split the table in half so that I can increase the font size. Ok, so I count the rows on the initial table, divide it by two, and cut the bottom half of the table and paste it into a new document. All fine and dandy... until... I cut the bottom values (in the lowest row of the sheet) from the initial sheet and paste them into the top row of the new sheet, Excel is changing those values in that row to the values in the first row on the initial page. Now, I suspect that this because of a default setting, but I have yet to find it. Here's an example:

Initial Sheet. .New Workbook Sheet (after pasting)

10 40 ............10 40 (should be "40 70")
20 50 ............50 80
30 60 ............60 90
40 70 ............70 100
(^^^this is what I'm cutting)

(Periods are in there to separate info from separate tables (each table in this example is 4 x 2))

Thanks in advance for any suggestions!

Bubbis Thedog

Cool Beans...

Thanks for your time, man. I figured it would be either difficult or impossible to incorporate programs outside of Office into a Macro. I'll just do it the save as .csv, import to Zetafax, save as .xls for now. Have a good one.

Bubbisthedog

Thanks, Derk...

Thanks for the feedback, Derk. I was just trying to make it so that a Macro could be run from a Excel worksheet, and then, voila!, that data was accessible for Zetafax importation --without having to save as .csv, then going opening Zetafax, then re-saving the Excel worksheet. Can a macro even be run to save Excel data to the Notepad (since Notepad is not part of Office)?

Thanks again for the assistance!

Bubbisthedog

Or Rather...

Maybe this is clearer:

Can I write a Macro that will import data from custom-width Excel columns into the Notepad (maybe just a cut/paste of the cell range that contains the data?)? I suppose once the Macro runs, and the data is transferred, the .txt file can be saved under the desired name. Any help whatsoever will be greatly appreciated.

Thanks again,

Bubbis Thedog

Hello, All.

I need to import Excel data located in a very simple, 4-column, 20-row worksheet into Zetafax, while maintaining a desired (not default) column width after closing the sheet. Zetafax only accepts character-delimited data; thus, I have to save this Excel worksheet as a comma-delimited file (.CMA, I think? I could be wrong on the extension filename.) before Zetafax can accept it. If I do not save the Excel worksheet under this filename, then Zetafax displays the error *could not import first row*. So then I save the Excel worksheet as a .CMA file, but after I close the worksheet and open it again, the column lengths return to default.

Now, I suspect that I can copy the Excel data on the clipboard, which should delimit the data (right???), and then Zetafax can import from the clipboard; therefore, the Excel file remains a .xls file and the custom grid widths will be maintained. Is my assertion correct here? My big question was --because I'm very new to VBA for Excel-- is there a Macro that accomplish this automatically?

Thanks for any help, folks; it's greatly appreciated in advance. And I will check in regularly to see if anyone's posted (so that I won't be one of those rude ones who post once and do not thank you all).

Bubbis Thedog