Access to Excel

[fkbodley]

I am trying to upload a filtered form to excel. Using code someone posted to me I tried to make this work. But like all great code for a newbee.. IT DOSENT WORK! Can some one help me out.
Code:

Private Sub Excel_Click()
'Be sure to set your References vis Tools on the Menu Bar to
'Microsoft Excel 10.0 Object Library or to what ever
'version excel you are running

    Dim rs As Recordset
    Dim intMaxCol As Integer
    Dim intMaxRow As Integer
    Dim objXL As Excel.Application
    Dim objWkb As Workbook
    Dim objSht As Worksheet
    Dim f As field
    Dim i As Long
Dim objXLApp As Object
Dim objXLws As Object
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strDocPath
Dim strPath As String
    
    Dim sSql As String
    Dim sCriteria As String
    
        sCriteria = "WHERE 1=1 "
        
        If Me![cboFilterPONumber] <> "" Then
                sCriteria = sCriteria & " AND  [Milestone Serch Query].[Purchase Order] = """ & cboFilterPONumber & """"
        End If
        If Me![cboFilterDescription] <> "" Then
            sCriteria = sCriteria & " AND  [Milestone Serch Query].Description like """ & cboFilterDescription & "*"""
        End If
        If Me![cboFilterBuilding] <> "" Then
                sCriteria = sCriteria & " AND  [Milestone Serch Query].Building Like """ & cboFilterBuilding & "*"""
        End If
        If Me![txtStartDate] <> "" And txtEndDate <> "" Then
            sCriteria = sCriteria & " AND  [Milestone Serch Query].[Ship Date] between #" & Format(txtStartDate, "dd-mmm-yyyy") & "# and #" & Format(txtEndDate, "dd-mmm-yyyy") & "#"
        End If
        If Me![txtFilterTAG] <> "" Then
            sCriteria = sCriteria & " AND  [Milestone Serch Query].TAG like """ & txtFilterTAG & "*"""
        End If

        sSql = "SELECT [Milestone Serch Query].[TAB], [Milestone Serch Query].[Purchase Order], [Milestone Serch Query].[Description], [Milestone Serch Query].[Supplier], [Milestone Serch Query].[Building], [Milestone Serch Query].[Tag Number], [Milestone Serch Query].[Ship Date], [Milestone Serch Query].[Intermediate Delivery], [Milestone Serch Query].[Intermediate Shipment], [Milestone Serch Query].[Arrival Date], [Milestone Serch Query].[Length], [Milestone Serch Query].[Width], [Milestone Serch Query].[Height], [Milestone Serch Query].[Weight], [Milestone Serch Query].[Shipping Probability], [Milestone Serch Query].[Notes], [Milestone Serch Query].[Shipment Release Number] FROM [Milestone Serch Query]" & sCriteria
        Forms![frmSearchCriteriaMain]![Milestone Serch Query subform].Form.RecordSource = sSql
        Forms![frmSearchCriteriaMain]![Milestone Serch Query subform].Form.Requery


        Set rs = CurrentDb.OpenRecordset(sSql, dbOpenSnapshot)
            intMaxCol = rs.Fields.Count
            If rs.RecordCount < 1 Then
                MsgBox "The report you are trying to produce does not contain any data!" & vbCr & vbCr & _
                "Please check that there is data for this report.", vbCritical
            Else
                

' Populate the excel object
Set objXLApp = CreateObject("Excel.Application")
' Open the template workbook
objXLApp.Workbooks.Open ("Milestone Serch Query")
' Save the template as the file specified by the user
objXLApp.ActiveWorkbook.SaveAs ("Milestone Serch Query")
' Select the 'Raw Data' worksheet
Set objXLws = objXLApp.ActiveWorkbook.Worksheets(1)
' Activate the selected worksheet
objXLws.Activate
' Ask Excel to copy the data from the recordset
objXLws.Range("A1").CopyFromRecordset rs
' Select the main worksheet
objXLApp.Worksheets("Milestone Serch Query").Activate
' Activate the selected worksheet
Set objXLws = objXLApp.ActiveWorkbook.Worksheets("Milestone Serch Query")
' Populate the criteria box on the main form (so the user knows what was exported)
objXLws.Cells(1, 2).Value = sCriteria
End If

' Destroy the recordset and database objects

Set rst = Nothing
Set db = Nothing

' Hide warnings on the spreadsheet
objXLApp.DisplayAlerts = False
' Refresh the root PivotTable (which refreshes all)
objXLApp.ActiveWorkbook.Save
' Turn spreadsheet warnings back on
objXLApp.DisplayAlerts = True
' Make it visible
objXLApp.Visible = True

'**error handling, in the function exit  - make sure you set the object references to nothing as shown below.

FunctionExit:

Set objXLws = Nothing
Set objXLApp = Nothing

End Sub

[MMcCarthy]

You have uploaded code without explaining what is working and what is not working. Please be clearer about what your problem is. For example, is an instance of Excel opening?

[fkbodley]

The code is to take a main form, which filters a subform, and send the results to an excel table. The end user wants specified formating of the cells, which was to be my next step in code. But first I need my export code to work right. The idea is a comand button will automate the process and the user can then email the resulted report to the team. I have attached the database in case it helps.

[NeoPa]

I'll try saying it :
What is not working where in your code?

[Annalyzer]

Subscribing. And wishing there were a way to subscribe more discreetly. =)

[MMcCarthy]

Subscribing. And wishing there were a way to subscribe more discreetly. =)

Check under the first post. Look for a link saying Subscribe. :D

[Annalyzer]

Well, I'll be darn! Thanks msquared! =)

[fkbodley]

The whole thing imports only pieces of the records. At this time I have 277 records that need importing. When I tried it this morning it took 15 of the 277 records. Honestly I dont know what is broken. I am just learning VBA and have struggled with this. The origional code wont work at all and this new modified doesnt seam to work corecly. The bottom line is I need the filtered records to be posted into an excel table with set formating. If someone has a better way to do this I am open to suggestions.

Thank you
FKB

[NeoPa]

OK FKB, If I'm to get involved in this you will need to explain yourself in words rather than expecting me to decipher the question from your code.

It is never ok to post code in place of a clearly formulated question. As reference material it's fine, but it's only there to illustrate what has already been expressed as clearly as possible in the thread.

I make a special point of this because your question is among the more involved, and I expect to be doing enough work on this anyway, without having to do yours for you.

If you're happy to continue with this understanding then there are certainly ways and means of transferring data from Access into Excel. Unfortunately, knowing what best suits your situation involves a clear understanding of many details. Doing this via a web-based forum adds extra complication as I don't have access to what you have access to.

I would like a better understanding of the data that you're intending to transfer across, as well as how the data will need to be stored in the spreadsheet. Any important restrictions etc.

I will start off with dropping a few ideas that may help you if you look into them. Note, I am not suggesting I leave it there by any means, but be prepared for what may be a long slog. This doesn't have any signs that it'll be easy I'm afraid.

Application Automation is a useful link to explore. Also look at CopyFromRecords et Method.

PS. I just found out about the latter today. Had to recode some of my project because it was obviously a better method for what I wanted doing.

[janders468]

Hi FKB,

I put together the below function which will return the correct where clause. It's no frills and very straightforward , we could get fancy with the code and make it more extensible but based on your aforementioned knowledge of VBA I think straightforward is best, but there are many ways to do this:

Public Function BuildWhereClause() As String
        Dim strCriteria As String
        Dim strShipDate As String, strArriveDate As String
        If Me.txtStartDate <> "" And Me.txtEndDate <> "" Then
            strShipDate = "[Ship Date] between #" & CDate(Me.txtStartDate) & "# AND #" & Me.txtEndDate & "#"
        End If
        If Me.txtARStartDate <> "" And Me.txtAREndDate <> "" Then
            strShipDate = "[Ship Date] between #" & CDate(Me.txtARStartDate) & "# AND #" & Me.txtAREndDate & "#"
        End If
        If Me.cboFilterPONumber <> "" Then
            strCriteria = strCriteria & "[Purchase Order] = '" & Me.cboFilterPONumber & "' AND "
        End If
        If Me.txtFilterTAG <> "" Then
            strCriteria = strCriteria & "[Tag Number]='" & Me.txtFilterTAG & "' AND "
        End If
        If Me.cboFilterDescription <> "" Then
            strCriteria = strCriteria & "[Description]='" & Me.cboFilterDescription & "' AND "
        End If
        If Me.cboFilterBuilding <> "" Then
            strCriteria = strCriteria & "[Building]='" & Me.cboFilterBuilding & "' AND "
        End If
'We know there will be an extra " AND " at the end so strip it out
        If strCriteria <> "" Then
            BuildWhereClause = Left(strCriteria, Len(strCriteria) - 5)
        Else
            BuildWhereClause = ""
        End If
End Function

Based on my understanding of wanting a date range this will only filter on the date if both a start and end date are selected (for either the ship date or arrival date), if that isn't what you require you will have to find a way to change it. My preference in code is to break down functionality into manageable units. Exporting and building a where clause are fundamentally two different things and it's a lot to wrap your mind around doing too many things at once (for me at least). I would use the return value of this to build your recordset. One further change I would make, is in your record source you have used the SQL to select everything from an underlying query, you can just change the actual query object to be your source since you aren't altering it in any way. In this way we can simplify the previous code by invoking it as follows:

Private Sub Excel_Click()
        Dim strQuery As String
        Dim strWhere As String
        strWhere = BuildWhereClause
        If strWhere <> "" Then
            strQuery = "SELECT * FROM [Milestone Serch Query] WHERE " & strWhere
        Else
            strQuery = "[Milestone Serch Query]"
        End If
        'now export to excel
End Sub

See if you understand what I am saying here and then we can progress on to the export routine (which is honestly easier just a little more boilerplate code to write). Hope that helps.

[fkbodley]

I am sorry about all this... I gues the issue is I dont speak english as well as I should. The fillter function Works great. What doesnt work is the import code.
The code I posted was modified to try and over come my origional code error. Which I posted bellow and marked the first line the code hangs up on.

My origioal code was:

Set rs = CurrentDb.OpenRecordset(sSql, dbOpenSnapshot)
            intMaxCol = rs.Fields.Count
            If rs.RecordCount < 1 Then
                MsgBox "The report you are trying to produce does not contain any data!" & vbCr & vbCr & _
                "Please check that there is data for this report.", vbCritical
            Else
                If rs.RecordCount > 0 Then
                    rs.MoveLast:    rs.MoveFirst
                    intMaxRow = rs.RecordCount
                    Set objXL = New Excel.Application
                  With objXL
                            .Visible = True
                            Set objWkb = .Workbooks.Add [B]<--Error Here[/B]
                            Set objSht = objWkb.Worksheets(1)
                        With objSht
                                For i = 0 To rs.Fields.Count - 1
                                    objSht.Cells(1, i + 1).Value = rs.Fields(i).Name
                                    Next i
                                With objSht
                                   .Range(.Cells(2, 1), .Cells(intMaxRow + 1, intMaxCol)).CopyFromRecordset rs
                                End With
                        End With
                  End With
                End If
            End If

My second problem is finding a way to pre-format excel with code. This I have no clue on.

Agian I appoligise that I made so many people upset with my question. I am more an end user then a programer. I do all this to try and expedite what I do day to day. VBA and Access are just tools I started learning in the military but never had formal training on. So please dont get upset if I cant explane this well. To me something works or doesnt. I few people assist me in finding code or writing code and 90% of the time I can solve my own problem. But this is abouve my simple minded level.

Agian thank you all for helping me.
FKB

[Stewart Ross]

Hi. I have added code tags around your code, just as NeoPa did for your earlier posts - it helps to make your code more readable if you would use them.

The line giving you an error is not wrong in any obvious way. You do not mention what the error is, so I am unable to hazard a guess at why you are receiving such an error.

I am assuming that objwkb is defined as a workbook (as listed in your first post, but I don't see the DIM statement in this last post).

After instantiating a new Excel application object Excel is running but without an active workbook, so the next stage is to either add a workbook (which is done using the workbooks.add method just as you are doing), or open an existing workbook (which you listed in the code in post 1). I don't see an error with the code itself - would you mind posting the complete code (with the DIM statements included), please? You could also check that Excel is being opened correctly by setting its Visibility attribute true immediately after creating the application instance - at least then you will know that the 'empty' Excel server is running or not. Oh, I see you do this already...

On your formatting question it is much easier to open an Excel sheet which you have formatted manually to the correct format than it is to re-format a sheet in code. If you have a look at the simplest of examples you will find that they can become quite code-complex very quickly. Here for instance is code for formatting a range (in this case a cellrange object which is passed to a formatting subroutine) with a four-side border line on every cell.

    
    CellRange.Borders(xlDiagonalDown).LineStyle = xlNone
    CellRange.Borders(xlDiagonalUp).LineStyle = xlNone
    With CellRange.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With CellRange.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With CellRange.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With CellRange.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With CellRange.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With CellRange.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

For Excel workbooks with complex formatting my practice is to prepare a pre-formatted Excel workbook sized with as many rows (and columns) as I will likely ever need, open the workbook in Access using Excel as an automation server, transfer Access data into the formatted cells, delete any unused rows and columns, then save the fully-formatted data without applying any formatting at the Access end. It saves a lot of work in the long run.

-Stewart

ps I now recognise the comments in the VBA code in post 1 about setting library references as ones I made myself when assisting a previous poster with Excel automation. I did begin to wonder if those were my code fragments when I saw the recordset transfer done using CopyRecordset after setting the field names. You have actually adapted one of my transfer to Excel routines - no wonder I recognised the object references! Well, all I can say is that the Excel code definitely worked before it was adapted...

[NeoPa]

FKB,

Your post was eloquently expressed, and it's easy for confusion when communicating across a forum page, so I do understand.

Frankly, it will always be hard to deal with even slightly complicated questions in this environment. It is with this in mind that we feel that certain rules must be followed. They are designed, as best we can, to reduce difficulties with communication between members in these circumstances (a Q&A forum).

Things like not posting your question simply as "Here is my code - please sort it out for me", and forgetting to use [ CODE ] tags. I mention these to show that there is very good reasoning behind our responses and not simply to preach.

The former unloads the effort of working out the question onto the person helping you. Resulting in less expert resource being available to answer questions generally. The absence of the latter makes comprehending any code you post that much more difficult.

Getting back to your current problem, I should warn you that, even though some of us do have experience in this area (mine garnered literally over the last couple of weeks) it is nevertheless a fairly advanced and complex area. Certainly not for the feinthearted. This also means that extra care (more than usual) will need to be given to the communication around the matter.

From my reading of Stewart's reply it seems to me that he and I are singing from the same songsheet. Very much along the lines I would have led you down.

In the circumstances (Mary asked for some assistance with this question as her Admin responsibilitie s keep her very busy & she could see this thread getting quite complicated - as do I - and everyone here has such respect for Mary that they all wanted to help) I will happily take a back-seat in this discussion. Too many cooks and all. However, I will continue to monitor it, so that I will get any response directed to me, and I can return to fuller involvement if anyone requests that specifically.

PS. I believe Stewart (in his PS) was referring to the .CopyFromRecord set() call in line #66 of your original post.

[Stewart Ross]

Hi NeoPa. The code is an adaptation of what I posted in post # 6 of this thread. I thought I knew the style of some of the OP's code...

-S

[NeoPa]

Hi NeoPa. The code is an adaptation of what I posted in post # 6 of this thread. I thought I knew the style of some of the OP's code...

-S

Yes. I got that from your earlier post (not the details of course). I just wish I'd seen that a week or two ago. I've just had to work that all out pretty well from scratch at work recently.

It's just at that nearly perfect stage now. A couple of tidy ups still to do, but the process actually works flawlessly (as far as my testing has shown so far at least).

I've always used the more basic Transfer... procedures before, but this time I needed the data to be inserted into a spreadsheet that later had to select a filtered set of the inserted data and then automatically FTP that up to a supplier's site. All that was another story of course.