Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro/Paste Record Excel to Access (97)

    Hi

    I need to automate a simple task - to cut the current selected record in an Excel spreadsheet, paste as new record into an Access table (named "Exclusions"), then delete from whichever Excel worksheet I am using at that time. I want to assign this macro to a keyboard shortbut, perhaps ctrl+o.

    Could someone help me out with the necessary coding please? I can record the macro for the "cut selected record" part but it is the switching to the Access database table (and back again to Excel after pasting) that I cannot achieve. I don't want to do this as an import from within Access as it is only for single records, here and there. I just want to be able to find the record I want, hit ctrl+o and know that that particular record has now moved to the Access table.

    Any input would be greatly appreciated!
    Regards, Sue

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Macro/Paste Record Excel to Access (97)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Sue

    Welcome to the Lounge.

    OK first things first, <font color=red> DO NOT </font color=red> use CTRL+O because this is already an assigned shortcut for Open. Pick something else, perhaps CTRL+ALT+O.

    OK now my question(s) to you are:

    1) Do you want to control MS-Access from MS-Excel, or vice versa? Where do you want the VBA code to be? I think there is evidence in your post that you want to have MS-Excel running and send data to MS-Access.

    2) You say "Cut" and "Paste", do you not want to keep the data in MS-Excel?

    3) You say <<< then delete from whichever Excel worksheet I am using at that time. >>> and <<< the current selected record >>> Well I know that the selected record MUST be on the activesheet, does that mean that you want to delete from the active worksheet, or some other worksheet? This is confusing to me <img src=/S/confused.gif border=0 alt=confused width=15 height=20>.

    The best thing I can say is to investigate the <font color=blue> Get External Data </font color=blue> under your data menu, and write a query that would send the cell values to MS-Access.

    Then the code will simply delete the cell values uploaded to Access.

    This will limit your need to <<< switching to the Access database table (and back again to Excel after pasting) >>>.

    I hope this helps you get started, if you need help with coding the SQL query, send the structure of the database, and the Excel worksheet point out what cell goes to what field if it is not too obviouse.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro/Paste Record Excel to Access (97)

    Are you saying that one can actually use Get External Data to UPDATE or APPEND data to an external database? AFAIK one can only do what its name suggests: GET data.

    She will have to use ADO or similar to achieve this.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro/Paste Record Excel to Access (97)

    Hi Wassim and Jan

    Thanks for your replies. However, I think there must be a simpler solution than to use a query from Excel. I have had a quick go this morning and as Pieter says, cannot append data (at least not to the place I want!) I suppose I could run a query and return the results to another Excel workbook, then export to Access/import from Excel but it is adding another (unnecessary?) step in the process that I want to simplify. In answer to your questions:

    Yes, I want to be able to use in on the active sheet - any active sheet. I have to use hundreds of different workbooks, one at a time, so do not want to have to keep changing the name each time I open one so therefore want a global macro.

    I will run the macro from within Excel, from whichever active sheet I happen to be using.

    Yes, I do want to delete the information from Excel once I have pasted it into the Access table (whose field names match the Excel spreadsheet field names).

    At the moment, I use Ctrl+F to find the record I want, cut the record, switch to the Access table (which is already open), paste it in as the last or a new row, switch back to Excel and delete the row I had cut.

    I have already recorded a macro to do the cutting the selected record, another for deleting the record, but I need the code to do the middle bit, i.e. switch to the Access table, paste as a new row, switch back to Excel.

    Look forward to hearing from you.
    Regards, Sue

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Macro/Paste Record Excel to Access (97)

    Sure Jan Karel

    As long as the SQL code is written to send the data to MS-Access, vs getting it from MS-Access.

    Once you have the SQL code written to update the database, then use the facilities of the Get External Data to run this SQL code.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Macro/Paste Record Excel to Access (97)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Sue

    OK I have something new for you, based on the new information you posted. <<< I have to use hundreds of different workbooks, one at a time >>>

    If all of these workbooks need to go to a single table in a singe database, it would be better to accumulate the data into a worksheet in MS-Excel and then populate the database from this worksheet all at once.

    I will post another message later on, with some ideas.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Macro/Paste Record Excel to Access (97)

    Hi Sue,
    You can do what you're after most effectively using DAO. Something along the lines of:
    <pre>Sub LoadIntoAccess()
    Dim ws As DAO.Workspace
    Dim db As DAO.Database
    Dim rngCell As Range
    Dim strSQL As String, strValues As String
    Set ws = CreateWorkspace("DataLoad", "admin", "", dbUseJet)
    Set db = ws.OpenDatabase("InsertPathToDatabaseHere.mdb", True, False)
    For Each rngCell In Selection
    strValues = strValues & "'" & rngCell.Value & "', "
    Next rngCell
    strValues = Left(strValues, Len(strValues) - 2)
    strSQL = "INSERT INTO tblTest ( Field1, Field2, Field3, Field4, Field5 ) "
    strSQL = strSQL & "VALUES (" & strValues & ");"
    db.Execute strSQL
    db.Close
    Set db = Nothing
    ws.Close
    Set ws = Nothing

    End Sub
    </pre>

    should do the trick. You will need to change paths and field names to match your requirements and it could also do with some error-handling and checks to see you've selected enough data, but hopefully it should get you started.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro/Paste Record Excel to Access (97)

    Hi Wassim

    Many thanks for your response. Sorry about the reply, have been on holiday for a week.

    I don't think your solution will work for my particular needs in that I have to keep the worksheets separate. It would be far too cumbersome a file so I think I will have to go down an alternative route. Thanks for trying anyway!

    Regards
    Sue

  9. #9
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro/Paste Record Excel to Access (97)

    Hi Rory,

    Thanks for your response. This is something more along the lines I was looking for, except ..... I can't get it to work, almost certainly something I have/haven't done! I have altered the path & field names, as follows:

    Sub LoadIntoAccess()
    '
    Dim ws As DAO.Workspace
    Dim db As DAO.Database
    Dim rngCell As Range
    Dim strSQL As String, strValues As String
    Set ws = CreateWorkspace("DataLoad", "admin", "", dbUseJet)
    Set db = ws.OpenDatabase("C:windowsdesktop11 August.mdb", True, False)
    For Each rngCell In Selection
    strValues = strValues & "'" & rngCell.Value & "', "
    Next rngCell
    strValues = Left(strValues, Len(strValues) - 2)
    strSQL = "INSERT INTO DeletionsMailingList (BusinessName, Address1, Address2, Address3, Address4, Address5, Address6, Postcode) "
    strSQL = strSQL & "VALUES (" & strValues & ");"
    db.Execute strSQL
    db.Close
    Set db = Nothing
    ws.Close
    Set ws = Nothing

    End Sub


    I am attaching my trial database and Excel spreadsheet so perhaps you could have a look and see where I'm going wrong. Is it because I'm using it in Access and Excel 2000 (whereas I need to use it at work on the '97 versions)? It doesn't produce any error message, just does not add the record to the Access database.

    Look forward to hearing from you!

    Regards, Sue

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Macro/Paste Record Excel to Access (97)

    Sue,
    I had a quick look at the database and I think I know what the problem is - the fields have Allow Zero Length set to No. The data in your spreadsheet doesn't have anything in the Address4 column and the way the code works, it doesn't check for blanks but simply encloses the value of each cell in quotes. This means that where you have no data, the SQL string actually contains a zero-length string, not Null. You either need to change the table to allow zero-length strings in each field (if appropriate) or you need to revise the code to check each value as it loops through. It should then work OK - I tested it setting the Allow Zero Length to Yes and it ran fine.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Macro/Paste Record Excel to Access (97)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Sue

    Good to have you rested and ready for some more work.

    My solution, in adding all the data into one single worksheet and upload that single worksheet to the database, is a step in the process. It is not a way to change your workbook(s) structure.

    You can still have all the workbook, and worksheets the way you have them today, but consider this:

    1) Work as usual...

    2) When you want to update the database, have the code copy the data from all workbooks/worksheets into a single worksheet, this is very easy and can be very fast in MS-Excel.

    3) Update the database from this single worksheet.

    Hope this shed some more light on the issue.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  12. #12
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro/Paste Record Excel to Access (97)

    Hi Rory,

    Many thanks for your reply. Have altered fields to allow zero length and it now works very well. I've also added in a line to delete the record from Excel once it has been added to the Access database.

    What I want to do now is to combine that macro (now called "InsertIntoAccess" with a "Find ......" feature. I currently hit Ctrl+F to locate the Businessname (which is the first field of the Excel spreadsheet), then activate "InsertIntoAccess". Is it possible to :

    Find name (partial match or full match)
    If found, prompt user to "OK" it (by hitting Enter button, rather than mouse click), then run macro "InsertIntoAccess" or find next (hit "N" key?) if not correct record
    If match not found, msgbox advising not found, then input box requesting next name to search for, or exit.

    I've tried to adapt the Visual Basic FindFirst example, without success, due to limited knowledge!

    If you could provide code, it would be a great help.

    Many thanks, regards, Sue

  13. #13
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro/Paste Record Excel to Access (97)

    Hi Wassim,

    Many thanks for your input. Having tried Rory's solution and found it works, I think I'll stick with that (as per the saying, "If it aint broke, don't fix it!).

    I will, however, consider your solution when we next order in a new database, before it is split into regions.

    Many thanks and regards, Sue

  14. #14
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro/Paste Record Excel to Access (97)

    Hi Rory

    Sorry to bother you again but I've been using the code you supplied and it works very well except when one of the cell values contains an apostrophe. I think the error is somewhere in the following lines:

    strValues = strValues & "'" & rngCell.Value & "', "
    Next rngCell
    strValues = Left(strValues, Len(strValues) - 2)
    strSQL = "INSERT INTO DeletionsMailingList ( BusinessName, Address1, Address2, Address3, Address4, Address5, Address6, Postcode, Notes, Address7, Contactname, F13, F14, F15, F16) "
    strSQL = strSQL & "VALUES (" & strValues & ");"

    I've used the solution in the help files successfully in Access in the past but it is not working with this one.

    Please could you have a look at the above and let me know what you think the problem is.

    The error message I get is "Syntax error (missing operator) in query expression..................."

    Many thanks & regards,
    Sue

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Macro/Paste Record Excel to Access (97)

    Hi Sue,
    Sorry for the delay in responding but Hurricane Fabian is keeping me busy!
    You should be able to solve your problem (assuming you have no data with double-quotes in) by changing the
    <pre>strValues = strValues & "'" & rngCell.Value & "', "</pre>

    line to read:
    <pre>strValues = strValues & Chr(34) & rngCell.Value & Chr(34) & ", "</pre>

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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