Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419

    Access / Python Question

    Hello Guys,

    I was wondering, does anyone know how I could export Access table from access into Excel using Python codes ?



    Could you provide coding examples

    I am not sure if this is a good place to look for it since it is dealing with Access database and other coding language, I thought I ask, maybe someone has
    work with it before.

    Thank you, appreciate it !

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I know a bit about python, but not used it in the way you require.

    you can create a macro in access to export to excel then use python to run the macro by including the name in the command line, but not sure if this is what you want.

    You may get better answers, particularly as you want coding examples, if you explain in more detail the background to your requirement

  3. #3
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Hi Ajax,

    Thanks for the message, I was thinking create a python file and when I run the python file it will export an access table into excel worksheet.

    The access table and database are already created, with a path and the access name such as dataload.accdb, the access table for example is name as
    tableA. I just want to use python code in a python file that when press F5, it will export the access table into a newly created excel file.

  4. #4
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    but could you show me an example of your way and I take a look at it ?

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Sorry, thought I was clear - I don't have a 'way' since I've never used python in that way.

    If you are asking how to transfer data using vba or a macro, look at the transferspreadsheet function.

    as for how to run that from python, you would open the access db with a switch to run the macro - see this link as an example for the commandline prompt which you should be able to replicate in python

    https://bytes.com/topic/access/answe...-access-module

  6. #6
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Hi Ajax,

    thanks for your message again, but I am also trying to upload excel sheet into a predefined access table,
    with this code below but is not working. I believe the bottom code only works when it is written from ACCESS vba.

    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblC_upload", _fname, True, "Sheet1!A1:L3"
    Do you know how I could use excel vba to upload excel sheet into access table ?
    I have search via website but it seems to always the code on top which is via access database.
    Thanks !

  7. #7
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    I have a similar thread here ! Thanks

    http://www.mrexcel.com/forum/excel-q...on-access.html

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you originally said
    how I could export Access table from access into Excel using Python codes
    the code you provided would run in access and export from access to excel

    but now you are saying
    excel vba to upload excel sheet into access table ?
    which is it you want to do?

  9. #9
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    the code you provided would run in access and export from access to excel

    I noticed that after looking at it carefully.

    I want to
    1 - use excel vba to upload excel worksheet with information onto ms access table that is already created with a path
    2 - use excel vba to export access table (a table, database is already created in a directory) into excel worksheet
    3 - use python code to upload excel worksheet with information onto ms access table that is already created with a path
    4 - use python code to export access table (already created with a path) into excel worksheet

    if I can do these that would be good, it doesn't seem very easy since I have search the web for it to almost no avail so far.
    with python some module needs to be install and import, after doing so some can't be install because some files are needed that are not available etc.

  10. #10
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    if I can do just 1, 2, and 4 would be good. I think 3 is more difficult than all of them.

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    1 - use excel vba to upload excel worksheet with information onto ms access table that is already created with a path
    see this link
    http://stackoverflow.com/questions/1...s-access-table

    2 - use excel vba to export access table (a table, database is already created in a directory) into excel worksheet
    not clear if you mean the current open excel file or another file - see this link for a current file
    http://stackoverflow.com/questions/1...heet-excel-vba


    3 - use python code to upload excel worksheet with information onto ms access table that is already created with a path
    4 - use python code to export access table (already created with a path) into excel worksheet
    no examples for python code but this link may help
    http://stackoverflow.com/questions/2...-or-batch-file

    Suggest learn to google properly - all the above links I found in a few seconds by googling something like 'excel vba import from access' or 'commandline execute excel macro'

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    by googling 'python copy data from access to excel'

    I found this link

    http://stackoverflow.com/questions/7...l-using-python

  13. #13
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    I have a "method execute of object _ connection" failed after running the below code
    for moving excel sheet into access table.

    i think it fail with this statement
    Code:
    ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
    Code:
    Public Sub DoTrans()
    
    
      Set cn = CreateObject("ADODB.Connection")
      dbPath = Application.ActiveWorkbook.Path & "\CPoad.accdb"
      dbWb = Application.ActiveWorkbook.FullName
      dbWs = Application.ActiveSheet.Name
      scn = "provider='Microsoft.ACE.OLEDB.12.0';" & _
                "Data Source = " + dbPath
      dsh = "[" & Application.ActiveSheet.Name & "$]"
      cn.Open scn
        
      ssql = "INSERT INTO tblP_upload ([Count], [MT], [FPmount], [Fold]) "
                                                                          
      ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
    
    
      cn.Execute ssql  
      Set cn = Nothing
    
    
    End Sub

  14. #14
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    I change this code

    Code:
     ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
    to this

    Code:
      cn.Execute "INSERT INTO tblp_upload ([Count], [MT])" & _             "SELECT FPP.Count, FPP.MT FROM FPP"
    FPP is a name range in excel, and I have error MS office access database engine cannot find the input table or query 'FPP'. Make sure it exists and that its name is spelled correctly.

    I follow from this thread - http://www.mrexcel.com/forum/excel-q...ess-table.html

  15. #15
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    quite an old thread - probably based on .xls so probably the excel version - see this link

    http://www.rondebruin.nl/win/s9/win012.htm

    Also be aware that count is a reserved word, using it for field, table or sub/function names can cause issues

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

Similar Threads

  1. Not an Access Question
    By MaineLady in forum Access
    Replies: 0
    Last Post: 03-14-2015, 01:50 PM
  2. Replies: 4
    Last Post: 12-14-2013, 06:20 PM
  3. RunApp to Open a python file
    By raytackettsells in forum Programming
    Replies: 1
    Last Post: 10-16-2013, 12:26 PM
  4. Converting Access Backend from Access 2003 to SQL Question
    By seattlebrew in forum Database Design
    Replies: 0
    Last Post: 03-07-2013, 07:14 PM
  5. Access Question!
    By gn987654 in forum Access
    Replies: 1
    Last Post: 12-10-2008, 03:30 PM

Posting Permissions

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