Posts by novice_user

Quote from rory

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

Quote from rory

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?

file attached.

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

Quote from royUK

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:

    Exit Sub
errorhandler1n:
    Err.Clear
    ThisWorkbook.Sheets("instructions").Cells(i, 7) = "no SAP data"
    Resume sap3n

errorhandler5n:
        Err.Clear
        ThisWorkbook.Sheets("instructions").Cells(i, 8) = "no SAP data"
        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:

On Error GoTo errorhandler1n:

   'sap scripting for fbl1n

Exit Sub
errorhandler1n:
  Err.Clear
  ThisWorkbook.Sheets("instructions").Cells(i, 7) = "no SAP data"
  Resume sap3n

sap3n:

On Error GoTo errorhandler5n

  'sap scripting for fbl5n

'same error handler as above

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:

On Error GoTo errorhandler1n:

  'sap scripting

Exit Sub
errorhandler1n:
ThisWorkbook.Sheets("instructions").Cells(i, 7) = "no SAP data"

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?

Sub Create_Le()

Application.DisplayAlerts = False


Dim i As Integer
Dim year As String
Dim per As String
Dim wb As Workbook
Dim le As String
Dim template As Workbook
Dim xFd As String



i = 1

Do Until IsEmpty(Sheet1.Cells(i, 3))
  
  
  With template
    Set template = Workbooks.Open("L:\FASE\2000_Account To Report\11000 A2R Reorg\GA - Leasing, Tax & OIE\6 OIE Reconciliations\TEST MACRO\New files\OIE reconciliation Template.xlsm")
    
     
         'what we change    'what we make it equal too
     
          'company code
          Worksheets("Cover Sheet_pdf").Cells(11, 7) = Sheet1.Cells(i, 3)
          'year
          Worksheets("Cover Sheet_pdf").Cells(8, 7) = Sheet1.Cells(1, 1)
          'quarter
          Worksheets("Cover Sheet_pdf").Cells(9, 7) = Sheet1.Cells(2, 1)
  
 End With
   
le = Worksheets("Cover Sheet_pdf").Cells(11, 7)
year = Worksheets("Cover Sheet_pdf").Cells(8, 7)
per = Worksheets("Cover Sheet_pdf").Cells(9, 7)
wbname = le & " OIE reconciliation Q" & per & " " & year & ".xlsm"

xFd = "L:\FASE\2000_Account To Report\11000 A2R Reorg\GA - Leasing, Tax & OIE\6 OIE Reconciliations\TEST MACRO\New files\Margo"
ActiveWorkbook.SaveAs Filename:=xFd & "\" & wbname, FileFormat:=52

ActiveWorkbook.Close

i = i + 1
Loop

End Sub

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:

Sub Create_Le()

Application.EnableAnimations = False
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Dim i As Integer
Dim year As String
Dim per As String
Dim wb As Workbook
Dim le As String
Dim template As Workbook
Dim xFd As FileDialog



i = 1

Do Until IsEmpty(Sheet1.Cells(i, 3))
  
  
  With template
    Set template = Workbooks.Open("L:\FASE\OIE reconciliation Template.xlsm")
    
     
         'what we change    'what we make it equal too
     
          'company code
          Worksheets("Cover Sheet_pdf").Cells(11, 7) = Sheet1.Cells(i, 3)
          'year
          Worksheets("Cover Sheet_pdf").Cells(8, 7) = Sheet1.Cells(1, 1)
          'quarter
          Worksheets("Cover Sheet_pdf").Cells(9, 7) = Sheet1.Cells(2, 1)
  
 End With
   
le = Worksheets("Cover Sheet_pdf").Cells(11, 7)
year = Worksheets("Cover Sheet_pdf").Cells(8, 7)
per = Worksheets("Cover Sheet_pdf").Cells(9, 7)
wbname = le & " OIE reconciliation Q" & per & " " & year & ".xlsm"

Set xFd = Application.FileDialog(msoFileDialogFolderPicker)

ActiveWorkbook.SaveAs Filename:=xFd & "\" & wbname, FileFormat:=52

ActiveWorkbook.Close

i = i + 1
Loop

End Sub

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

Quote from dangelor

Could you not just run a find/replace to turn those lines into comments...

...and then reverse it when you're done?

Thank you for your input! As this file will be run monthly (for approx 50 files), the find & replace method would require too much work & would be easier to remove the messages completely! Thanks for the input though!:)

Quote from rory

No, you pass False as an argument:

Code

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

for example.

Hi Rory,

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

If pBln_EnableFinalMsg Then
MsgBox "KE5Z data were downloaded without incident."
End If

And this is defined as:

Function FGetSAPDataZREP18_Bln(ByVal pBln_EnableFinalMsg As Boolean _
                                    ) As Boolean

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

 Sub SCallGetSAPDataZREP18()
        Dim lVar_Calculation As Variant
            lVar_Calculation = FInitializeFunction_Var
            gStr_CallingFunction = "SCallGetSAPDataZREP18"
            gStr_CallingFunctionPart = "Main Code"
            
                If Not FSetGlobalVariables_Bln Then GoTo Finally
                Call FGetSAPDataZREP18_Bln(True)
Finally:
            STerminateFunction (lVar_Calculation)
    End Sub

Is there a way to set the the

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

Quote from rory

No, you pass False as an argument:

Code

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

for example.

Ahh that is amazing! Thank you!! I will edit & let you know how it goes! thanks so much!

Quote from rory

I'd suggest you just amend the declaration of the macros that are run so they look like:

Code

Sub MacroName(Optional ShowMessages As Boolean = True)

then amend the relevant msgbox lines so that they start with:

Code

If ShowMessages Then

Then when you want to disable the messages from the calling code, simply pass the optional argument as False.

Display More

Ahh great thank you. For example:

     Sub SCallGetSAPData(Optional ShowMessages As Boolean = True)
        Dim lVar_Calculation As Variant
            lVar_Calculation = FInitializeFunction_Var
            gStr_CallingFunction = "SCallGetSAPData"
            gStr_CallingFunctionPart = "Main Code"
            
                If Not FSetGlobalVariables_Bln Then GoTo Finally
                If Not FGetSAPDataKE5Z_Bln(False, All) Then GoTo Finally
                If Not FGetSAPDataZREP18_Bln(False) Then GoTo Finally
                 
                If ShowMessages Then MsgBox "SAP data were downloaded without incident."
Finally:
            STerminateFunction (lVar_Calculation)
    End Sub

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

Private Sub LoopThroughFiles()

Application.EnableAnimations = False
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Dim ShowMessages as Boolean
    Dim ws As Worksheet
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.xlsm*")
        Do While xFileName <> ""
            With Workbooks.Open(xFdItem & xFileName)
            Set ws = ActiveWorkbook.Sheets("Overview")
            Set showMessages = False
                ws.Cells(1, 4) = Sheet1.Range("a1")
                Application.Run "'" & xFileName & "'!SAP"
                Application.Run "'" & xFileName & "'!PlayAllWithoutMsg"
                .Save
                .Close
                  
            End With
            xFileName = Dir
        Loop
    End If
    
Application.EnableAnimations = True
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

MsgBox "All files successfully updated"

End Sub

Quote from royUK

Attach an an example file that runs the main code and one of the files it opens. It doesn't need any confidential data

One of the files it opens contains a lot of code which I don't believe is confidential but the file is pretty complex so it may have more info than I know. Is it possible to delete a thread once resolved?

Quote from royUK

The only way that I can think of is to add a boolean variable in each workbook that confirms the msgbox runs. This would be set to False from the code above.

Could you please explain further how to do this ?

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)

Private Sub LoopThroughFiles()

Application.EnableAnimations = False
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Dim ws As Worksheet
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.xlsm*")
        Do While xFileName <> ""
            With Workbooks.Open(xFdItem & xFileName)
            Set ws = ActiveWorkbook.Sheets("Overview")
            
                ws.Cells(1, 4) = Sheet1.Range("a1")
                Application.Run "'" & xFileName & "'!SAP"
                Application.Run "'" & xFileName & "'!PlayAll"
                .Save
                .Close
                  
            End With
            xFileName = Dir
        Loop
    End If
    
Application.EnableAnimations = True
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

MsgBox "All files successfully updated"

End Sub

resolved!

Quote from rory

Glad to help.

PS It's Rory, not Roy.

hahah oops!! Sorry Rory*** have a nice day!

Ah Roy this is amazing!! It's working & I am made up! a 3h task (being generous)--> 22 mins! I love VBA!