Posts by novice_user

    As I said, June and November have the same number of days, so that should work fine; only months with different numbers of days have issues.


    Will the dates always be the end of a month?

    Ahh okay that makes sense now, I have a solution thank you! I already have a lookup built in to check whether theres 31 or 30 days in the month so will incorporate this now to ensure it calcs the right days. I misinterpreted your initial comment.


    Thanks,


    Mel

    DATEDIF has always had problems with this calculation with months that have different numbers of days in them.

    Do you have any recommendation for a replacement formula? Its strange as Oct-Nov is calculating incorrectly but June-Nov is calculating the correct months or vise versa depending on the formula.


    Or, do you understand the reasons for these inconsistencies so I can try to formulate around it?

    Hi,


    I am using the DATEDIF formula & it is not considtent with the calculations. I am using it to calculate the amount of months passed, to calculate accruals & the following happens:


    =IF(P17="Accrual",DATEDIF(I17,($F$2),"M"),0)


    where I17 is the end date of last invoice & F2 is the current end of the month (i.e. this month is 30/11/2020)


    I have attached an image where you can see the calculation is wrong. Can someone please help explain why when the end date is Oct 2020 that in the month of Nov 2020, the accrual value is not 1?


    This formula works fine for an accrual which ended June 2020 (correctly calculated 5 months)


    Thanks,


    Mel

    I've moved the error handler to the end of the code. Exit sub is where the cod will finish after successfullu completing.


    The Resume will start the code at Sap3n

    Thats great thank you, I also have the error handler built in for the fbl5n download, so if i move the 2nd error handler below the first error handler like this:

    Code
    1. Exit Sub
    2. errorhandler1n:
    3. Err.Clear
    4. ThisWorkbook.Sheets("instructions").Cells(i, 7) = "no SAP data"
    5. Resume sap3n
    6. errorhandler5n:
    7. Err.Clear
    8. ThisWorkbook.Sheets("instructions").Cells(i, 8) = "no SAP data"
    9. Resume nextline

    Does this work? or do I need another exit sub in there?

    Hi Roy,


    Thank you, Sap3n is the following line. Please see code below:

    So I ran this & the code still stopped as it got to exit sub, is there any way for the code to continue but only exit sub when theres an error in SAP?I have attached my file for your reference, incase it helps.


    Thanks,

    Mel

    Hi,


    I am building in error handlers into my VBA file, for when extracting data from SAP where for certain accounts & countries there is no data. I found online to use the following:

    Code
    1. On Error GoTo errorhandler1n:
    2. 'sap scripting
    3. Exit Sub
    4. errorhandler1n:
    5. ThisWorkbook.Sheets("instructions").Cells(i, 7) = "no SAP data"
    6. GoTo sap3n

    However, when running this code, when the sap scripting is complete & the file is exported, the code stops because of the exit sub. Why is this & how do I correctly write the error handler? Can you also please explain why & the logic behind it.


    Thanks,

    Mel

    Sorry, so i have been investigating & I believe this happened when I was trying to open the template file & save with new info.


    So, I have a macro which had been edited & I want to use this across the 40 company codes I have. To reduce the manual work, I wrote the code below to open the template add in new info (company code) and save file. But in doing this, this is where the file seems to become corrupt & the download doesn't work/takes a while. Do you know if there's a reason why I can change the company code & save manually & it works but when i do it through VBA (the below code) it doesn't work?


    Hi,


    I am running a macro which will kick start the macro in multiple files, but whilst doing this it appears to get stuck here at the opening of the file. It says downloading for ever & prior to using this macro to connect & run all this didnt happen. Can anyone provide insight to why this happens & what I should do to stop it/speed it up?



    Kind regards,


    Mel

    Hi Everyone,


    So I recently noticed that when I open a file using Workbooks.Open method which has external connections, the connection becomes corrupt or breaks. Does anyone know why this happens/is there a way around it? I want to open the file with external connections, fill in some information & save as a new name & loop through a variable. I will place the code here:

    The code above works fine, except the fact it appears to break the connections.


    I could not find anything relevant online.


    Please help!


    Thank you in advance,


    Mel

    No, you pass False as an argument:


    Code
    1. Application.Run "'" & xFileName & "'!SAP", False


    for example.

    Hi Rory,


    After searching & messing around I found in the code the following:

    Code
    1. If pBln_EnableFinalMsg Then
    2. MsgBox "KE5Z data were downloaded without incident."
    3. End If

    And this is defined as:


    Code
    1. Function FGetSAPDataZREP18_Bln(ByVal pBln_EnableFinalMsg As Boolean _
    2. ) As Boolean

    And this relates to the code I am calling from my first book. I will be calling SCallGetSAPDataZREP18 (). Please see below:


    Is there a way to set the the

    Code
    1. pBln_EnableFinalMsg

    As false/not activate the message from my code in the book I want to run them all? I have tried searching to understand byVal & whether theres a way to deactivate this in my file but I am not sure.


    Thanks,


    Mel

    Ahh great thank you. For example:

    And in my file where I am trying to run all, I would set show messages = false?


    Hi All,


    I am writing a piece of code to open & run macros looping through the files, but every code run individually has a message box at the end for "code was ran without incident". I was wondering whether there is a way to disable this message box so the code can run through multiple files without having to click the space bar? I do not want to remove this message box, because when the individual runs the code in one file it is useful to have the message box there. Also, I have tried to copy the code & rewrite without the message box but this is someone else's code & it is very complex meaning it jumps a lot between different modules for different purposes so it would take a while to go through every sub & repeat but without a message (error prone too). So, I was wondering is there another alternative which I can add into the below code to automatically disable/close message boxes? (screen updating/displays do not work)