Posts by Phil

Re: Copy all text from word doc into merged cells

No takers???

Thought I'd give it another try and bounce it back to the top.

TIA,

Phil

Can’t seem to find a solution…I’m using Application.GetOpenFilename to open a word document but from there I want to copy all the text from that document into the activecell of the Excel sheet that I’m currently in.

I found a post that find’s a certain table in a doc (document) and moves that into the sheet but nothing on getting the entire text.

Stumped…Can anyone help?

Thanks,

Phil

Re: Importing word document into workbook

With the help of Norie the below is what I came up with. It works quite well to add a word document into an excel file as a seperate sheet.

Sub Add_Word_Document()
    Application.ScreenUpdating = False
    Dim MyFile
    Sheets.Add After:=Worksheets(Worksheets.Count)
    With ActiveWindow
        .DisplayGridlines = False
        .DisplayHeadings = False
        .DisplayOutline = False
        .DisplayZeros = False
    End With
    MyFile = Application.GetOpenFilename(FileFilter:="Microsoft Word Files (*.doc),*.doc,", FilterIndex:=5, _
         Title:="Select the Word File that you would like to add to this file")
    ActiveSheet.OLEObjects.Add(Filename:=(MyFile), Link:=False, DisplayAsIcon:=False).Select
    MyFile = Left(MyFile, Len(MyFile) - 4)
    MyFile = Right(MyFile, Len(MyFile) - Len((Left(MyFile, InStrRev(MyFile, "\")))))
    ActiveSheet.Name = InputBox("Enter what you would like to name this sheet.", "Sheet Name", (MyFile))
    Range("A1").Select
    Application.ScreenUpdating = True
End Sub

Thanks for the help Norie...

Re: Importing word document into workbook

I've tried to record the macro and I can get it to record the inserting of a word object. However, it does not record the insert file operation or dialog.

I've used...

Application.Dialogs(xlDialogInsertPicture).Show

...to access an insert picture dialog but I cannot adapt this for a word file.

Any other suggestions. I'm sure that someone has done this before, I just can't find it.

Phil

I've searched several forums and can't seem to find this answer.

I'm trying to import a word document into a new sheet within a workbook.

Upon running, the routine would call up a dialog box that is similar to a file open box. This would display only files with a .doc extension.

Upon navigating to and selecting the desired file the document would imbed itself into the new excel sheet within the workbook.

Might some have a bit of code to achieve the preceding?

Thanks in advance for any help.

Phil

Re: A userform based Calender to COPY into your App!

Just to offer alternatives.

The attached file is the latest version of the calendar that I originally built in Feb 2003. Many suggestions from this board were incorporated to make it what it is today.

The latest functionality is when one selects a date that falls in the preceding or proceeding months the calendar changes to that month.

Additionally, many of the international date issues were resolved.

Best regards to all,

Phil

Re: A userform based Calender to COPY into your App!

Just to offer alternatives.

The attached file is the latest version of the calendar that I originally built in Feb 2003. Many suggestions from this board were incorporated to make it what it is today.

The latest functionality is when one selects a date that falls in the preceding or proceeding months the calendar changes to that month.

Additionally, many of the international date issues were resolved.

Best regards to all,

Phil

Re: A userform based Calender to COPY into your App!

Just to offer alternatives.

The attached file is the latest version of the calendar that I originally built in Feb 2003. Many suggestions from this board were incorporated to make it what it is today.

The latest functionality is when one selects a date that falls in the preceding or proceeding months the calendar changes to that month.

Additionally, many of the international date issues were resolved.

Best regards to all,

Phil

Does anyone know of a way to automatically keep reenabling events?

I have many event handled or triggered routines. Problem is if any for whatever reason error out the event handler is left in the turned off or application.enableevents=false state.

I could place a button to reset but the average user isn't going to know to do that.

Re: Need help on using specialcells instead of a do loop?

Here's something you can try...it's untested but you can see how the specialcells operator can be used as a range method.

Personally, I don't like relying on users to enter dates properly. I'd probably create a listbox that populates with the months and let the user select the month they want. Or I'd use a date picker of some fashion.
[vba]Dim AllCells As Range, Cell As Range
Sheets("Current year detail").Select
Range("r12").Select
Calculate
rmonth = InputBox("Enter then month in Mmm format")
Set AllCells = ActiveSheet.SpecialCells(xlCellTypeAllFormatConditions)
For Each Cell In AllCells
If Cell.Interior.ColorIndex = 34 Then
Cell.FormulaR1C1 = "=SUM(RC[-12]:RC[" & (Format(rmonth, "m") - 19) & "])"
End If
Next Cell[/vba]

Edit - code tags added - jiuk

Re: Macro to it's own sheet

I'm using Activesheet where appropriate in the code.

The problem isn't really that. It's when I assign a macro to a button on the sheet. Then if I copy that sheet into another workbook the button/macro reference points to sheet(1) instead of the sheet that it's contained within.

Maybe there is another way to reference the macro or something else???

Thanks,

Phil

I have several custom written sub routines that I have placed in the worksheet instead of a module. I did this to hopefully have the sheet take these routines and use the routines after the sheet has been copied into another book.

Good theory but the buttons are now looking at sheet1 for the routines after a sheet is moved or copied into another book. It ends up giving an error 400.

Is there a way to assign the macro to a button and have it only look at that sheet?

TIA,

Phil

Re: Parse file name from file path with vba

Thanks Guys...your code was dead on.

For anyone interested this is what was created to delete all the Excel links in a workbook.

Private Sub DeleteLink()
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
ActiveWorkbook.ChangeLink Name:=(aLinks(i)), NewName:=(ActiveWorkbook.Name), Type:=xlExcelLinks
Next i
End If
End Sub

Thanks again,

Phil

I know this a simple line of code but have vapor lock on the brain...

I'm looking to parse out the file name from the file path string.
i.e...
Path=C:\Documents and Settings\johnsp\Desktop\TMS Master Files\Forms\DE_ADD.XLS

I want to extract the file name itself (DE_ADD.XLS) from the string.

TIA,

Phil

Re: Controls Toolbox appearing on userform

Well Barry, your absolutly right. Mine does have a multipage control on it and when I did as you mentioned the tools no longer appear.

Thanks for the tip.

PS...Enjoy those mountains this season. My sis lives in the springs (I used to live in Denver) and I can't wait to pay a visit and get some skiing in.

Take care,

Phil

Does anyone know why. When working in a userform (which was created using the vb editor) while in my workbook, if I hit the "Enter" key the Controls Toolbox appears.

I've been looking for the cause/solution of this for awhile but can't seem to find it.

TIA

Phil

Hey Derk...

Once again, your the greatest!!! Your solution worked great and I learned a lot in the process.

BTW...Your right about the question marks, it is misleading. I've been working with my users to not do things as that. In fact I'm going to write an event handler on that column that checks for anything not a number and kick it out (with message of course).

Again, thanks a million.

You have a great day.

Phil

Ya know Derk, I feel like an idiot.

First off, the formulas you suggested worked perfectly...Thank you so much. I don't use those functions much and do get a little lost.

Secondly, (this is the idiot part) I have been working the weight cells for some time now, which I just modified your original with the Lbs named range and I cannot seem to make it work. I keep trying but I thought I'd ask if you had any more ideas.

As with other that have passed you praises. I'm a believer, you got it going on. Thanks again.

Phil

Derk...

Attached is a copy of my challenge. I looked again at some of your previous examples and still struggling with the right direction.

Thanks again,

Phil

Derk...

I'm sure that would work and I have seen some of your previous threads of examples this method. However, I've struggled with the execution of how to do it.

Thank you for responding.