Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export? (Access 2000)

    Afternoon All,

    I have the following code and all is well:

    Dim objXL As Excel.Application
    Dim objWkb As Excel.Workbook
    Dim objSht As Excel.Worksheet
    Dim db As Database
    Dim rs As Recordset
    Const conMAX_ROWS = 20000
    Const conSHT_NAME = "Details"
    Const conWKB_NAME = "cocuments and Settingsrnewt01My DocumentsComTrac.xls"

    Set db = CurrentDb
    Set objXL = New Excel.Application
    Set rs = db.OpenRecordset("tblComExport", dbOpenSnapshot)
    With objXL
    .Visible = True
    Set objWkb = .Workbooks.Open(conWKB_NAME)
    On Error Resume Next
    Set objSht = objWkb.Worksheets(conSHT_NAME)
    If Not Err.Number = 0 Then
    Set objSht = objWkb.Worksheets.Add
    objSht.Name = conSHT_NAME
    End If
    Err.Clear
    On Error GoTo 0
    .Range("A65536").End(xlUp).Offset(1, 0).CopyFromRecordset rs

    End With


    Set objSht = Nothing
    objWkb.Close True
    objXL.Application.Quit
    Set rs = Nothing
    Set db = Nothing

    My next step however, requires the user to make updates to a record being stored in Access, (one that was exported out to Excel in the previous code). No problem there. I have all of the features worked out for the Access end of the updates, what I don't have is the "how to update the record in Excel". I don't want to update the spreadsheet with another record, I'd rather update the already existing record within Excel.

    What I'm hoping to accomplish is that someone here can provide me with a point of reference or some suggestions on how to reference, say a "case number" in Access (a field in Access and a field in Excel) and use "Find" in Excel to locate that record.

    Any suggestions?

    Thanks.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Export? (Access 2000)

    It's not clear to me where you want to do the editing - in Excel or in Access. It would probably be easier to do it from Excel, since you could trigger a routine from Excel to update the corresponding record in Access any time a cell was changed. You could do the same thing from a form in Access, and anytime the record changed you could trigger automation code to find the particular record in the workbook (I assume you have a unique identifier that servers as the primary key in Access - if not you need one) and update the existing record. One thing is for sure - you don't want users updating it from both places or you will end up with a real mess. Actually, you could probably do the same function using an attached Excel worksheet as a table in Access, and not require the automation code. In that case, as with all Excel workbooks, if it is open in Access, it will be read-only to all other users.

    I'm curious what the big picture is here - are you trying to avoid using Access on some workstations? If that's the case you might consider creating a run-time Access package - you don't have to buy Access for each workstation that way. In nearly all respects, Access is far better than Excel at doing record kinds of updates - Excel's real strength is in doing analysis and statistical work.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export? (Access 2000)

    Wendell:

    Thanks for your response and sorry for the delay in mine.

    It's not Access I'm trying to bypass, rather Excel. I want to Automate everything through Access.

    Below is the code as I have it now.

    Public Function CTEUpdate(Object As Object)
    Dim objXL As Excel.Application
    Dim objWkb As Excel.Workbook
    Dim objSht As Excel.Worksheet
    Dim oCell As Range
    Dim db As Database
    Dim rs As Recordset
    Dim strCaseNum As String
    Const conMAX_ROWS = 20000
    Const conSHT_NAME = "Details"
    Const conWKB_NAME = "cocuments and Settingsrnewt01My DocumentsComTrac.xls"

    Set db = CurrentDb
    Set objXL = New Excel.Application
    Set rs = db.OpenRecordset("tblComplaint", dbOpenSnapshot)
    strCaseNum = rs.Fields("CaseNum")

    With objXL
    .Visible = True

    Set objWkb = .Workbooks.Open(conWKB_NAME)
    On Error Resume Next

    Set objSht = objWkb.Worksheets(conSHT_NAME)

    If Not Err.Number = 0 Then
    Set objSht = objWkb.Worksheets.Add
    objSht.Name = conSHT_NAME

    End If
    Err.Clear
    On Error GoTo 0
    End With


    With objSht.Range("A65536")
    Set oCell = .Find(strCaseNum, Lookin:=xlValues)
    .CopyFromRecordset rs
    End With


    With objWkb
    .Save
    End With

    Set objSht = Nothing
    objWkb.Close True
    objXL.Application.Quit
    Set rs = Nothing
    Set db = Nothing

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "dqryUpdateConcerns", acNormal, acEdit
    DoCmd.SetWarnings True

    End Function


    The procedure at this point is as follows:

    I want the information from the underlying table to be exported from Access to an Excel spreadsheet. The purpose is because the ppl here aren't very familiar with Access as they are with Excel. This db will be published on the intranet taking in user responses and the data dumped into a spreadsheet.

    Upon occassions, there will be a need for super's to update records, which they will through Access. However, because there is a matching record already in Excel. I need to update the corresponding Excel record with the NEW updated information in Access the super just updated. Hope that is more clear to you as to what is going on.

    Therefore, the code above is what I'm working on. All is working except, the find and paste part. Any suggestions, I will buy you a steak dinner.

    Thanks thanks thanks,


    Roberta
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Export? (Access 2000)

    Hi there!
    In digging through your code, it appears to me the problem is with this section of code:
    <font face="Georgia"><font color=blue>
    With objSht.Range("A65536")
    Set oCell = .Find(strCaseNum, Lookin:=xlValues)
    .CopyFromRecordset rs
    End With
    </font color=blue></font face=georgia>
    For one thing, it appears the range is set to the last possible row in the first column of the worksheet. From there you do a find, to locate the row that contains the CaseNum, which presumable could be anywhere in the worksheet. At that point you try to do the copy recordset command, which should copy everything from tblComplaints into the workbook - at least that's what I get from the example in the Help files. Is that really what you want to do? Or are you simply trying to update the row containing the current record? I guess the $64K question is where this function is getting triggered - at the time a record is updated in Access, or when someone has made a bunch of updates and then clicks a button that says copy changes to Excel <img src=/S/question.gif border=0 alt=question width=15 height=15>

    If the latter is the case, I would recommend using the TransferSpreadsheet command rather than trying to do Automation in Excel. It copies the entire table to a specified worksheet in an Excel Workbook, does it much quicker than recordset manipulations, and involves only minimal code. There is a fundamental problem however in the whole approach - Excel workbooks can only be opened by one person at a time, and if someone has it open already and you try to use either approach it will fail. You will need to write code to trap that specific situation, and of course give the user explicit instructions on what to do in that case.

    Hope this helps some - I know about Omaha steaks, but I don't know much about Kentucky steaks - spent one veerrryyy long night barhopping in Lexington nearly 40 years ago and had to drive to Winston-Salem the next day! Oh the pain!
    Wendell

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Export? (Access 2000)

    Excuse me for butting in like this.

    >>Upon occassions, there will be a need for super's to update records, which they will through Access. However, because there is a matching record already in Excel. I need to update the corresponding Excel record with the NEW updated information in Access the super just updated. Hope that is more clear to you as to what is going on.<<

    Why not just create a new sheet from the Access database everytime? You say that the super updates the DB from time to time, is anybody updating the excel sheet? If they are then suck in the sheet from Excel to Access make the necessary mods for the differences between the two (do this in Access) then create a new sheet.

    I'm probably way off base here.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export? (Access 2000)

    Wendell and Patt,

    Once again thanks for the responses. However, neither will work.

    Wendell, you are correct as far as where the code seems to bomb and also, yes I'm using a command button to trigger the update.

    The reason neither approach will work is as follows (more boring details but probably necessary).

    The Primary form is bound to a table and stores the data in Access. As the user closes the form I have code that sends the information (the NEW record) to Excel. Therefore a new record is added to Excel each time a new record is added to the Access Table. If Access has 351 records then Excel will have 351 records. OK so far.

    Now....if the super comes along and updates the information in Access and he/she will because the Primary form has several "FOLLOW UP" fields specifically for this pupose. When this happens, I have another form that is opened by the super which requestes the Case #. OK so far. Once the record is pulled up, the super updates the information. OK....here is where it gets sticky. This information has to be updated in Excel also....(it has to be this way, as I mentioned earlier, not all ppl know or have Access) that is why I have to export the data to Excel. I can't just export this information to Excel as I did before because that would create a new record in Excel and it's not a new record, the record (the information) has been modified. So I can't use the transferdatabase nor can I create a New worksheet. I REALLY do need to do the FIND record and paste recordset.

    The other option I've been thinking of is sending the rs as I do with my first code which works fine .... then FIND the old record and delete it in Excel. However, that option seems to be more work than the first one.

    LEXINGTON!!!!!!.....I'm offended...I'm in Louisville....GREAT STEAK PLACES HERE! LOL! [img]/forums/images/smilies/smile.gif[/img] The only thing Lexington has is the University and I'm not sure that's so good.....LOL....well OK...they have some great ponies too.....which we will thoroughly enjoy on May 3rd.

    Thanks again.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Export? (Access 2000)

    I could be wrong after 40 years - maybe it was Louisville. But I was with a friend who was attending a university! Anyhow. . .

    <<The other option I've been thinking of is sending the rs as I do with my first code which works fine .... then FIND the old record and delete it in Excel. >>

    I think that may be the better choice - you should be able to still set the range with the Find, and then write each of the individual fields to the Excel record. In that case you shouldn't have to delete the old record (row). The problem with trying to use CopyFromRecordset is that it will write out the entire table since that's what you set the recordset to. I must admit I've never used it either. With Automation we always just write the record a field at a time, or we use the TransferSpreadsheet.

    Just curious - why can't you replace the entire worksheet each time a record is edited in Access? (I would expect it to be about as fast as writing an individual row and saving it.) And I still think you are going to have issues with errors because someone has the workbook open. Meanwhile back at the horse ranch - have a good evening and kick back!
    Wendell

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Export? (Access 2000)

    I presume that no one needs to change the Excel sheet, if I'm right, then you could have a Data Access page to show the details from the Access database on the Web.
    Pat <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export? (Access 2000)

    Hello All,

    Just wanted to update you guys, send thanks to all who provided suggestions and inform the winner of the Steak Dinner.....

    Below is the line of code that took care of the problem:

    .Range("A2..Z10000").Find(strCaseNum, Lookin:=xlValues).Offset(0, -3).CopyFromRecordset rs

    And the winner is ME!.....and I think I will take a cold one with it too!.......Thanks all.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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