Update excel from access

[diegomarino]

Hi,
i found something in the forum but i'm not so good to understand a partial example.

i want to update some excel files from my access database, both inserting new rows than updating existing rows. i have key fields naturally



another related question is on excel tables. i'd like to keep "clean" those excel files in which i need tables to manipulate datas in another app, so i'd like to shrink tables when i delete rows, is it possibile?

[June7]

Why use Excel as a database?

Have to use VBA Excel automation to edit data or structure in Excel.

About the Excel tables - can only say "probably".

Use Excel macro recorder to generate code and adapt as needed for use in Access procedure.

[diegomarino]

I use Excel to display data on my phone with powerapps

About access you are suggesting to take the code on excel and adapting it on access. I'll try it thank

[Micron]

If there are records in Access table(s) that are not in spreadsheet AND you have not modified the rows in Excel, you should just be able to replace the existing sheet with your current Access query/table records by simply doing TransferSpreadSheet function. Easier than Automation?

If you create Excel macros you can run them from Access if that would make the whole operation easier to do from Access.

[diegomarino]

If there are records in Access table(s) that are not in spreadsheet AND you have not modified the rows in Excel, you should just be able to replace the existing sheet with your current Access query/table records by simply doing TransferSpreadSheet function. Easier than Automation?

If you create Excel macros you can run them from Access if that would make the whole operation easier to do from Access.

unfortunely is not so simple, powerapps needs table in excel to use data, so i can't export every time my tables, unless there is a way to make an excel table exporting the access.however i found a course on udemy that fits my need i hope

[ssanfu]

Have you looked at Ken's ACCESS Examples and Information for Working With EXCEL Workbook Files? Lots of Access - Excel automation code there.

[diegomarino]

no, i totally missed it and it looks like really good

[diegomarino]

Hi, sorry to reply to my old topic but i have the last issue.
i made this code (maybe it needs some polish but it works)

Public Function ExportExcel()
   
   Dim db As DAO.Database, RstUpdt As DAO.Recordset, RstApp As DAO.Recordset, RstDel As DAO.Recordset, Tbf As DAO.TableDef, FldNum As Integer
   Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
   Dim blnEXCEL As Boolean, FileName As String, WorksheetName As String, CellRange As Range
   Dim Fld As DAO.Field, FldName As String, FldValue As Variant
   Dim Idx As Index, i As Integer, ColNum As Integer
   Dim TbEx As String, ColName As String, RowNum As Long
     
   Set db = CurrentDb
   
   
   ' stabilisce un'applicazione oggetto excel
   blnEXCEL = False
   On Error Resume Next
   Set xlx = GetObject(, "Excel.application")
   If Err.Number <> 0 Then
      Set xlx = CreateObject("Excel.application")
      blnEXCEL = True
   End If
   Err.Clear
   On Error GoTo 0
   xlx.Visible = False ' se setto vero mi apre il file excel in questione
   
   For Each Tbf In db.TableDefs
      On Error Resume Next
      TbEx = Tbf.Name & "Ex"
      
      If ifTableExists(TbEx) Then 'controlla se esiste la tabella collegata (per evitare tabelle che non ho esportato)
         Debug.Print (TbEx)
         Set Fld = Tbf.Fields
         
         For Each Idx In Tbf.Indexes ' cerca tra i campi della tabella quali sono i primary key
            
            If Idx.Primary Then
               Fld = Idx.Fields
               FldName = Replace(Idx.Fields, "+", "") 'ritorna il nome del campo primary key
               Debug.Print (FldName)
               Set RstUpdt = db.OpenRecordset("SELECT [" & Tbf.Name & "].* FROM [" & TbEx & "] INNER JOIN [" & Tbf.Name & "] ON [" & TbEx & "].[" & FldName & "] = [" & Tbf.Name & "].[" & FldName & "] " & vbCrLf & _
                                              "WHERE ((([" & Tbf.Name & "].[Data/ora modifica])>[" & TbEx & "].[data/ora modifica]));")


               
               
               Set RstApp = db.OpenRecordset("SELECT [" & Tbf.Name & "].[" & FldName & "] FROM [" & Tbf.Name & "] LEFT JOIN [" & TbEx & "] ON [" & Tbf.Name & "].[" & FldName & "] = [" & TbEx & "].[" & FldName & "] WHERE ((([" & TbEx & "].[" & FldName & "]) Is Null));")
               Set RstDel = db.OpenRecordset("SELECT [" & TbEx & "].[" & FldName & "], [" & TbEx & "].[Data/ora creazione], [" & TbEx & "].[Data/ora modifica] " & vbCrLf & _
                                             "FROM [" & TbEx & "] LEFT JOIN [" & Tbf.Name & "] ON [" & TbEx & "].[" & FldName & "] = [" & Tbf.Name & "].[" & FldName & "] " & vbCrLf & _
                                             "WHERE ((([" & Tbf.Name & "].[" & FldName & "]) Is Null));")
               
               
               RstUpdt.MoveLast '!!!
               RstApp.MoveLast
               RstDel.MoveLast
               Debug.Print (RstUpdt.RecordCount)
               Debug.Print (RstApp.RecordCount)
               Debug.Print (RstDel.RecordCount)
            
               If RstUpdt.RecordCount > 0 Or RstApp.RecordCount > 0 Or RstDel.RecordCount > 0 Then 'se ci sono record allora faccio l'update del file
                  FileName = "C:\Users\diego\OneDrive - Indipendente\Lavoro\Informatica\Gestionale\Dati\" & Tbf.Name & ".xlsx"
                  Set xlw = xlx.Workbooks.Open(FileName)
                  Debug.Print (FileName)
                  WorksheetName = Tbf.Name
                  Set xls = xlw.Worksheets(WorksheetName)
                  FldNum = CurrentDb.TableDefs(Tbf.Name).Fields.Count ' numero di campi nella tabella
                  Debug.Print (CurrentDb.TableDefs(Tbf.Name).Fields.Count)
                  Debug.Print (WorksheetName)
                  Debug.Print (FldNum)
                  For i = 1 To FldNum ' trovo la colonna corrispettiva in excel del campo access: cerco dalla prima colonna della prima riga fino al numero di campi della tabella access se il nome campo access è uguale all'intestazione colonna di excel
                     Debug.Print (xls.Cells(1, i).Value)
                        If xls.Cells(1, i).Value = FldName Then
                           ColName = (Left(Right(xls.Cells(1, i).Address, Len(xls.Cells(1, i).Address) - 1), InStr(xls.Cells(1, i).Address, "$")))
                           Debug.Print (ColName)
                           Exit For
                        End If
                  Next i
                  
                  If RstUpdt.RecordCount > 0 Then
                     RstUpdt.MoveFirst
                     Do While Not RstUpdt.EOF Or RstUpdt.BOF
                        Debug.Print (RstUpdt.Fields(FldName))
                        FldValue = RstUpdt.Fields(FldName)
                        Set xlc = xls.Range(ColName & ":" & ColName).Find(FldValue) 'Rstupdt.Fields(FldName))
                        Debug.Print (xlc)
                        RowNum = xlc.Row
                        Debug.Print (RowNum)
                        For ColNum = 0 To FldNum - 1
                           xlc.Offset(0, ColNum).Value = RstUpdt.Fields(ColNum).Value
                           Debug.Print (xlc.Offset(0, ColNum).Value)
                           Debug.Print (RstUpdt.Fields(ColNum).Value)
                        Next ColNum
                        RstUpdt.MoveNext
                     Loop
                  End If
                  
               End If
               
            End If
         Next Idx
      End If
   Next Tbf
   
   Set xlc = Nothing
   Set xls = Nothing
   xlw.Close True   ' close the EXCEL file and save the new data
   Set xlw = Nothing
   If blnEXCEL = True Then xlx.Quit ' se esiste l'applicazione excel la chiude
   Set xlx = Nothing
   
   
   
End Function

the problem is that when the code stops it saves the excel file in a new file, but i want to update the existing file. I cannot figure out how to get this

[diegomarino]

sorry it was an error

EDIT i meant it was an error this post, unfortunely i didn't find the problem