Posts by the_sleeper

  • Excel and Lotus Notes

    Hi All,


    One of the biggest issues I am having is producing VBA code to email the current workbook as an attachment in LOTUS NOTES. I know you can reference the object, but I don't know how to actually get it started. I can't use the application.sendmail method because it uses the useless Microsoft Exchange app (I need to send it via Lotus Notes)


    Any assistance would be appreciated


    Thanks


    DW

  • Getting Filename from full file path

    I am running a custom function in VBA that returns a list of full path's of files in a folder, such as:


    C:\foldername01\foldername02\foldername03\workbook.xls


    I then add this list of paths to a listbox, but i want only the last part to appear in the listbox, i.e.


    workbook.xls


    Not that other information


    Is there any way to do this (Isuppose combining the right, mid and find functions)??????


    Thanks


    DW

  • Updating Links

    Note:


    The asktoupdatelilnks method doesnt work on the workook open event becaseu the update links dialog box is loaded before the workbook open event.


    Furthermore, using this will automatically update links, and I want to NOT update the links.


    The problem I am having is that some links are to files in restricted directories on our network, so updating links will bring up the 'locate' links dialog (which I don't want). I can then develop code based on the username to update links the non-restricted files.


    Any thoughts??

  • Updating Links

    Is there any way when opening a workbook to not update links. I know of the 'asktoupdatelinks' method but that will update the links automatically.


    Is there any way of not showing the message to update links and then NOT update them upon opening??


    Thanks


    DW

  • Formulas and external references

    thanks for that guys


    The only way around it is to have another sheet setup in the workbook that contains the formulas and link to each cell in the external workbook, then refer the formulas to this other sheet


    formula->external workbook


    formula->sheet ->external workbook


    Thanks a bundle


    That explains a lot


    DW

  • Formulas and external references

    With more investigation I have found that this error only occurs with any formula that has a condition in it (ie countif, sumif, vlookup, etc etc) BUT NOT WITH COUNT, COUNTA, SUM etc


    Is there any way to amend this through VBA or whatever


    TA


    DW

  • Formulas and external references

    Hey All,


    I am constructing a workbook that is linked to various other workbooks on our network and have come across a particular issue regarding the external references


    I enter the following formula into a cell:


    =COUNTIF('G:\FolderName\[Orders.xls]Sheet1'!$AJ:$AJ,"WEB")/D21


    and the result is #VALUE!


    Further to this the formula in cell D21 refers to the same workbook (but is just a plain count function)


    =COUNT('G:\FolderName\[Orders.xls]Sheet1'!$B:$B)


    and does NOT return an error but the result of the formula



    ALSO......when I open the other workbook the errors change to results of formulas (weird???)


    I have updated the links when I open the workbook and I see there is no reason why a result would be achieved from one formula and not the other (except perhaps that the workbook needs to be opened for the formula to calculate - which is really silly)


    Thanks


    DW

  • OLE Object

    Hans,


    Sorry bout the inaccurate post...


    I wast to control Word from Excel. I have no problems creating the object and then transferring data between applications however if I want to refer back to the object in another procedure I ahve to open it again after I have saved it in another procedure. Is ther any way of being able to access the same object (globally perhaps) without having to reopen it (ie keep it open for all prcedures)


    Thanks

  • OLE Object

    I was wondering f soemone could give me some advice on OLE Objects with VB/VBA


    I'm am just starting to learn OLE coding and creating Objects and such and I have come across a few issues I hope could be resolved.


    I am trying to create an interface that connects to Excel, modifies an existing worksheet in a already dreated workbook and then returns the results in the controls on the form.


    I have tried creating and setting up the object in the Load_Form event similar to below


    Dim exlApp As Word.Application
    Dim exlDoc As Word.Document


    Set exlApp = CreateObject("Excel.Application")


    Set exlDoc = exlApp.Documents.Open("C:\Foldername\Filename.xls")


    but then I don't know how to then access this object from other procedures (ie other events or subs)


    I hope this is relevant and any feedback or advice would be highly appreciated


    Regards