Export to Excel with Conditional Formatting (1 Viewer)

P

psyc0tic1

Access Moron
Local time
Today, 15:18
Joined
Jul 10, 2017
Messages
360
Cronk said:
That should be
Code: 
Public Function fnLastRow(sh As Object) On Error Resume Next         'With xlSheet
         with sh
....

And I presume you have set a reference in Tools | Reference to Excel
Click to expand...

I do have a reference to the Mocrosoft Excel 15.0 Object Library.

I changed the "With xlSheet" to "With sh" (without the quotation marks) and I am still getting the same error. There are several references to xlSheet in the button code as well... do they need some changing?
 
P

psyc0tic1

Access Moron
Local time
Today, 15:18
Joined
Jul 10, 2017
Messages
360
I have attached a screenshot of the button code with all of the references to xlSheet circled in black. The line circled in red is the one that gets highlighted when I get the error
 

Attachments

  • error3.jpg
    error3.jpg
    64 KB · Views: 132
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:18
Joined
May 7, 2009
Messages
19,247
Can you check the value of lngRow
 
P

psyc0tic1

Access Moron
Local time
Today, 15:18
Joined
Jul 10, 2017
Messages
360
I have searched but I cannot find anything about how to check the value of lngRow
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:18
Joined
May 7, 2009
Messages
19,247
There is a portion there in the code, debug. Print lngRow, you can see the result in immediatel window.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:18
Joined
May 7, 2009
Messages
19,247
ok, we'll revised the code.
find this line on our code:

lngRow = fnLastRow(xlSheet)




if you found it replace it with:

lngRow = .Cells(.Rows.Count, 1).End(-4162).Row 'xlUp is -4162
 
P

psyc0tic1

Access Moron
Local time
Today, 15:18
Joined
Jul 10, 2017
Messages
360
arnelgp said:
ok, we'll revised the code.
find this line on our code:

lngRow = fnLastRow(xlSheet)




if you found it replace it with:

lngRow = .Cells(.Rows.Count, 1).End(-4162).Row 'xlUp is -4162
Click to expand...

Received a totally different error.

how about we start over...

I now have this button code without a module
Code: 
Private Sub Advance_Waiting_on_Lab_Report_Click()

Const FileNameBase As String = "W:\Quality-Projects\RCabler\Databases\Weekly Reports\Waiting on Visual Weekly Report [CurrentDate].xlsx"
    Dim strFileName As String
    strFileName = Replace(FileNameBase, "[CurrentDate]", Format$(Date, "m-dd-yyyy"))
    
    If DCount("*", "AdvanceWaitVis") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "AdvanceWaitVis", strFileName, True, "Advance"
    End If
    If DCount("*", "ArcadiaWaitVis") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "ArcadiaWaitVis", strFileName, True, "Arcadia"
    End If
    If DCount("*", "EcruWaitVis") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "EcruWaitVis", strFileName, True, "Ecru"
    End If
    If DCount("*", "LeesportWaitVis") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "LeesportWaitVis", strFileName, True, "Leesport"
    End If
    If DCount("*", "RipleyWaitVis") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "RipleyWaitVis", strFileName, True, "Ripley"
    End If
    If DCount("*", "WanekWaitVis") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "WanekWaitVis", strFileName, True, "Wanek"
    End If
    If DCount("*", "WanvogWaitVis") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "WanvogWaitVis", strFileName, True, "Wanvog"
    End If


Dim oXLApp As Excel.Application
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet
 
  Set oXLApp = New Excel.Application
 
  oXLApp.Visible = True
 
  Set oXLApp = Nothing
  
End Sub

Nice simple button code that generates the file I need with todays date and generates multiple sheets for the different facilities if there is data in the records the query is asking for. This does not push table formatting.

Here is the macro code I recorded from Excel:
Code: 
Sub FormatF()
'
' FormatF Macro
'

'
    Columns("F:F").Select
    Cells.FormatConditions.Delete
    Columns("F:F").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=TODAY()-$F1>13"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Columns("F:F").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ISBLANK(F1)=TRUE"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .Pattern = xlNone
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
End Sub

Can this be added into the button code?
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:18
Joined
May 7, 2009
Messages
19,247
Post ur excel and I'll test it. The code I gave you is tested on my machine
 
P

psyc0tic1

Access Moron
Local time
Today, 15:18
Joined
Jul 10, 2017
Messages
360
My Excel? Excel2013

Did you mean my Access database? If so it is too big unless this site allows 40 mb attachments
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:18
Joined
May 7, 2009
Messages
19,247
The excel file, the one we are working on cf
 
P

psyc0tic1

Access Moron
Local time
Today, 15:18
Joined
Jul 10, 2017
Messages
360
arnelgp said:
The excel file, the one we are working on cf
Click to expand...

Here is the code line that highlets when it errors.
Code: 
rng .FormatConditions(xlObj.Selection.FormatConditions.Count) _
           .SetFirstPriority

the exported excel file is attached
 

Attachments

  • Waiting on Visual Weekly Report 9-27-2017.xlsx
    11.5 KB · Views: 107
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:18
Joined
May 7, 2009
Messages
19,247
here it is working now.

Code: 
    Dim xlWB As Object
    Dim xlObj As Object
    Dim xlSheet As Object
    Dim lngRow As Long
    
    Set xlObj = CreateObject("Excel.Application")
    Set xlWB = xlObj.Workbooks.Open(strFileName, False, False)
    
    For Each xlSheet In xlWB.WorkSheets
        
        With xlSheet
            
            .Activate
            'lngRow = fnLastRow(xlSheet)
            lngRow = .Cells(.Rows.Count, 1).End(-4162).Row 'xlUp
            Debug.Print lngRow
            .Range("F1:F" & lngRow).Select
            xlObj.Selection.FormatConditions.Add Type:=2, Formula1:= _
                    "=TODAY()-F1>13"
            xlObj.Selection.FormatConditions(xlObj.Selection.FormatConditions.Count).SetFirstPriority
            With xlObj.Selection.FormatConditions(1).Interior
                .PatternColorIndex = -4105
                .Color = 255
                .TintAndShade = 0
            End With
            xlObj.Selection.FormatConditions(1).StopIfTrue = False
            
        End With

    Next
    xlWB.Close True
    Set xlSheet = Nothing
    Set xlWB = Nothing
 
P

psyc0tic1

Access Moron
Local time
Today, 15:18
Joined
Jul 10, 2017
Messages
360
arnelgp,

you are totally awesome. That worked perfect. Thank you so very much.

While you were working on it I was also trying some things because there were more formatting things I needed to have happen... I just didn't want to start out too complicated.

Anyway... I did notice there was some differences in the export methods.

the DoCmd.Outputto method will export with formatting from the table perfectly but will not work for multiple sheets in one workbook.

The DoCmd.TransferSpreadsheet method will export WITHOUT table formatting but allows to have multiple sheets in one workbook.... however

The DoCmd.Outputto method is reading my "Part Number" field in the table correctly and outputs the actual part number.

The Do.TransferSpreadsheet method is outputting the ID number of the part numbers rather than the part number itself. I noticed the part number field in the Main Data table (Audit Data) of my database has a relationship with the ID field in the (Parts) table rather than the "Part Number" field.

Is there something in the query I can add to the "Part Number" to make it out put the actual part number rather than the ID of the part number?

Here is the SQL of one of the queries:
Code: 
SELECT [Audit Data].Facility, [Audit Data].Status, [Audit Data].[PO Number], [Audit Data].[Part Number], [Audit Data].[Total Received], [Audit Data].RecDate, [Audit Data].RecEntryDate
FROM [Audit Data]
WHERE ((([Audit Data].Facility)="17") AND (([Audit Data].Status)="Waiting on Visual Inspection"));
 
Last edited:
P

psyc0tic1

Access Moron
Local time
Today, 15:18
Joined
Jul 10, 2017
Messages
360
In an attempt to further this project I am trying to automate the export generation process and send the exports out in an email.

I have a form that has 3 buttons on it.

First button runs the code to export data to Excel
Second button runs the code to export different data to Excel
Third button generates the email, attaches the exports and sends to the chosen recipients.

The Excel exports were worked out in this thread... the email part was worked out in another thread.

The form works as expected. There is a delay in clicking the buttons as the exports take time each. The time they take is determined by network speed since the back end is stored on a server in another state than I am in.

So in an attempt to automate the exports and then the email I put the form code for each button in their own modules and then tried to call them from the on load event on my main form load.

First module named mod_WV:
Code: 
Option Compare Database

Public Function WaitVis()

Const FileNameBase As String = "\\site.com\Designated Folders\Weekly Reports\Waiting on Visual Weekly Report [CurrentDate].xlsx"
    Dim strFileName As String
    strFileName = Replace(FileNameBase, "[CurrentDate]", Format$(Date, "m-dd-yyyy"))

If DCount("*", "qry_advancewaitvis") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_advancewaitvis", strFileName, True, "Advance"
End If
If DCount("*", "qry_arcadiawaitvis") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_arcadiawaitvis", strFileName, True, "Arcadia"
End If
If DCount("*", "qry_ecruwaitvis") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_ecruwaitvis", strFileName, True, "Ecru"
End If
If DCount("*", "qry_leesportwaitvis") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_leesportwaitvis", strFileName, True, "Leesport"
End If
If DCount("*", "qry_ripleywaitvis") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_ripleywaitvis", strFileName, True, "Ripley"
End If
If DCount("*", "qry_wanekwaitvis") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_wanekwaitvis", strFileName, True, "Wanek"
End If
If DCount("*", "qry_whitehallwaitvis") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_whitehallwaitvis", strFileName, True, "Whitehall"
End If

Dim xlWB As Object
    Dim xlObj As Object
    Dim xlSheet As Object
    Dim lngRow As Long
    
    Set xlObj = CreateObject("Excel.Application")
    Set xlWB = xlObj.Workbooks.Open(strFileName, False, False)
    
    For Each xlSheet In xlWB.Worksheets
        
        With xlSheet
            
            .Activate
            lngRow = .Cells(.Rows.Count, 1).End(-4162).Row 'xlUp
            Debug.Print lngRow
            .Range("F1:F" & lngRow).Select
            xlObj.Selection.FormatConditions.Add Type:=2, Formula1:= _
                    "=TODAY()-F1>13"
            xlObj.Selection.FormatConditions(xlObj.Selection.FormatConditions.Count).SetFirstPriority
            With xlObj.Selection.FormatConditions(1).Interior
                .PatternColorIndex = -4105
                .Color = 255
                .TintAndShade = 0
            End With
            xlObj.Selection.FormatConditions(1).StopIfTrue = False
            .Range("A1:G1").Select
            With xlObj.Selection
                .HorizontalAlignment = xlLeft
                .VerticalAlignment = xlBottom
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ReadingOrder = xlContext
                .MergeCells = False
                
                With .Font
                    .Name = "Calibri"
                    .FontStyle = "Bold"
                    .Size = 11
                End With
                .Borders(xlDiagonalDown).LineStyle = xlNone
                .Borders(xlDiagonalUp).LineStyle = xlNone
                With .Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlInsideVertical)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                .Borders(xlInsideHorizontal).LineStyle = xlNone
                With .Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = -0.14996795556505
                    .PatternTintAndShade = 0
                End With
            End With
            .Columns("A:A").Select
            xlObj.Selection.ColumnWidth = 8.3
            .Columns("B:B").Select
            xlObj.Selection.ColumnWidth = 28.86
            .Columns("C:C").Select
            xlObj.Selection.ColumnWidth = 13.29
            .Columns("D:D").Select
            xlObj.Selection.ColumnWidth = 12.57
            .Columns("E:E").Select
            xlObj.Selection.ColumnWidth = 13.57
            .Columns("F:F").Select
            xlObj.Selection.ColumnWidth = 11
            .Columns("G:G").Select
            xlObj.Selection.ColumnWidth = 13.29
            .Range("A1").Select
            xlObj.ActiveWindow.FreezePanes = False

                        
        End With

    Next
    xlObj.Sheets(1).Activate
    xlWB.Close True
    Set xlSheet = Nothing
    Set xlWB = Nothing
    xlObj.Quit
    Set xlObj = Nothing

End Function
Second module named mod_WL:
Code: 
Option Compare Database

Public Function WaitLab()

Const FileNameBase As String = "\\site.com\Designated Folders\Weekly Reports\Waiting on Lab Weekly Report [CurrentDate].xlsx"
    Dim strFileName As String
    strFileName = Replace(FileNameBase, "[CurrentDate]", Format$(Date, "m-dd-yyyy"))

If DCount("*", "qry_advancewaitlab") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_advancewaitlab", strFileName, True, "Advance"
End If
If DCount("*", "qry_arcadiawaitlab") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_arcadiawaitlab", strFileName, True, "Arcadia"
End If
If DCount("*", "qry_ecruwaitlab") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_ecruwaitlab", strFileName, True, "Ecru"
End If
If DCount("*", "qry_leesportwaitlab") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_leesportwaitlab", strFileName, True, "Leesport"
End If
If DCount("*", "qry_wanekwaitlab") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_wanekwaitlab", strFileName, True, "Wanek"
End If

Dim xlWB As Object
    Dim xlObj As Object
    Dim xlSheet As Object
    Dim lngRow As Long
    
    Set xlObj = CreateObject("Excel.Application")
    Set xlWB = xlObj.Workbooks.Open(strFileName, False, False)
    
    For Each xlSheet In xlWB.Worksheets
        
        With xlSheet
            
            .Activate
            lngRow = .Cells(.Rows.Count, 1).End(-4162).Row 'xlUp
            Debug.Print lngRow
            .Range("F1:F" & lngRow).Select
            xlObj.Selection.FormatConditions.Add Type:=2, Formula1:= _
                    "=TODAY()-F1>13"
            xlObj.Selection.FormatConditions(xlObj.Selection.FormatConditions.Count).SetFirstPriority
            With xlObj.Selection.FormatConditions(1).Interior
                .PatternColorIndex = -4105
                .Color = 255
                .TintAndShade = 0
            End With
            xlObj.Selection.FormatConditions(1).StopIfTrue = False
            .Range("A1:H1").Select
            With xlObj.Selection
                .HorizontalAlignment = xlLeft
                .VerticalAlignment = xlBottom
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ReadingOrder = xlContext
                .MergeCells = False
                
                With .Font
                    .Name = "Calibri"
                    .FontStyle = "Bold"
                    .Size = 11
                End With
                .Borders(xlDiagonalDown).LineStyle = xlNone
                .Borders(xlDiagonalUp).LineStyle = xlNone
                With .Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlInsideVertical)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                .Borders(xlInsideHorizontal).LineStyle = xlNone
                With .Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = -0.14996795556505
                    .PatternTintAndShade = 0
                End With
            End With
            .Columns("A:A").Select
            xlObj.Selection.ColumnWidth = 8.3
            .Columns("B:B").Select
            xlObj.Selection.ColumnWidth = 18
            .Columns("C:C").Select
            xlObj.Selection.ColumnWidth = 13.29
            .Columns("D:D").Select
            xlObj.Selection.ColumnWidth = 12.57
            .Columns("E:E").Select
            xlObj.Selection.ColumnWidth = 13.57
            .Columns("F:F").Select
            xlObj.Selection.ColumnWidth = 11
            .Columns("G:G").Select
            xlObj.Selection.ColumnWidth = 15
            .Columns("H:H").Select
            xlObj.Selection.ColumnWidth = 13.29
            .Range("A1").Select
            xlObj.ActiveWindow.FreezePanes = False

                        
        End With

    Next
    xlObj.Sheets(1).Activate
    xlWB.Close True
    Set xlSheet = Nothing
    Set xlWB = Nothing
    xlObj.Quit
    Set xlObj = Nothing

End Function
Third module named mod_SE: (I have it set to Display rather than Send for testing)
Code: 
Option Compare Database

Public Function SendEMail()

Dim appOutLook As Outlook.Application
  Dim MailOutLook As Outlook.MailItem
  Dim strPath As String
  Dim strFileName As String
  Set appOutLook = CreateObject("Outlook.Application")
  Set MailOutLook = appOutLook.CreateItem(olMailItem)
  Set rst = CurrentDb.OpenRecordset("select EmailAddress from tbl_users where AccessLvl in (2, 3, 4) ")
rst.MoveFirst
        
    Do While Not rst.EOF
  strEmailTo = strEmailTo & "; " & rst!EmailAddress

  rst.MoveNext

Loop
    With MailOutLook
    .BodyFormat = olFormatRichText
    
    .To = strEmailTo
    .Subject = "Weekly Electrical Audit Report - " & Date
    .HTMLBody = "<HTML><BODY><font face=Calibri>Attention all,<BR><BR>This is the weekly report for all facilities.<BR><BR>*Notice if your facility does not show up in the report that means you are caught up with what is in the database.<BR><BR>**If you have PO's in your possession that are not in the database, Please let me know so I can enter that data for you.<BR><BR>***If there are PO's in the report that cannot be completed, please let me know. Also please let me know the reason that the PO's cannot be completed.<BR><BR><b>Thank you everyone for all of your efforts!</b></font></BODY></HTML>"

    'add all Excel files
    strPath = "W:\Folder\Databases\Weekly Reports\"
    strFileName = Dir(strPath & "*.xlsx")
    If strFileName <> "" Then
        While strFileName <> ""
          .Attachments.Add (strPath & strFileName)
          strFileName = Dir()
        Wend
    Else
        MsgBox "No file matching " & strPath & "*.csv " & " found." & vbCrLf & _
                "Processing terminated."
        Exit Function
    End If
    '.Send
    .Display      'Used during testing without sending (Comment out .Send if using this line)
  End With
End Function

I wanted the code to check to see if it is Monday and if so... do all that. I also have a module for the pause to give the time for the code to do it's thing.

The code in the on load of my main form is this:
Code: 
Private Sub Form_Load()

   If Weekday(Now) = vbMonday Then
     WaitVis
     Pause (240)
     WaitLab
     Pause (240)
     SendEMail
   End If

On form open the code runs the first call creating the first export named:
Code: 
Waiting on Visual Weekly Report [CurrentDate].xlsx
but then when trying to run the second call to create the export called:
Code: 
Weekly Reports\Waiting on Lab Weekly Report [CurrentDate].xlsx
I get a message box stating it cannot find the file

and highlights this line in the second module named mod_WL:
Code: 
Set xlWB = xlObj.Workbooks.Open(strFileName, False, False)
and no code runs after that error.

I know I am calling these wrong... can anyone please tell me the proper way to call these functions and them work?

**Note** all of the above code used for the exports and sending the email work using command buttons in a form.

I apologize for the very lengthy post with lots of code
 

Attachments

  • Capture1.JPG
    Capture1.JPG
    37.3 KB · Views: 349
Last edited:
P

psyc0tic1

Access Moron
Local time
Today, 15:18
Joined
Jul 10, 2017
Messages
360
Another note... I have the code in the on load of the main form which only I get so it won't run for anyone else. (everyone else that uses the database sees different main forms)
 
P

psyc0tic1

Access Moron
Local time
Today, 15:18
Joined
Jul 10, 2017
Messages
360
And just for informational purposes here is the code for the mod_Wait for the pause:
Code: 
Option Compare Database

Public Function Pause(NumberOfSeconds As Variant)
On Error GoTo Err_Pause

Dim PauseTime As Variant, Start As Variant

PauseTime = NumberOfSeconds
Start = Timer
Do While Timer < Start + PauseTime
DoEvents
Loop

Exit_Pause:
Exit Function

Err_Pause:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Pause

End Function

I get the same above mentioned error whether or not I use the pause.
 
P

psyc0tic1

Access Moron
Local time
Today, 15:18
Joined
Jul 10, 2017
Messages
360
Ha Ha never mind... it was due to there actually being no data to export causing the error popup.... then I added some additional error checking to move on if there was no data to export.
 
You must log in or register to reply here.

Similar threads

W
  • Question
Solved Excel xls-Export
Replies
6
Views
355
wanzi
W
T
Solved Append table to excel file
Replies
8
Views
836
ebs17
E
MattBaldry
Solved Export to Spreadsheet and Name Tab
Replies
6
Views
525
MattBaldry
MattBaldry
D
Export to Excel not working correctly
Replies
3
Views
753
Pat Hartman
P
oxicottin
Solved Export search form data to excel
Replies
11
Views
623
oxicottin
oxicottin

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom