By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

Access to Excel

P: 4
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Excel_Click()
  2. 'Be sure to set your References vis Tools on the Menu Bar to
  3. 'Microsoft Excel 10.0 Object Library or to what ever
  4. 'version excel you are running
  6.     Dim rs As Recordset
  7.     Dim intMaxCol As Integer
  8.     Dim intMaxRow As Integer
  9.     Dim objXL As Excel.Application
  10.     Dim objWkb As Workbook
  11.     Dim objSht As Worksheet
  12.     Dim f As field
  13.     Dim i As Long
  14. Dim objXLApp As Object
  15. Dim objXLws As Object
  16. Dim db As DAO.Database
  17. Dim rst As DAO.Recordset
  18. Dim strDocPath
  19. Dim strPath As String
  21.     Dim sSql As String
  22.     Dim sCriteria As String
  24.         sCriteria = "WHERE 1=1 "
  26.         If Me![cboFilterPONumber] <> "" Then
  27.                 sCriteria = sCriteria & " AND  [Milestone Serch Query].[Purchase Order] = """ & cboFilterPONumber & """"
  28.         End If
  29.         If Me![cboFilterDescription] <> "" Then
  30.             sCriteria = sCriteria & " AND  [Milestone Serch Query].Description like """ & cboFilterDescription & "*"""
  31.         End If
  32.         If Me![cboFilterBuilding] <> "" Then
  33.                 sCriteria = sCriteria & " AND  [Milestone Serch Query].Building Like """ & cboFilterBuilding & "*"""
  34.         End If
  35.         If Me![txtStartDate] <> "" And txtEndDate <> "" Then
  36.             sCriteria = sCriteria & " AND  [Milestone Serch Query].[Ship Date] between #" & Format(txtStartDate, "dd-mmm-yyyy") & "# and #" & Format(txtEndDate, "dd-mmm-yyyy") & "#"
  37.         End If
  38.         If Me![txtFilterTAG] <> "" Then
  39.             sCriteria = sCriteria & " AND  [Milestone Serch Query].TAG like """ & txtFilterTAG & "*"""
  40.         End If
  42.         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
  43.         Forms![frmSearchCriteriaMain]![Milestone Serch Query subform].Form.RecordSource = sSql
  44.         Forms![frmSearchCriteriaMain]![Milestone Serch Query subform].Form.Requery
  47.         Set rs = CurrentDb.OpenRecordset(sSql, dbOpenSnapshot)
  48.             intMaxCol = rs.Fields.Count
  49.             If rs.RecordCount < 1 Then
  50.                 MsgBox "The report you are trying to produce does not contain any data!" & vbCr & vbCr & _
  51.                 "Please check that there is data for this report.", vbCritical
  52.             Else
  55. ' Populate the excel object
  56. Set objXLApp = CreateObject("Excel.Application")
  57. ' Open the template workbook
  58. objXLApp.Workbooks.Open ("Milestone Serch Query")
  59. ' Save the template as the file specified by the user
  60. objXLApp.ActiveWorkbook.SaveAs ("Milestone Serch Query")
  61. ' Select the 'Raw Data' worksheet
  62. Set objXLws = objXLApp.ActiveWorkbook.Worksheets(1)
  63. ' Activate the selected worksheet
  64. objXLws.Activate
  65. ' Ask Excel to copy the data from the recordset
  66. objXLws.Range("A1").CopyFromRecordset rs
  67. ' Select the main worksheet
  68. objXLApp.Worksheets("Milestone Serch Query").Activate
  69. ' Activate the selected worksheet
  70. Set objXLws = objXLApp.ActiveWorkbook.Worksheets("Milestone Serch Query")
  71. ' Populate the criteria box on the main form (so the user knows what was exported)
  72. objXLws.Cells(1, 2).Value = sCriteria
  73. End If
  75. ' Destroy the recordset and database objects
  77. Set rst = Nothing
  78. Set db = Nothing
  80. ' Hide warnings on the spreadsheet
  81. objXLApp.DisplayAlerts = False
  82. ' Refresh the root PivotTable (which refreshes all)
  83. objXLApp.ActiveWorkbook.Save
  84. ' Turn spreadsheet warnings back on
  85. objXLApp.DisplayAlerts = True
  86. ' Make it visible
  87. objXLApp.Visible = True
  89. '**error handling, in the function exit  - make sure you set the object references to nothing as shown below.
  91. FunctionExit:
  93. Set objXLws = Nothing
  94. Set objXLApp = Nothing
  96. End Sub
Aug 11 '08 #1
Share this Question
Share on Google+
14 Replies

Expert Mod 10K+
P: 14,534
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?
Aug 11 '08 #2

P: 4
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.
Attached Files
File Type: zip (55.8 KB, 59 views)
Aug 12 '08 #3

Expert Mod 15k+
P: 31,186
I'll try saying it :
What is not working where in your code?
Aug 12 '08 #4

P: 122
Subscribing. And wishing there were a way to subscribe more discreetly. =)
Aug 13 '08 #5

Expert Mod 10K+
P: 14,534
Subscribing. And wishing there were a way to subscribe more discreetly. =)

Check under the first post. Look for a link saying Subscribe. :D
Aug 13 '08 #6

P: 122
Well, I'll be darn! Thanks msquared! =)
Aug 13 '08 #7

P: 4
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
Aug 13 '08 #8

Expert Mod 15k+
P: 31,186
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 CopyFromRecordset 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.
Aug 14 '08 #9

Expert 100+
P: 112

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:
Expand|Select|Wrap|Line Numbers
  1. Public Function BuildWhereClause() As String
  2.         Dim strCriteria As String
  3.         Dim strShipDate As String, strArriveDate As String
  4.         If Me.txtStartDate <> "" And Me.txtEndDate <> "" Then
  5.             strShipDate = "[Ship Date] between #" & CDate(Me.txtStartDate) & "# AND #" & Me.txtEndDate & "#"
  6.         End If
  7.         If Me.txtARStartDate <> "" And Me.txtAREndDate <> "" Then
  8.             strShipDate = "[Ship Date] between #" & CDate(Me.txtARStartDate) & "# AND #" & Me.txtAREndDate & "#"
  9.         End If
  10.         If Me.cboFilterPONumber <> "" Then
  11.             strCriteria = strCriteria & "[Purchase Order] = '" & Me.cboFilterPONumber & "' AND "
  12.         End If
  13.         If Me.txtFilterTAG <> "" Then
  14.             strCriteria = strCriteria & "[Tag Number]='" & Me.txtFilterTAG & "' AND "
  15.         End If
  16.         If Me.cboFilterDescription <> "" Then
  17.             strCriteria = strCriteria & "[Description]='" & Me.cboFilterDescription & "' AND "
  18.         End If
  19.         If Me.cboFilterBuilding <> "" Then
  20.             strCriteria = strCriteria & "[Building]='" & Me.cboFilterBuilding & "' AND "
  21.         End If
  22. 'We know there will be an extra " AND " at the end so strip it out
  23.         If strCriteria <> "" Then
  24.             BuildWhereClause = Left(strCriteria, Len(strCriteria) - 5)
  25.         Else
  26.             BuildWhereClause = ""
  27.         End If
  28. 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:
Expand|Select|Wrap|Line Numbers
  2. Private Sub Excel_Click()
  3.         Dim strQuery As String
  4.         Dim strWhere As String
  5.         strWhere = BuildWhereClause
  6.         If strWhere <> "" Then
  7.             strQuery = "SELECT * FROM [Milestone Serch Query] WHERE " & strWhere
  8.         Else
  9.             strQuery = "[Milestone Serch Query]"
  10.         End If
  11.         'now export to excel
  12. 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.
Aug 15 '08 #10

P: 4
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:
Expand|Select|Wrap|Line Numbers
  1. Set rs = CurrentDb.OpenRecordset(sSql, dbOpenSnapshot)
  2.             intMaxCol = rs.Fields.Count
  3.             If rs.RecordCount < 1 Then
  4.                 MsgBox "The report you are trying to produce does not contain any data!" & vbCr & vbCr & _
  5.                 "Please check that there is data for this report.", vbCritical
  6.             Else
  7.                 If rs.RecordCount > 0 Then
  8.                     rs.MoveLast:    rs.MoveFirst
  9.                     intMaxRow = rs.RecordCount
  10.                     Set objXL = New Excel.Application
  11.                   With objXL
  12.                             .Visible = True
  13.                             Set objWkb = .Workbooks.Add <--Error Here
  14.                             Set objSht = objWkb.Worksheets(1)
  15.                         With objSht
  16.                                 For i = 0 To rs.Fields.Count - 1
  17.                                     objSht.Cells(1, i + 1).Value = rs.Fields(i).Name
  18.                                     Next i
  19.                                 With objSht
  20.                                    .Range(.Cells(2, 1), .Cells(intMaxRow + 1, intMaxCol)).CopyFromRecordset rs
  21.                                 End With
  22.                         End With
  23.                   End With
  24.                 End If
  25.             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.
Aug 15 '08 #11

Expert Mod 2.5K+
P: 2,545
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.
Expand|Select|Wrap|Line Numbers
  2.     CellRange.Borders(xlDiagonalDown).LineStyle = xlNone
  3.     CellRange.Borders(xlDiagonalUp).LineStyle = xlNone
  4.     With CellRange.Borders(xlEdgeLeft)
  5.         .LineStyle = xlContinuous
  6.         .Weight = xlThin
  7.         .ColorIndex = xlAutomatic
  8.     End With
  9.     With CellRange.Borders(xlEdgeTop)
  10.         .LineStyle = xlContinuous
  11.         .Weight = xlThin
  12.         .ColorIndex = xlAutomatic
  13.     End With
  14.     With CellRange.Borders(xlEdgeBottom)
  15.         .LineStyle = xlContinuous
  16.         .Weight = xlThin
  17.         .ColorIndex = xlAutomatic
  18.     End With
  19.     With CellRange.Borders(xlEdgeRight)
  20.         .LineStyle = xlContinuous
  21.         .Weight = xlThin
  22.         .ColorIndex = xlAutomatic
  23.     End With
  24.     With CellRange.Borders(xlInsideVertical)
  25.         .LineStyle = xlContinuous
  26.         .Weight = xlThin
  27.         .ColorIndex = xlAutomatic
  28.     End With
  29.     With CellRange.Borders(xlInsideHorizontal)
  30.         .LineStyle = xlContinuous
  31.         .Weight = xlThin
  32.         .ColorIndex = xlAutomatic
  33.     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.


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...
Aug 15 '08 #12

Expert Mod 15k+
P: 31,186

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 responsibilities 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 .CopyFromRecordset() call in line #66 of your original post.
Aug 15 '08 #13

Expert Mod 2.5K+
P: 2,545
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...

Aug 15 '08 #14

Expert Mod 15k+
P: 31,186
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...

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.
Aug 15 '08 #15

Post your reply

Sign in to post your reply or Sign up for a free account.