+ Reply to Thread
Results 1 to 7 of 7

Copy from excel paste to access box

  1. #1
    Forum Contributor
    Join Date
    03-16-2012
    Location
    Halifax, UK
    MS-Off Ver
    MS Office 365
    Posts
    206

    Copy from excel paste to access box

    Hi
    I have an Excel sheet that runs an Access sheet query to get the data from our enquiries database.
    When the vba runs from excel, it opens access - runs query - pops up a 'Start date' box - then pops a 'End date' box up - then returns the data between these dates - selects all the data - closes query & access - selects A4 on excel - pastes data into excel.

    I would like to be able to automatically copy the start and end dates from excel cells (C1 & C2) and paste them into the start and end date boxes on access.

    Is there a way to do this?

    Here is the code I have that.
    Kieran

    Please Login or Register  to view this content.
    I tried adding something like this, but it won't paste in the box.

    Please Login or Register  to view this content.
    Last edited by kieranm105; 12-19-2018 at 10:13 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Copy from excel paste to access box

    I suspect that you will need to change your VBA to display the SQL statement in the code instead of calling on the QBF query name. In this manner, you can insert the specifics of the start and end into the SQL statement. Currently you have a parameter built into your QBF. The values in C1 and C2 will be the WHERE part of your SQL statement. If you need further assistance, we will need to see the SQL statement that comprises your current Query in Access in order to convert it for VBA.
    Last edited by alansidman; 12-19-2018 at 11:11 AM.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    03-16-2012
    Location
    Halifax, UK
    MS-Off Ver
    MS Office 365
    Posts
    206

    Re: Copy from excel paste to access box

    Hi Alan
    Thanks for the reply, I think this is the code you mention?

    Please Login or Register  to view this content.

    In the 'Where' section I would need to add the excel cell details somehow?
    Kieran

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Copy from excel paste to access box

    In your code, define Start Date and End Date as Date Variables, then use an input box to declare what those variables will be. Then you should be ok.

    Here is what your SQL statement as you presented should look like in VBA

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    03-16-2012
    Location
    Halifax, UK
    MS-Off Ver
    MS Office 365
    Posts
    206

    Re: Copy from excel paste to access box

    Hi Alan
    This is what I have now, is this correct?

    Please Login or Register  to view this content.
    Last edited by kieranm105; 01-03-2019 at 11:39 AM.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Copy from excel paste to access box

    Without being able to test your code, I am guessing, but you will need delete the DoCmd lines surrounding the SQL statement, but will probably need to add a QueryDef line. Have you tested and does it work or are you getting error codes.

  7. #7
    Forum Contributor
    Join Date
    03-16-2012
    Location
    Halifax, UK
    MS-Off Ver
    MS Office 365
    Posts
    206

    Re: Copy from excel paste to access box

    Nothing gets input into the Start Date box for it to continue.

    I've removed the 4 docmd lines and now get paste error.
    Last edited by kieranm105; 01-03-2019 at 12:24 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need Access macro to copy data from one table and paste into another.
    By wes675 in forum Access Tables & Databases
    Replies: 2
    Last Post: 01-21-2014, 02:18 PM
  2. Copy and Paste Error From Access to Excel
    By google2155 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-29-2013, 03:24 PM
  3. Automate copy and paste and copy and paste back to excel
    By Bmw318be in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-23-2013, 05:42 AM
  4. Automate copy and paste and copy and paste back to excel
    By Bmw318be in forum Access Tables & Databases
    Replies: 0
    Last Post: 02-23-2013, 05:42 AM
  5. Replies: 1
    Last Post: 01-16-2013, 05:36 AM
  6. Copy/Paste Excel data into Access
    By jay567 in forum Access Tables & Databases
    Replies: 6
    Last Post: 12-07-2011, 01:40 AM
  7. Copy from Excel to Access, delete data from DB before paste.
    By jasocke2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-21-2009, 06:37 AM
  8. paste Excel to Access
    By PlanetGong in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-09-2008, 07:51 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1