UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Export To Excel Best Practice, Access 2013    
 
   
gint32
post Feb 7 2019, 07:06 AM
Post#1



Posts: 368
Joined: 8-May 09
From: Australia


Currently i have the contents of a query ( 20 -50 rows max)export to a designated preformatted excel spreadsheet . But this is proving temperamentle as it seems like different excel versions affect whether itll export or not.So understandably.. i need a method that's more rebust though. I can.t have it work sometimes and others times not. When i click the action button to export. The reason i export to excel in the first place is the users need to ne able to add ectradata grom other sources manually to this spreadsheet newly created sheet. And yhis option just doesnt seem to cut it..so my question is should i ditch the VBA to excel and run with some other means/ functionality thats built into acceess ybat i am unaware of as yet. Or can the VBA be coded in such a way that it.ll export regardless of the VBA and or references being 14 16 or 16 or whaatever the usdues are that hold it back.
Meaning is there a way to format a form with a subform ...theat.ll mimic excels rows sources with headers and still ne able to frreetype additional data in the empty cells(feilds) before sending to the printer ..bearing in mind(other than the qry results. ) the additional data will not be stored in any tables as it.s mearly just to get it onto the printed paper for distribution. then itll. Be discarded.

So in essence am I using the best approach to suit my needs.

Anyone got any suggestions or tried and tested code or methods..If could stick with Access i would be it seems to me that reports are nit built for this type of thing...correct me if i'm wrong.
Go to the top of the page
 
theDBguy
post Feb 7 2019, 11:19 AM
Post#2


Access Wiki and Forums Moderator
Posts: 75,598
Joined: 19-June 07
From: SunnySandyEggo


Hi. I'm not sure I follow all that; but if you're having problems with different Excel library references, then you could look into "late binding."

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
River59
post Feb 7 2019, 12:00 PM
Post#3



Posts: 1,734
Joined: 7-April 10
From: Detroit, MI


This is a little hard to follow and it's hard to know if your problem are Excel references or if you are sending data over with missing/added columns that may not conform to the Excel column data types ...

If it is the Excel version causing the problems, then dbGuy's recommendation to look into late binding is good advice.

If it is that you are sending over different columns (or amount of columns, as in sending 12 columns when the Excel template expects 15), then you need to be sure your query contains all of the columns that Excel expects.

You can also send over a recordset to a blank Excel workbook and fill in the columns names with VBA (ask if you need to do this and we will show you how).

A little more clarity on the problem and any error message will help us to help you.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
gint32
post Feb 7 2019, 08:21 PM
Post#4



Posts: 368
Joined: 8-May 09
From: Australia


So far as I can tell I already am using late binding. I really have no idea where the issue is coming from, all I know is sometimes I click the button and it works and then the next time it doesn't (which might be just a few minutes later). Its so bizarre
code below from the "button event"
CODE
Private Sub btnExportExcel_Click()

Stop

        Dim appExcel As Object
        Dim wkbWorkBook As Object
        Dim wksSheet As Object
        Dim LastRow As Integer '''within excel worksheet
        Dim rs As Recordset
        Dim row As Integer: row = 3
        Dim col As Integer: col = 1
        Dim LlastRow As Integer  '''lastrow in the rs.record count(Recordset)
        Dim DateOfForm As Date
        Dim date_test As Date


        Set appExcel = CreateObject("Excel.Application")
        Set wkbWorkBook = appExcel.Workbooks.Open("P:\Somewhere\SomeWorkbook.xlsm")
        Set wksSheet = wkbWorkBook.Worksheets("SomeSheetName")
        appExcel.Visible = True 'false


Call getformsdate(DateOfForm) 'Gets the date from the currently open form
'stop
    date_test = DateOfForm
    
                    'Start with inserting  all the required header Info   > > > > > > > > > >
                    '> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
                    
                        Range("F1") = "   For..   " & Format(date_test, "dd mmmm yyyy")
                    Columns("F:F").ColumnWidth = 19.43
                    Rows("1:1").RowHeight = 20.25
                        Range("A1:E1").MergeCells = True
                        Range("A1:E1").Select
                    ActiveCell.FormulaR1C1 = "  SOMEHEADERINFO "
                        Range("F2").Select
                    ActiveCell.FormulaR1C1 = "  SOMEHEADERINFO "
                        Range("E2").Select
                    ActiveCell.FormulaR1C1 = "  SOMEHEADERINFO  "
                        Range("G2").Select
                    ActiveCell.FormulaR1C1 = " SOMEHEADERINFO  "
                        Range("D2").Select
                    ActiveCell.FormulaR1C1 = "  SOMEHEADERINFO "
                        Range("H2").Select
                    ActiveCell.FormulaR1C1 = "  SIGNATURE  "
                        Range("B2").Select
                    ActiveCell.FormulaR1C1 = "  SOMEHEADERINFO   "
                      Range("C2").Select
                    ActiveCell.FormulaR1C1 = "  SOMEHEADERINFO "
                    Columns("C:C").ColumnWidth = 25#
                    ' < < < < < < < < < < < <  < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < <
                       'Finished inserting  all the required header Info    < < < < < < < < < < < <
    
    
                                'Take care of Formatting   >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                                '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                                
                                        Range("A1:H2").Font.UnderLine = xlUnderlineStyleDouble
                                        Range("A1:H2").Font.Size = 14
                                        Range("B1:H2").Borders(xlEdgeBottom).LineStyle = xlContinuous
                                        Range("A1:H2").Borders(xlEdgeBottom).Weight = xlThick
                                        Range("A1:H2").Borders(xlEdgeBottom).Color = vbGreen
                                        Range("A1:H2").Font.Color = RGB(0, 0, 0)
                                        Range("A1:H2").HorizontalAlignment = xlGeneral
                                        Range("A1:H2").VerticalAlignment = xlBottom
                                        Range("A1:H2").WrapText = False
                                        Range("A1:H2").HorizontalAlignment = xlCenter
                                        Range("A1:H2").VerticalAlignment = xlCenter
                                        Range("A1:H2").Interior.ColorIndex = 1
                                        Range("A1:H2").Font.ColorIndex = 2
                                        Range("A1:H2").Borders.LineStyle = xlSolid
                                        Range("A1:H2").EntireColumn.AutoFit
                                
                                'Finish taking care of Formatting   >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                                '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                                


                    Sheets("ExcessHours").Range("A3:Z10000").Delete 'clear the workbook.sheets last contents if any there form last time opening ."

                                'Now Pull the data Through and populate all the cells

                                 Set rs = Me.Form.Recordset
                                
                                            If rs.RecordCount > 0 Then
                                                    
                                                        LlastRow = rs.RecordCount + 2
                                                        rs.MoveFirst
                                                
                                                Do While Not rs.EOF
                                                            
                                                            Dim fld As Field
                                                            For Each fld In Me.Recordset.Fields
          
                                                                        
                                                                        
                                                                        If col > 5 Then Exit For

                                                                                wksSheet.Cells(row, col) = fld
                                                                                col = col + 1
                                                                            Next fld
                                                                            rs.MoveNext
                                                                            col = 1
                                                                            row = row + 1
                                                 Loop
                                                                        End If
    
    
    
                                                            LastRow = Cells(Rows.Count, 2).End(xlUp).row
                                                            Range("A1:A" & LastRow).Select
                                                            Range("A3:A" & LastRow).ClearContents ' clear contents of first Column.
        
                                                                If LastRow < 3 Then
                                                                
                                                                        GoTo ForGetTheIf  'equals no records to display
                                                                                          'So skip this routine
                                                                Else
                                                                
                                                                    Range("A3").Select
                                                                    ActiveCell.FormulaR1C1 = "1"
                                                                    
                                                                            'now insert column numbering beginning at A3 with AutoFill.
                                                                            'MMMMMMMMMMMMMMM
                                                                            If LastRow > 3 Then
                                                                                Range("A4").Select
                                                                                ActiveCell.FormulaR1C1 = "2"
                                                                            End If
                                                                            'MMMMMMMMMMMMMMM
                                                                            
                                                                            If LastRow > 4 Then
                                                                                Range("A3:A4").Select
                                                                                Range("A3:A4").AutoFill Destination:=Range("A3:A" & LastRow)
                                                                            End If
                                                                            'MMMMMMMMMMMMMMM
ForGetTheIf:
                                                        
                                                                    Range("A3:A" & LastRow).EntireColumn.AutoFit
                                                                    Range("A3:H" & LastRow).Borders.LineStyle = xlSolid
                                                                
                                                                End If

        ' Now Save and Quit
        
                wkbWorkBook.Save
                Set wksSheet = Nothing
                Set wkbWorkBook = Nothing
                Set appExcel = Nothing

Error_Handler_Exit:
    On Error Resume Next
    appExcel.Visible = True   'Make excel visible to the user
    Set rs = Nothing
    Set wksSheet = Nothing
    Set wkbWorkBook = Nothing
    appExcel.ScreenUpdating = True
    Set appExcel = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: ExportRecordset2XLS" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit

End Sub

Sub getformsdate(DateOfForm)

                DateOfForm = Forms!Some!txt_OTDate
                DateOfForm = Format(DateOfForm, "dd/mmm/yy h:m AM/PM")

End Sub



Go to the top of the page
 
WildBird
post Feb 7 2019, 09:08 PM
Post#5


UtterAccess VIP
Posts: 3,560
Joined: 19-August 03
From: Auckland, Little Australia


A few things.

You are exporting to a known file, not a new file. This file could be open in the background and this could be the issue.

I have been doing this for years.

Have a Excel file with formatting etc on it. So headings would go etc, set column height, bold etc.

From Access, create a copy of this file, and save it (usually with datetimestamp at start, so it is 'unique'*

Use copyfromrecordset to populate this file.

Save this file.

Your version seems like you are applying formatting to existing formatting. And hardcoding of cells etc. Can be much cleaner.

I have code you could look at if it makes it easier.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
gint32
post Feb 7 2019, 09:10 PM
Post#6



Posts: 368
Joined: 8-May 09
From: Australia


Sounds like it worth a try ,can I see the code
Go to the top of the page
 
gint32
post Feb 7 2019, 09:16 PM
Post#7



Posts: 368
Joined: 8-May 09
From: Australia


re-Meaning is there a way to format a form with a subform ...that.ll mimic excels rows sources with headers and also still be able to free type(add) additional info into empty the cells(or fields) before sending to the printer.

Is there a way to do this, so as I can get away from excel, as then I'll have eliminated my excel issue.
Go to the top of the page
 
gint32
post Feb 7 2019, 09:26 PM
Post#8



Posts: 368
Joined: 8-May 09
From: Australia


Think i have discovered the issue, but haven't solved it as yet...

I checked processes and Excels still running even though all open/visible excel workbooks are closed.

Obviously, I can't just right the VBA code to kill off all excel processes, ...in case users are multitasking as they would loose all there other excel work and my name would be mud:) ...

Does anyone with any ideas? on a way forward.
Go to the top of the page
 
WildBird
post Feb 7 2019, 10:34 PM
Post#9


UtterAccess VIP
Posts: 3,560
Joined: 19-August 03
From: Auckland, Little Australia


This is why I dont like users opening a file on the network - hard to know who has it open, and they will lock it etc.

I basically create a platform.

I have either Access or Excel file on a developers machine - i.e. yours. This has links to data, be it Access, SQL, Excel etc. Doesnt matter, code is basically the same. Lets just call this "Reporter"

I have a template file (this is a .xlsm, not to be confused with a xlst file). This has empty worksheets, named like SalesData

Reporter has code, lets call it Process(), that
1 Copies the template file and creates a new file.
2 Loops the queries or worksheets. I use naming convention like "qryExpSales". Anything starting with qryExp (for query Export) will be exported. It will look in the new file for a worksheet named "SalesData" and populate it with the recordset. If you had another query named qryExpBudget, you would just add a new sheet named BudgetData (its all based on naming conventions) to the template file.
3 Saves the new file and closes it etc.
4 The template file has code in it that will build pivot tables and graphs etc based off these data sheets. This gets called once by Reporter and sets a named range. Means when the new file is opened, all the processing is already done.
5 Each time this process is run, a new file is created with a naming convention of YYYYMMDDHHNNSS and the report name.xlsm eg. 20190208153651SLAKPIReport.xlsm
6 I have a folder named GetLatest. This has 2 files in it. GetLatest.ini and GetLatest.xlsm
7 GetLatest.xlsm has code to look at GetLatest.ini. GetLatest.ini has entries
Search=SLAKPIReport
Extension=xlsm
FromPath=\\Path\SLAReporting\DEVExport\
NewName=SLAKPIReport
8 This GetLatest.xlsm will copy the latest file matching the Search string above, in the path above and place it on the users C:\Temp Drive (this can be changed) and opens it up.

This means every user will have their own copy of the file, with data embedded within it. No data connections, no file locks etc.

Adding new data sources straightforward.
Adding new reports is also straightforward, literally copy and paste GetLatest folder, change the entries and youre done.
End user has a shortcut for each report. Could be just 1, where I am now we have around 4 or 5 different reports. Users dont have to search a folder to find the latest file.
Files are archived on the network.
Reports are essentially on demand and self service.








--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
gint32
post Feb 8 2019, 03:02 AM
Post#10



Posts: 368
Joined: 8-May 09
From: Australia


QUOTE
Re This is why I dont like users opening a file on the network

Sounds good . I am not convinced its a network issue as I get same results with using the same script on a partition on my SSD drive. However I need some thing that works everytime . Even when I am long dead and there nobody else around to sort..so if you dont mind when you get the time can I see the code please..
Go to the top of the page
 
PaulBrand
post Feb 8 2019, 05:21 AM
Post#11



Posts: 1,704
Joined: 4-September 02
From: Oxford UK


Have a look at the CopyFromRecordset method, this will simplify your process

CODE
Set rs = Me.Form.Recordset
                                
If rs.RecordCount > 0 Then
do something...
Else
wksSheet.Cells(row, col).CopyFromRecordset rs
End If

This post has been edited by PaulBrand: Feb 8 2019, 05:27 AM

--------------------
Paul
Go to the top of the page
 
Minty
post Feb 8 2019, 05:53 AM
Post#12



Posts: 310
Joined: 5-July 16
From: UK - Wiltshire


In addition to the advice above, I always do this locally on the end users local drive. You can create a local ExcelSaves folder for the user and use that for all you local processing.
Then if required copy the completed local formatted file over to the network.
I check if the network copy is opened before trying to overwrite it, and let the user know.
Go to the top of the page
 
cheekybuddha
post Feb 8 2019, 06:11 AM
Post#13


UtterAccess VIP
Posts: 11,327
Joined: 6-December 03
From: Telegraph Hill


>> Obviously, I can't just right the VBA code to kill off all excel processes <<

This is raising alarm bells!

If you use Excel automation code from Access, and do not fully qualify all your object references, you will end up with lingering instances of Excel in memory even after you thought you had done with it all.

Once you have got all your code to where you think you want it to be, post it here so we can try and spot cases of unqualified references (not an easy job!! dazed.gif)

See here and here for a couple (of many examples on UA) of what I mean.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
gint32
post Feb 8 2019, 06:18 AM
Post#14



Posts: 368
Joined: 8-May 09
From: Australia


QUOTE
wksSheet.Cells(row, col).CopyFromRecordset rs


Thanks, But I am not sure where your going with the above, I'd really need you to explain with further detail(VBA) for me to be able take what ever your suggesting further. thanks though
Go to the top of the page
 
PaulBrand
post Feb 8 2019, 06:30 AM
Post#15



Posts: 1,704
Joined: 4-September 02
From: Oxford UK


I thought I hade made it plain.

Instead of

CODE
                    Sheets("ExcessHours").Range("A3:Z10000").Delete 'clear the workbook.sheets last contents if any there form last time opening ."

                                'Now Pull the data Through and populate all the cells

                                 Set rs = Me.Form.Recordset
                                
                                            If rs.RecordCount > 0 Then
                                                    
                                                        LlastRow = rs.RecordCount + 2
                                                        rs.MoveFirst
                                                
                                                Do While Not rs.EOF
                                                            
                                                            Dim fld As Field
                                                            For Each fld In Me.Recordset.Fields
          
                                                                        
                                                                        
                                                                        If col > 5 Then Exit For

                                                                                wksSheet.Cells(row, col) = fld
                                                                                col = col + 1
                                                                            Next fld
                                                                            rs.MoveNext
                                                                            col = 1
                                                                            row = row + 1
                                                 Loop


Use this:

CODE
                    Sheets("ExcessHours").Range("A3:Z10000").Delete 'clear the workbook.sheets last contents if any there form last time opening ."

                                'Now Pull the data Through and populate all the cells

                                 Set rs = Me.Form.Recordset
                                
                                                    
                             Sheets("ExcessHours").Range("A3").CopyFromRecordset rs

--------------------
Paul
Go to the top of the page
 
PaulBrand
post Feb 8 2019, 06:40 AM
Post#16



Posts: 1,704
Joined: 4-September 02
From: Oxford UK


You may also consider using an Excel template...

--------------------
Paul
Go to the top of the page
 
cheekybuddha
post Feb 8 2019, 06:55 AM
Post#17


UtterAccess VIP
Posts: 11,327
Joined: 6-December 03
From: Telegraph Hill


Hi,

I've just had a scan of your code above - even if you adjust it according to Paul's suggestion, you will have an instance of Excel left over in running processes after it completes.

You have unqualified references to the following objects:
Range
Columns
Rows
ActiveCell
Sheets
Cells
(and maybe others!)

It may not be the problem you are trying to solve, but it will be a problem if you use this code nonetheless.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Feb 8 2019, 07:31 AM
Post#18


UtterAccess VIP
Posts: 11,327
Joined: 6-December 03
From: Telegraph Hill


Hi,

I tidied up your code to try and deal with the unqualified references.

Whenever you copy code from Excel's macro recorder for use in automation in Access, you really want to try and remove all cases of:
CODE
Range("A1").Select
ActiveCell.Property = "Blah"
and translate it to:
CODE
worksheetObject.Range("A1").Property = "Blah"

The recorder is actually recording your mouse clicks on each cell, which is not necessary to your code, and actually slows things down, as well as making life a lot less clear!

This code is a bit of a mess (as is always the way when just taking the output from the Excel macro recorder!) and could be tidied up further:
CODE
Private Sub btnExportExcel_Click()

  Dim appExcel As Object
  Dim wkbWorkBook As Object
  Dim wksSheet As Object
  Dim LastRow As Integer '''within excel worksheet
  Dim rs As Recordset
  Dim row As Integer: row = 3
  Dim col As Integer: col = 1
  Dim LlastRow As Integer  '''lastrow in the rs.record count(Recordset)
  Dim DateOfForm As Date
  Dim date_test As Date
  Dim fld As Field

  Set appExcel = CreateObject("Excel.Application")
  Set wkbWorkBook = appExcel.Workbooks.Open("P:\Somewhere\SomeWorkbook.xlsm")
  Set wksSheet = wkbWorkBook.Worksheets("SomeSheetName")
  appExcel.Visible = True 'false

  date_test = getformsdate 'Gets the date from the currently open form
    
   With wksSheet          
  'Start with inserting  all the required header Info   > > > > > > > > > >
  '> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
    .Range("F1") = "   For..   " & Format(date_test, "dd mmmm yyyy")
    .Columns("F:F").ColumnWidth = 19.43
    .Rows("1:1").RowHeight = 20.25
    .Range("A1:E1").MergeCells = True
    .Range("A1:E1").FormulaR1C1 = "  SOMEHEADERINFO "
    .Range("F2").FormulaR1C1 = "  SOMEHEADERINFO "
    .Range("E2").FormulaR1C1 = "  SOMEHEADERINFO  "
    .Range("G2").FormulaR1C1 = " SOMEHEADERINFO  "
    .Range("D2").FormulaR1C1 = "  SOMEHEADERINFO "
    .Range("H2").FormulaR1C1 = "  SIGNATURE  "
    .Range("B2").FormulaR1C1 = "  SOMEHEADERINFO   "
    .Range("C2").FormulaR1C1 = "  SOMEHEADERINFO "
    .Columns("C:C").ColumnWidth = 25#
  ' < < < < < < < < < < < <  < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < <
  'Finished inserting  all the required header Info    < < < < < < < < < < < <
    
  'Take care of Formatting   >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
  '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    .Range("A1:H2").Font.Size = 14
    .Range("A1:H2").Font.UnderLine = xlUnderlineStyleDouble
    .Range("B1:H2").Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Range("A1:H2").Borders(xlEdgeBottom).Weight = xlThick
    .Range("A1:H2").Borders(xlEdgeBottom).Color = vbGreen
    .Range("A1:H2").Font.Color = RGB(0, 0, 0)
    .Range("A1:H2").HorizontalAlignment = xlGeneral
    .Range("A1:H2").VerticalAlignment = xlBottom
    .Range("A1:H2").WrapText = False
    .Range("A1:H2").HorizontalAlignment = xlCenter
    .Range("A1:H2").VerticalAlignment = xlCenter
    .Range("A1:H2").Interior.ColorIndex = 1
    .Range("A1:H2").Font.ColorIndex = 2
    .Range("A1:H2").Borders.LineStyle = xlSolid
    .Range("A1:H2").EntireColumn.AutoFit
  'Finish taking care of Formatting   >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
  '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
  End With
  With wkbWorkBook.Sheets("ExcessHours")
    .Range("A3:Z10000").Delete 'clear the workbook.sheets last contents if any there form last time opening ."
    'Now Pull the data Through and populate all the cells
    Set rs = Me.Form.Recordset
    If rs.RecordCount > 0 Then
      LlastRow = rs.RecordCount + 2
      rs.MoveFirst
      Do While Not rs.EOF
        For Each fld In rs.Fields
          If col > 5 Then Exit For
          wksSheet.Cells(row, col) = fld      ' <-- Why are you doing this here?
          col = col + 1                       '     It would be better done above when you re dealing with wksSheet
        Next fld
        rs.MoveNext
        col = 1
        row = row + 1
      Loop
    End If
    LastRow = .Cells(Rows.Count, 2).End(xlUp).row
    .Range("A1:A" & LastRow).ClearContents ' clear contents of first Column.
    If LastRow < 3 Then
      GoTo ForGetTheIf  'equals no records to display
                              'So skip this routine
    Else
      Range("A3").FormulaR1C1 = "1"
      'now insert column numbering beginning at A3 with AutoFill.
      'MMMMMMMMMMMMMMM
      If LastRow > 3 Then
        .Range("A4").FormulaR1C1 = "2"
      End If
      'MMMMMMMMMMMMMMM
      If LastRow > 4 Then
        .Range("A3:A4").AutoFill Destination:=.Range("A3:A" & LastRow)
      End If
      'MMMMMMMMMMMMMMM
ForGetTheIf:
      .Range("A3:A" & LastRow).EntireColumn.AutoFit
      .Range("A3:H" & LastRow).Borders.LineStyle = xlSolid
    End If
  End With
' Now Save and Quit
  wkbWorkBook.Save

Error_Handler_Exit:
  On Error Resume Next
  appExcel.Visible = True   'Make excel visible to the user
  Set rs = Nothing
  Set wksSheet = Nothing
  Set wkbWorkBook = Nothing
  appExcel.ScreenUpdating = True
  Set appExcel = Nothing
  Exit Sub

Error_Handler:
  MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
         "Error Number: " & Err.Number & vbCrLf & _
         "Error Source: ExportRecordset2XLS" & vbCrLf & _
         "Error Description: " & Err.Description & _
         Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
         , vbOKOnly + vbCritical, "An Error has Occured!"
  Resume Error_Handler_Exit

End Sub

Private Function getformsdate() As String

  getformsdate = Format(Forms!Some!txt_OTDate, "dd/mmm/yy h:m AM/PM")

End Function

I also change getformsdate() to a function that returns the date (or a string representation!)

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
River59
post Feb 8 2019, 09:11 AM
Post#19



Posts: 1,734
Joined: 7-April 10
From: Detroit, MI


I don't see where you are quitting Excel. Maybe you can try to replace this


CODE
       ' Now Save and Quit
        
                wkbWorkBook.Save
                Set wksSheet = Nothing
                Set wkbWorkBook = Nothing
                Set appExcel = Nothing


With this

CODE
              wkbWorkBook.Save
               appExcel.Quit
               Set wksSheet = Nothing
               Set wkbWorkBook = Nothing
               Set appExcel = Nothing


[/code]

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
cheekybuddha
post Feb 8 2019, 09:34 AM
Post#20


UtterAccess VIP
Posts: 11,327
Joined: 6-December 03
From: Telegraph Hill


You wouldn't want to quit your Excel instance if you want to leave it open for the user, no?

--------------------


Regards,

David Marten
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    25th June 2019 - 06:42 AM