AnsweredAssumed Answered

Export Script To Excel Runs Slow

Question asked by on Sep 16, 2018
Latest reply on Sep 18, 2018 by

Hi Community

Can you please assist me with my current export script from Filemaker Pro to Excel it runs a bit slow (according to customers) but works great...

I use the Save Record As Excel, then use the Send Event Script, then Excel converts the report into a PDF

Because we have Interlab tests these hook up to the main report so I have to tell Excell to hide many rows I believe this is where the slow down is

Here below is a snippet of my hide rows VBA, the script parts in question is below in red text, I have to hide rows from 39 to 107 if B=Blank, is there a shorter method


Application.WindowState = xlMinimized

Application.EnableCancelKey = xlDisabled



If Err.Number = 1004 Then

    MsgBox "Stop hitting ctrl + break !!!"


End If

    Application.DisplayAlerts = False


    Dim strTerminateThis As String

    Dim objWMIcimv2 As Object

    Dim objProcess As Object

    Dim objList As Object

    Dim intError As Integer


    'Process to terminate

    strTerminateThis = "AcroRd32.exe"


    'Connect to CIMV2 Namespace

    Set objWMIcimv2 = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\.\root\cimv2")


    'Find the process to terminate

    Set objList = objWMIcimv2.ExecQuery("select * from win32_process where name='" & strTerminateThis & "'")


    'Terminates a process and all of its threads.

    For Each objProcess In objList

        intError = objProcess.Terminate

        On Error Resume Next


    Set objWMIcimv2 = Nothing

    Set objList = Nothing

    Set objProcess = Nothing


Dim ws As Worksheet

For Each ws In Worksheets

ws.PageSetup.LeftFooter = Worksheets("PSD Customer Report").Range("$M636").Value

ws.PageSetup.RightFooter = Worksheets("PSD Customer Report").Range("O636").Value


Next ws


    For Each w In Application.Workbooks


Next w

    Application.WindowState = xlMinimized


    ActiveSheet.PageSetup.PrintArea = Range("B4:J4", Range("j200").End(xlUp)).Address


    If Range("B39").Value = "" Then

       Rows("39:39").EntireRow.Hidden = True

      ElseIf Range("B39").Value >= "" Then

       Rows("39").EntireRow.Hidden = False

       End If

The above Hide Row formula is in my macro from Row 39 up to row Row 107 so that is 68 script steps, is there a shorter better way?


Works great just a bit slow on the export.....


Also just one last question, where the exported data comes from is an export layout, should the export data be placed on the layout going across the layout or downwards or does this not matter?