Posts by 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.



    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...

    Code
    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