Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87

    Unanswered: How do I make this promising code do what I want?

    Hey everyone. It seems like I'm on here every night with a new question, sorry for that. I'm new to this and have found tremendously useful help on this forum. Not only does my problem get solved, but I have also learned WHY the solution worked. Anyway, my problem for tonight is as follows:

    I have a form that creates a new record in a table. I'd like for it to serve one more purpose. When the record is created, I'd like to export the information from that record into a predetermined Excel workbook, into a predetermined tab (worksheet?). This will all be done (hopefully) with the On_Click of a command button. I was looking through the code bank, which is super helpful if you know what you're looking for, when I came across a code that I believe will help. I simply need to know where to put this code, and what to put in the obvious "modify me" line. All I want for it to do is paste the information. I don't need formatting or anything, as of right now. All of this will be handled by my buddy who is an Excel wizard. The code is as follows.

    Code:
    Dim AppXL As Excel.Application
    Dim BookXL As Excel.Workbook
    Dim FilePath As String
    
    Set AppXL = New Excel.Application
    FilePath = "c:\myfile.xls"
    With AppXL
    .Workbooks.Open FileName:=FilePath 
    .Sheets("MySheet").Select
    .Rows("1:1").Select
    ' do whatever you want to do with the selected sheet here
    .ActiveWorkbook.Save
    .ActiveWorkbook.Close
    .Quit
    End With
    Set AppXL = Nothing
    There's some obvious editing in there that needs to be done, but from the looks of it, I should be able to fairly easily select the desired excel workbook and worksheet. The line of code that I don't know what to do with is the one with the comment. Also, the code as a whole: where does it go? can it simply be made into a macro or part of the On_Click function? Sorry if these are n00b questions. I'm a n00b.

    EDIT:

    Also, I'd like the original Excel workbook to be "readonly", and when this code is ran, it prompts you to save it with a different name. That way, the blank "template" workbook will be preserved and each record that is exported into Excel is saved as it's own Excel workbook. Is it as simple as changing "Save" to "SaveAs"?
    Last edited by th3spankst3r; 03-25-10 at 00:59.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Was it from here?
    http://www.dbforums.com/6222281-post4.html
    I am guessing not since what you want to do is in there.
    I would download it, run it and if it does the sort of thing you want (hint: it does) then examine the code.
    In short though, populate a recordset and use the CopyFromRecordset method of Excel.

  3. #3
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    Unless I'm overlooking some functionality to that example, it doesn't exactly do what I want it to do. I don't want to export the entire table. Just the record created by my form. IE I want the contents of each control on the form to be pasted into its own cell on a predefined "import" worksheet from a predefined workbook. that way, my buddy can program the main tab to format the information in the way that he needs it. From what I can tell, your example exports the entire table.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you are exporting the row to a row in Excel then it is the same code, only with a WHERE clause for the recordset.

    If you need to put the data into different cells all over the worksheet then the code is still there, you just need to be a wee bit more lateral:
    Code:
    exSheet.Cells(1, i + 1).Value = rs.Fields(i).Name
    This is writing the name of the recordset field to a specific cell. You just need to change the numbers and get the values from your form.

    Something like:
    Code:
    exSheet.Cells(12, 40).Value = Me.mycontrol.value

  5. #5
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    I'm sorry. haha looking at your code simply confuses me more

    this line:

    Code:
    exSheet.Cells(12, 40).Value = Me.mycontrol.value
    I'm pretty sure I would change it to value = rs, according to your code, right? so that those cells' values equals the record set?

    and then change this:
    Code:
    Set rs = db.OpenRecordset("SELECT CategoryName, ProductName, SupplyerName, CustomerName, TotalSale FROM MyRawSalesData")
    to use a condition:
    Code:
    WHERE [FieldName]!Value = [ControlName]!Value
    Is that what you're suggesting?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - let's step back a bit and go over things one at a time.

    Are you exporting your data into a row in Excel?
    Or are you exporting one value in to one cell, and another value in to another unrelated cell , and another value...and so on?

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or take a step further back and tryo to establish the business requirement for exporting data to Excel.. it may make more sense to look at the Excel requirement and see if that can be better met by something in Access, or change the Excel model to grab the data on demand, or possibly on loading the spreadsheet. the big issue I see with Excel is that you can have read/write conflicts if someone has the excel model open at the same time. if you try to post a value at the same time, then there is a risk that the value will not get posted.

    if it were me I'd be tempted to deploy and Access solution first, then if that wasn't acceptable I'd go for a on demand load of data in Excel
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    pootle flump:
    Exporting into a row is fine with me. I don't care how the information is arranged on the worksheet, as long as each control from the form has it's own cell in the worksheet. IE I dont want them all pasted into one or two cells.

    healdem:
    If that's possible, it would be much better due to what you're saying. Access is a multi-user application, so running it off of a shared network drive like I am doesn't affect it as much. Excel, unless I'm mistaken, is not. The only reason I leaned toward exporting to Excel is because I'm very new to this and with exporting, I was at least able to formulate a plan as to how it would be done. Writing the code may be a different story, but I knew what I would need to do. The same cannot be said for the other method.

    All:
    I've attached a copy of my .mdb and a copy of the Excel document. Maybe that will help. If you take a look at the excel document, this format on the "ERO" worksheet is called just that. An Equipment Repair Order (ERO). Every record in my database will have an associated ERO, as this is essentially what my database keeps track of, anything in the cycle of maintenance. I have a buddy who is a wizard with Excel and he says that if each record, created by the Induct Gear form, is put into the "Test" worksheet in the same format as the last, regardless of the original format, he can program the ERO worksheet to pull the information and put it into the correct cells to fill out the ERO automatically. It would be nice if, when this happened, it prompted the user to save the file as a new name, or even saved it automatically with the contents of certain cells, IE the contents of the "ERO Number" control from the form. Eventually, I will have a control to edit the EROs as well, but that doesn't seem to be nearly the amount of challenge that this is.

    Dang, it won't let me attach either one. maybe my explanation helps though.

  9. #9
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    here we go.
    Attached Files Attached Files

  10. #10
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    This seems like it would work for me. However, it gives me an error that says "Too few parameters. Expected 2." It doesn't tell me where, even while debugging. I'm assuming it's talking about where I refer to Range ("1"). I don't know how else to reference just the first row.

    Code:
    Private Sub InductGear_OpenEROButton_Click()
    On Error GoTo Err_InductGear_OpenEROButton_Click
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim AppXL As Excel.Application
        Dim BookXL As Excel.Workbook
        Dim FilePath As String
        
        Set db = Application.CurrentDb
        Set rs = db.OpenRecordset("SELECT * FROM [In Maintenance] WHERE Tables![In Maintenance]![ERO Number] = Forms![Induct Gear]![ERO Number]")
    
        Set AppXL = GetObject("S:\1 MLG\CLR-17\Comm Co\Maint Plt\Electronic Forms\Test.xls", Excel.Workbooks)
        AppXL.Visible = True
        
        With AppXL
        .Sheets("Import").Select
        .Range("1") = rs.Fields
        .ActiveWorkbook.Save        'possibly .SaveAs ThisWorkbook.Path & "\" & (cell number who's value equals Forms![Induct Gear]![ERO number])
        End With
        Set AppXL = Nothing
        
        rs.Close
        
        
        DoCmd.GoToRecord , , acNewRec
        
    
    Exit_InductGear_OpenEROButton_Click:
        Exit Sub
    
    Err_InductGear_OpenEROButton_Click:
        MsgBox Err.Description
        Resume Exit_InductGear_OpenEROButton_Click
        
    End Sub
    Last edited by th3spankst3r; 03-27-10 at 18:09.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That's a DAO error and means your SQL is wrong.
    You should be able to step through code and then you'll learn exactly where the error is.
    Code:
        Set rs = db.OpenRecordset("SELECT * FROM [In Maintenance] WHERE [ERO Number] = " & Forms![Induct Gear]![ERO Number]
    I assume EOR Number is a numeric data type

  12. #12
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    no, it's a string. it includes a 3 letter prefix and 2 number suffix. I tried stepping through the code, it doesnt give me the error until after the sub function is complete.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    Set rs = db.OpenRecordset("SELECT * FROM [In Maintenance] WHERE [ERO Number] = '" & Forms![Induct Gear]![ERO Number] & "'"

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by th3spankst3r View Post
    I tried stepping through the code, it doesnt give me the error until after the sub function is complete.
    By any chance does it tell you the error a little before it completes, i.e. here:
    Code:
    MsgBox Err.Description

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The following code should perhaps do the work:
    Code:
    Function ExportToExcel(ByVal DataObject As String, ByVal FileName As String, ByVal CreateExcelFile As Boolean, _
                           Optional ByVal SheetName As String = "Sheet1", Optional ByVal IncludeHeader As Boolean = False) As Long
    
    ' Purpose:      Export a data set to an Excel file using OLE.
    ' -------
    '
    ' Dependencies: Must have a reference to the Microsoft Excel Object Library.
    ' ------------
    '
    ' Parameters:   DataObject: Can be the name of a table or query, or a valid SQL SELECT statement.
    ' ----------    FileName: The name of the .xls file to create.
    '               CreateExcelFile: If True, create a new file, else une an existing one.
    '               SheetName: Name of the sheet to export to (default = "Sheet1").
    '               IncludeHeader: If True, the first line of the Excel sheet will contain the columns name.
    '
    ' Usage:        Table: ExportToExcel "Tbl_Customers", "Customers.xls", True
    ' -----         Query: ExportToExcel "Qry_Customers", "Customers.xls", True
    '               SQL:   ExportToExcel "SELECT * FROM Tbl_Customers ORDER BY Name;", "Customers.xls", True
    '
    ' To do:        - Handle errors.
    ' -----         - Properly handle the case when there are more than 26 columns in the data set.
    '
        Dim appXL As Excel.Application
        Dim rst As DAO.Recordset
        Dim fld As DAO.Field
        Dim intRow As Integer
        Dim strcell As String
        
        Set rst = CurrentDb.OpenRecordset(DataObject, dbOpenSnapshot)
        Set appXL = New Excel.Application
        With appXL
            .Visible = True ' Optional: don't use in "quiet" mode.
            If CreateExcelFile = True Then
            
        '   Create a new Excel file.
        '
                .Workbooks.Add
                .Sheets.Add
                .ActiveSheet.Name = SheetName
            Else
        
        ' Use an existing Excel file.
        '
                .Workbooks.Open FileName
                .Sheets(SheetName).Select
            End If
            intRow = 1
            If IncludeHeader = True Then
                For Each fld In rst.Fields
                    Select Case fld.OrdinalPosition
                        
                        ' Should use a more generalist algorithm
                        ' that would be able to handle all cases.
                        '
                        Case 0 To 25    ' Cells "An" to "Zn"
                            strcell = Chr(65 + fld.OrdinalPosition) & CStr(intRow)
                        Case 26 To 51   ' Cells "AAn" to "AZn"
                            strcell = "A" & Chr(65 + fld.OrdinalPosition) & CStr(intRow)
                        Case Else ' etc.
                    End Select
                    .Range(strcell).Select
                    .ActiveCell.FormulaR1C1 = fld.Name
                Next
                intRow = intRow + 1
            End If
            Do Until rst.EOF
                For Each fld In rst.Fields
                    Select Case fld.OrdinalPosition
                        
                        ' See above.
                        '
                        Case 0 To 25
                            strcell = Chr(65 + fld.OrdinalPosition) & CStr(intRow)
                        Case 26 To 51
                            strcell = "A" & Chr(65 + fld.OrdinalPosition) & CStr(intRow)
                    End Select
                    .Range(strcell).Select
                    .ActiveCell.FormulaR1C1 = fld.Value
                Next
                rst.MoveNext
                intRow = intRow + 1
            Loop
            rst.Close
            Set rst = Nothing
            .ActiveWorkbook.SaveAs FileName
            .Quit
        End With
        Set appXL = Nothing
    
    End Function

    Another solution would consist in using named cells in the Excel sheet, each name being the same as the name of a column you want to export from the recordset. You can then enumerate the named cells and export the matching columns.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •