Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Exporting from Access to a specific table and range in excel mostly done, need a bit of help

  1. #1
    archy321 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    9

    Exporting from Access to a specific table and range in excel mostly done, need a bit of help

    Hello,



    I am trying to write a bit of VBA that will allow me to transfer an access query to a specific range on an excel template with the touch of a button. I thought i had it correct but I keep running into this error. Compile error: Duplicate declaration in current scope.

    I even tried making a whole new database in case i had some sort of button some where and i am still hitting this error. Below is my coding so far.

    Option Compare Database


    Private Sub Command0_Click()
    Dim MyDatabase As DAO.Database
    Dim MyQueryDef As DAO.QueryDef
    Dim MyRecordset As DAO.Recordset
    Dim i As Integer
    Dim wb As Excel.workbook
    Set MyDatabase = CurrentDb
    Set MyQueryDef = MyDatabase.QueryDefs("April deviations")
    Set wb = xlApp.Workbooks.Open("C:\Users\archy\Documents\fli ght metrics.xlsx")


    Set MyRecordset = MyQueryDef.OpenRecordset
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    With xlApp
    .Visible = True
    .Workbooks.Add
    .Sheets("Sheet1").Select
    .ActiveSheet.Range("A41").CopyFromRecordset MyRecordset
    For i = 1 To MyRecordset.Fields.Count
    xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
    Next i
    xlApp.Cells.EntireColumn.AutoFit
    End With
    'MsgBox "Query has been successful", vbInformation, "Sample"


    End Sub


    The line Private Sub Command0_Click() is highlighted yellow and Dim xlApp As Object is highlighted blue. If anyone could please help me out with this i would be forever grateful!

    V/R
    Archy321

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,647
    Are you sure xlApp isn't declared elsewhere as well? Ctrl-F can help you find them. You're using it before it's set in the set wb line.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,695
    I can't step through the code to see where it errors, but I did notice a couple of things.

    You have (I added the Option Explicit line)

    Click image for larger version. 

Name:	archyBAD.png 
Views:	26 
Size:	106.6 KB 
ID:	35454


    ---------------------------------------


    How I would write the code:

    Click image for larger version. 

Name:	archyBetter.png 
Views:	27 
Size:	162.8 KB 
ID:	35455

    ---------------------------------------

    In the Clean up part, you might have to change the "wb" to "xlApp"
    Code:
        wb.Save
        wb.Close
        xlApp.Quit
        Set xlApp = Nothing
    to xlapp
    Code:
        xlApp.Save
        xlApp.Close
        xlApp.Quit
        Set xlApp = Nothing
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    archy321 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    9
    SSanfu I could hug you! I haven't made any progress on this in the last two days! Its working, but not exactly in the way i was wanting it. Its now opening up a new sheet as well as the destination file. All the info is in the new sheet thats opened up, but none of the info is in the destination file. There is now also a new error that states: Run-time error '1004': save method of application class failed. I tried both versions of the clean up part you suggested, the first suggestion with the wb lines simply closed the destination file and kept the new worksheet and the second one presented the error 1004.



    Option Compare Database
    Option Explicit

    __________________________________________________ __________________________________________________ __________________________________________________ __________________________________________________ __________

    Private Sub Command0_Click()
    Dim MyDatabase As DAO.Database
    Dim MyQueryDef As DAO.QueryDef
    Dim MyRecordset As DAO.Recordset
    Dim i As Integer
    Dim wb As Excel.workbook
    Dim xlApp As Object


    Set MyDatabase = CurrentDb
    Set MyQueryDef = MyDatabase.QueryDefs("April deviations")


    Set MyRecordset = MyQueryDef.OpenRecordset
    Set xlApp = CreateObject("Excel.Application")
    Set wb = xlApp.Workbooks.Open("C:\Users\archy\Documents\fli ght metrics.xlsx")
    With xlApp
    .Visible = True
    .Workbooks.Add
    .Sheets("Sheet1").Select
    .ActiveSheet.Range("A41").CopyFromRecordset MyRecordset
    For i = 1 To MyRecordset.Fields.Count
    xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
    Next i
    xlApp.Cells.EntireColumn.AutoFit
    End With
    'MsgBox "Query has been successful", vbInformation, "Sample"


    'clean up
    'Excel Stuff
    xlApp.Save
    xlApp.Close
    xlApp.Quit
    Set xlApp = Nothing

    'access stuff
    MyRecordset.Close
    Set MyRecordset = Nothing
    Set MyQueryDef = Nothing
    Set MyDatabase = Nothing




    End Sub

  5. #5
    archy321 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    9
    omg, I'm so dumb... I had both Option Compare Database AND Option Explicit typed... The code works! THANK YOU SO MUCH!!!!

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,695
    Quote Originally Posted by archy321 View Post
    omg, I'm so dumb... I had both Option Compare Database AND Option Explicit typed... The code works! THANK YOU SO MUCH!!!!
    I don't understand this. You SHOULD have both lines at the top of EVERY module- standard, form and report.

    Glad you were able to get the code to work......
    Also, Welcome to the forum.


    ------------
    A couple of other things.
    You should NOT have spaces in names - any names.

    Object names should be letters and numbers.
    Do not begin an object name with a number.
    NO spaces, punctuation or special characters (exception is the underscore) in object names


    When posting code, use code tags. Code tags look like this
    [Co de] [/Co de]

    but without the spaces. (had to add spaces to ge able to show the code tags)

    Using the code tags allows formatted code to retain the formatting (like indentation) and won't add a space after 50 characters.
    Here are examples:
    without code tags:
    ---------------------------------------------------------------------------------
    Set wb = xlApp.Workbooks.Open("C:\Users\archy\Documents\fli ght metrics.xlsx")

    With xlApp
    .Visible = True
    .Workbooks.Add
    .Sheets("Sheet1").Select
    .ActiveSheet.Range("A41").CopyFromRecordset MyRecordset
    For i = 1 To MyRecordset.Fields.Count
    xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
    Next i
    xlApp.Cells.EntireColumn.AutoFit
    End With
    ---------------------------------------------------------------------------------


    with code tags:
    Code:
        Set wb = xlApp.Workbooks.Open("C:\Users\archy\Documents\flight metrics.xlsx")
    
        With xlApp
            .Visible = True
            .Workbooks.Add
            .Sheets("Sheet1").Select
            .ActiveSheet.Range("A41").CopyFromRecordset MyRecordset
            For i = 1 To MyRecordset.Fields.Count
                xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
            Next i
            xlApp.Cells.EntireColumn.AutoFit
        End With


    You can type in the tags or you can click the hash in the reply menu (see the red box)
    Click image for larger version. 

Name:	codetags.png 
Views:	20 
Size:	26.6 KB 
ID:	35460

    I copy the code, click on the hash mark, then paste the code.


    Good luck with your project......
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  7. #7
    archy321 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    9
    Thank you for the welcome to the forums and for all the great advice! If its okay, can I ask one more question? If I wanted multiple queries to go into the same excel form should I just make a module for each query with similar coding as above and then docmd them one after another after Command_click?

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,695
    It has been a long while since I've tried to automate Excel from Access.

    Quote Originally Posted by archy321 View Post
    If I wanted multiple queries to go into the same excel form <snip>
    This is not clear to me. Do you want to add data from multiple queries to the SAME worksheet ("Sheet1") in one workbook?
    Or add multiple worksheets to one workbook?
    How many queries?



    Quote Originally Posted by archy321 View Post
    <snip> make a module for each query<snip>
    No!! Depending on what you want, all of the code for multiple queries could/would be in one procedure.
    Speaking of which, what is the purpose of the button? A button name of "Command0" is not very descriptive. I ALWAYS change the name Access gives objects. For example, if I have a list box for employees, Access names the list box (when it is created) something like "List3". I rename it to "lstEmployees".
    In the IDE, which tells you more: "Private Sub List3_Click" or "Private Sub lstEmployees_Click"??


    Looking at you code, you really should learn to add comments. (I did ).
    In 6 months, you are going to look at the code and say <head_slap>"What was I thinking of?" (been there, done that)


    Now that I have had to decipher the code, I have questions.
    Why do you use
    Code:
        Set MyQueryDef = MyDatabase.QueryDefs("April deviations")
        Set MyRecordset = MyQueryDef.OpenRecordset
    when you could just use
    Code:
        Set MyRecordset = MyDatabase.OpenRecordset("April deviations")
    You don't do anything with the query def..........


    You have
    Code:
        Set wb = xlApp.Workbooks.Open("C:\Users\archy\Documents\fli ght metrics.xlsx")
    but you don't use it.


    After you copy the data from the recordset (and put it in the sheet starting at cell A41), why do you then add the header row (field names) at cell A1?





    Ha! Didn't see all those questions coming, did you?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  9. #9
    archy321 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    9
    Hi Ssanfu

    I am wanting multiple queries to be input into a single worksheet. Sorry I said form. The worksheet in my mind is a form. I work at a small airport and my bosses wanted a way to compile large amounts of historical data into a database so that we can see historical trends as well as call out other departments on their shenanigans. I stupidly spoke up and said “Hey we can use Microsoft access, I’ve seen other databases do exactly that thing”. They saw that I was the youngest one in the room and automatically assumed I knew everything about computers and gave me the task. Cue the last two weeks of me frantically googling and researching as much as possible, hence why all the bad habits, why the bit of coding seems so cobbled together (I must have used 20+ different websites by now) you see and why I am ever so grateful for everyone’s, especially yours help.

    One good thing is, is that the forms that were tracking the historical data were completely unchanged for the past 10 years or something like that (I swear they hired me only as an experiment about whether or not millennials are able to work). I figured if I were able to take apart the forms I could enter new data in from access so that every month I don’t have to tear apart the worksheets (individual files for each worksheet…not even a workbook) so I can make charts from the historical button. My coworkers are also very much computer illiterate. One so much so that he refuses the use ctrl+C, which further emphasized that I needed a way to fill in the worksheet with one button. I made about 5 queries that will be able to fill in several parts of the form.

    The purpose of the button that said “command 0” was because I was getting a duplicate declaration error, so I completely started a brand new database and button and still got the error. The database was up at the time so I just copied that bit of coding in and that’s what everyone saw, once a good line of code is made I can do the fun stuff and actually make the layout and nice button names etc.

    The reason for
    Code:
     Set MyQueryDef = MyDatabase.QueryDefs("April deviations")
    Was because a website told me to do it… I am very much a noob, I am so sorry ☹
    After playing around with the code again today I decided to remove the header row haha.
    I think I got all of the questions haha!

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,695
    Quote Originally Posted by archy321 View Post
    I stupidly spoke up and said “Hey we can use Microsoft access, I’ve seen other databases do exactly that thing”. They saw that I was the youngest one in the room and automatically assumed I knew everything about computers and gave me the task.
    Nothing more to add .......


    Quote Originally Posted by archy321 View Post
    I work at a small airport and my bosses wanted a way to compile large amounts of historical data into a database so that we can see historical trends as well as call out other departments on their shenanigans.
    OK, so you have a database created. And data is entered?

    Any chance you can/would post the dB and the Excel workbook for testing?
    (Make a copy of the dB, remove confidential data, do a "Compact & Repair", then zip it. Add the Excel WB to the zip file. Zip file must be smaller than 2MB. Attach the zip file to a reply - in the quick reply click on the "Go Advanced" button. Scroll down to "Attachments" Follow the prompts...)
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  11. #11
    archy321 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    9
    Yes definitely! if its okay with you I'll post it first thing tomorrow morning! I currently have half the data needed on my work computer while i focused on one problem at a time at home.

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,695
    Sounds good. Don't need all records - just enough to be able to see what is going on.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  13. #13
    archy321 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    9
    I had to create a new very much downgraded version of the database as there was too much info to scrub, but i hope that it illustrates what i am trying to accomplish. Btw OT = On, LT = Late and CANX is cancelled. I've also attached a scrubbed copy of the forms we use and how they need to be laid out. Thank again so much for helping Ssanfu!
    Attached Files Attached Files

  14. #14
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,695
    After looking at the spreadsheet, I now understand why "A41" was in the code.

    Quote Originally Posted by archy321 View Post
    I am wanting multiple queries to be input into a single worksheet
    How many queries?
    What are the query names?

    So the first query data is written at cell "A41". The next query should start writing after the last row of "Deviations_currentMonth_Query"?




    There is no code for the "Current Month" button?


    I will have to work on this a while. You have reserved words and illegal characters in object names.

    Could "OT, LT, CX" be called "Status"? If not, what other term could be used?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  15. #15
    archy321 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    9
    Query glance should start at A24

    OT_CM_Query should start at B8

    Weather_CM_Query should start at B4

    The current month button will be exactly the same as the previous month. Some of the queries have criteria to build that form with the current months data and the previous months button is made with the previous months data.

    OT, LT, CX can totally be renamed to status! Any other changes due to legality are very much acceptable!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 09-19-2016, 04:02 PM
  2. Replies: 12
    Last Post: 04-15-2014, 11:16 AM
  3. Exporting to specific spots on excel.
    By sonoamore in forum Programming
    Replies: 1
    Last Post: 12-05-2013, 09:55 PM
  4. exporting access report to specific excel cells
    By grgold14 in forum Import/Export Data
    Replies: 1
    Last Post: 02-07-2012, 03:38 PM
  5. Exporting to Specific Excel Sheet
    By unrealtb in forum Access
    Replies: 2
    Last Post: 01-24-2012, 09:32 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums