AnsweredAssumed Answered

Export Script To Excel Runs Slow

Question asked by info@seaqconsulting.com.au on Sep 16, 2018
Latest reply on Sep 18, 2018 by info@seaqconsulting.com.au

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

Next

Application.WindowState = xlMinimized

Application.EnableCancelKey = xlDisabled

MyErrorHandler:

 

If Err.Number = 1004 Then

    MsgBox "Stop hitting ctrl + break !!!"

    Resume

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

    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

    w.Save

Next w

    Application.WindowState = xlMinimized

    Range("B4:J109").Select

    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?

Outcomes