Posts by homegrownandy

    Its been a couple of years since I've done anything with VBA so I'm very rusty. I'm trying to improve some of our existing systems whilst i have some time to do so.


    I have code that will produce an invoice from the database, save it on the server and then attach it to an email.


    What I want to do now is for the code (where it adds attachments to email) to loop through and do this for all records in the record set (whilst also updating the date it happened so they are not on the future record set).

    To be clear, all records in the recordset should be put into one email.


    I will show the code that works for individual invoices/records. Ill also show my attempt at making it loop. The issue with the looping is; It will create multiple email windows and not add any attachments. Any ideas or advice would be appreciated.


    Working code for individual invoice:


    My attempt at making it loop:



    I have tried different variations and I'm pretty stuck for ideas at the moment.

    I have been using the slightly ammended code below which I found online. A problem with this is that the end dates are a day out.


    Reason being, all day events end at midnight. The time 00.00 registers as the next day. So thats the issue im looking to resolve at the moment.


    As a way for me to improve my understanding, what are: .subject .end .start .categories? What would I call these? So I can better search/understand my problem. A list of all these "properties?" would be greatbut I can't find any online.

    Thank you.


    Sorry, This will work, we do not need to check for if there is yes after deletion. we can just go to start. New problem: it will only copy one row over with this code. All others disappear.

    Please see attached. I have tried adding a goto event before the loop so when one is detected and deleted it would start again. No luck yet but thats the only idea I have. I'm out the office now till tomorrow but any questions or feedback let me know. :)


    Thanks. Jobs Tracking Test.xlsm


    for information: If you put Yes in column "I" on multiple rows one after the other. It will miss any consecutive rows. This is because it will be moved into the deleted rows space and not checked.

    This code does not work as intended. Once it deleted a row it looks to the next row to carry on with the code. BUT by deleting a row the next row has moved up one. So things can be missed when two rows meet the criteria one after the other.


    Its worth mentioning it on an old thread becausae this ranks quite highly in google for this type of search.


    Andy.

    Im running code in excel from access. So access does what it;s doing then calls excel to run a macro.


    This isn't causing too much issue at the moment but I dont think it's right. Excel remains in task manager wehn I have closed it from access.


    I have tested this using a simple message box with no issues at all (Ihe only code I changed in access for this was the macro that im calling). So the problem has to be within the code in excel.




    after reading on other posts/forums its because im not closing something here properly. Or referencing properly. Is anything obvious?


    Andy.

    Okay. thanks a lot for your suggestion. We now have a new problem. *note the code below is at the end of a very long piece of code. I have narrowed it down to juist this part as it's the only part dealing with excel. (also used breakpoints to clarify this section is causing issue).
    In acces when I run this code (in excel):

    Code
    xl.Run "ThisWorkbook.BetterExcelDataToWord"
    Exit Sub
    Error_Handle:
    MsgBox "Oops, an error has occured." & vbCrLf & vbCrLf & "Error Code : " & Err.Number & " , " & Err.Description
    End Sub


    I get the error 440 automation error. All the desired outcomes from the code are achieved and the error doesnt seem to be causing problem, but still I think it needs resolving..


    Any ideas on this one? ( I am researching online also).


    Thanks, Andy.


    Edit: Ive ran the code within excel seperatly and there is no error. So the error is within the small piece of code shown here.


    Apparently its a problem with the registry. Does this seem likely? Im doing this on a works network so it may be difficult to resolve if it is.

    I have the code shown below. this code is an in excel via a command in access. Access does not keep the formatting i want when pasting into a word table, so I have used excel as a "middle man". Everything works fine but the excel app is showing as closed on my computer. However; If i attempt to open this workbook it says its already in use by me. Or if i try to close the PC it says i need to save changes in this workbook.


    How can i properly close it via code once its completed the copy/paste task?


    thanks, Andy



    Waiting 20 seconds here does nothing. If i manually open the document before calling the macro in access the table does update.


    Code
    Dim Ticker As Range
     
     Application.Wait (Now + TimeValue("00:00:20"))
        Sheets("RISKS").Activate
        Set Ticker = Range(Cells(4, 1), Cells(65, 8))
        Ticker.Copy
    
    
        Sheets("Paste Special").Select
        Cells(4, 1).PasteSpecial xlPasteValues

    Im running a macro in excel externally (from access) using data from an access query.


    The macro copies a table of data and pastes it into word.


    When excel opens it will run the code before it has had time to refresh the data. Code shown here:



    Note: in the connection properties I have enabled "refresh data when opening file".

    I got carried away and finished it today. Works perfect,


    Thanks again


    Just a comment on over writing formulas, I was doing it after the paste so no issue there bar the speed.


    Your suggestion is a lot better.

    I havn't tested this but it seems thats what im looking for, It will take me a while to get all the references correct. I'll do that tomorrow.


    i did get it working using this:


    Code
    ActiveSheet.Range("A4" & ":H65").Select
        For Each Cell In Selection
            If Cell.Value = 0 Then
                Cell.Value = ""
            End If


    But it was very slow at deleting a page of 0's.


    Really appreciate your help. This was the final stage in a long project of mine. Happy to be near completion!


    Cheers, Andy.

    Dave - I'm unsure but unwilling to test that at the moment (It's taken weeks to get the paste into a word table working as I want). I could try this approach to put the rows I want onto a new sheet though.


    Johnathan - you are correct I had turned off show zeros. I have put them back on now.


    FYI: this is the formula in each cell in the table

    Code
    =INDEX('RISKS Import'!$A$1:'RISKS Import'!$H$77,K16,$N$2)


    Thanks, Andy

    Using the below code, i still get a page full of zeros instead of blanks.



    Is there anything obviously wrong there?


    Thanks, Andy