Access / Python Question

[johnseito]

Ajax thanks for the message,

I actually resolve the first code, that uploads excel worksheet into access database and it works.
below is the code.

'upload excel worksheet into access database
Dim fname As String
ActiveWorkbook.Save
fname = myPath + "Dumader.xlsm"






Set cn = CreateObject("ADODB.Connection")
  dbPath = Application.ActiveWorkbook.Path & "\CPdad.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
  cn.Execute "INSERT INTO tbload SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & ActiveWorkbook.FullName & "]" & ".[Sheet1$A1:L4]"


  Set cn = Nothing

I also actually solved the second code that export the database table into excel worksheet too, and it works, this code below.

Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
      
    strFilePath = Application.ActiveWorkbook.Path & "\Coad.accdb"
  
    cnn.Open "provider='Microsoft.ACE.OLEDB.12.0';" & _
            "Data Source = " + strFilePath
    sQRY = "SELECT * FROM tblCupload"
  
    rs.CursorLocation = adUseClient
    rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly
    Application.ScreenUpdating = False
    Sheet1.Range("A2").CopyFromRecordset rs
    
    cnn.Close

all i need now is to create a python file and export the access database table into a newly created excel worksheet and save it in the directory.

[CJ_London]

surprised that works - excel 8 is .xls, not .xlsm, but if it works, it works

good luck with your project - I cannot help with python

[johnseito]

Ok, thanks !