Trasferire Tabelle e/o query Access in Excel

di
Valecomme
il
23 risposte
V
Valecomme
Pochi posts
Iscritto da
ott, 2022
Messaggi:
12

Trasferire Tabelle e/o query Access in Excel

Buongiorno,

Sto creando un DB in Access (sono alle prime armi del codice VBA).

Ho bisogno di trasferire in EXCEL i dati che si trovano nella Query “QPreventivo”..

Gli ostacoli sono:

- Questo automatismo deve avvenire tramite pulsante inserito nella maschera;

- I dati riportati in Excel devo essere inseriti secondo un layout stabilito (mentre ora se esporto in excel mi trascrive tutto su una riga ).

Per ora ho creato sistemato questo codice ma non esegue quello che mi serve:

 Private Sub Comando196_Click()
DoCmd.OutputTo acOutputForm, "Preventivo", acFormatXLS, [Application].[CurrentProject].[Path] & _
"\" & "Elenco_Esportati.xls", True
End Sub

Ho visto una richiesta simile pubblicata nel 2012 dove suggeriva un  metodo pubblicato qui:

http://forum.masterdrive.it/access-79/access-esportare-dati-in-excel-xml-50060/

Ma ora questo link non funziona più.

Grazie mille

23 Risposte

  • @Alex
    @Alex
    Super Famoso
    Iscritto da
    feb, 2012
    Messaggi:
    8747

    Re: Trasferire Tabelle e/o query Access in Excel

    Cosa intendi per LayOut prestabilito….?

    Innanzitutto non si usa il metodo OutPutTo, ma TransferSpreadSheet… che consente anche di definire il RANGE in cui trasferire il blocco dati…

    Leggi questo:

    https://learn.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet

    Ci sono poi altri metodi di esportazione CopyFromRecordset, ma benchè più flessibili sono più complessi…

  • V
    Valecomme
    Pochi posts
    Iscritto da
    ott, 2022
    Messaggi:
    12

    Re: Trasferire Tabelle e/o query Access in Excel

    Grazie @Alex per la risposta.

    Per LayOut intendo indicare nel VBA la posizione esatta dove inserire un determinato record.

    Per farti un banale esempio l' importo totale del preventivo in “C2”.

  • @Alex
    @Alex
    Super Famoso
    Iscritto da
    feb, 2012
    Messaggi:
    8747

    Re: Trasferire Tabelle e/o query Access in Excel

    Allora ti ho dato la risposta che fa quello che chiedi.

  • V
    Valecomme
    Pochi posts
    Iscritto da
    ott, 2022
    Messaggi:
    12

    Re: Trasferire Tabelle e/o query Access in Excel

    Ciao @Alex,

    ho inserito questo codice

    Private Sub Apri_FileExce_Click()
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Preventivo", "C:\Users\PC\Desktop\Preventivo.xlsx", True
    End Sub

    Funziona correttamente nell'esportazione.

    L'unica cosa è che non ho capito come indicare la posizione (es: “importo totale preventivo” in “C2”)

    Potresti scrivermi un esempio?

    Scusami ma come ti dicevo prima sono alle prime armi con il VBA

    Grazie 

  • @Alex
    @Alex
    Super Famoso
    Iscritto da
    feb, 2012
    Messaggi:
    8747

    Re: Trasferire Tabelle e/o query Access in Excel

    Lecci il Link che ti ho postato trovi l'esempio… VUOLE UN RANGE e non la cella di partenza.

  • V
    Valecomme
    Pochi posts
    Iscritto da
    ott, 2022
    Messaggi:
    12

    Re: Trasferire Tabelle e/o query Access in Excel

    Ciao,

    ho provato a rivedere l'articolo ma non mi sembra che faccia quello che chiedo e forse mi sono spiegata male…

    Ho una serie di record da inserire tipo:

    Importo materiale ----?C8

    VA Materiale --? D8

    Importo trasporti--? C10

    VA Trasporti--D10

    Imponibile --? D11

    Iva --?E11

    Totale fattura --? F11

    Ho provato ad inserire un range ma non li inserisce nella posizione che desidero io.

    Grazie

  • @Alex
    @Alex
    Super Famoso
    Iscritto da
    feb, 2012
    Messaggi:
    8747

    Re: Trasferire Tabelle e/o query Access in Excel

    Mostra il codice… evidentemente sbagli qualcosa…

  • V
    Valecomme
    Pochi posts
    Iscritto da
    ott, 2022
    Messaggi:
    12

    Re: Trasferire Tabelle e/o query Access in Excel

    Ciao Alex,

    ho provato ad inserire il range come indicato nell'articolo

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QPreventivo", "C:\Users\PC\Desktop\Costi.xlsx", True, "A14:Q24"

    Però non funziona. Ma il range non si inserisce solo nell'import?

    Grazie

  • @Alex
    @Alex
    Super Famoso
    Iscritto da
    feb, 2012
    Messaggi:
    8747

    Re: Trasferire Tabelle e/o query Access in Excel

    Hai ragione solo per l'Import, quindi per esportare devi usare il Metodo CopyFromRecordset in questo modo:

    Public Function ExportXLData(xlFilePath As String, xlSheetName As String, xlCell As String, SourceToExport As String) As Boolean
    On Error GoTo Err_Handler
    
    Dim wb  As Object
    Dim ws  As Object
    Dim XL  As Object
    Dim rs  As DAO.Recordset

    Set rs = DBEngine(0)(0).OpenRecordset(SourceToExport)

    Set XL = CreateObject("Excel.Application")
    XL.displayalerts = False
    Set wb = XL.Workbooks.Open(xlFilePath)
    Set ws = wb.Sheets(xlSheetName)

    ws.Range(xlCell).CopyFromRecordset rs

    rs.Close
    Set rs = Nothing
    Set ws = Nothing
    wb.Save
    wb.Close
    XL.Quit
    Set wb = Nothing
    Set XL = Nothing

    ExportXLData = True
Exit_Here:
    Exit Function
Err_Handler:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Here
End Function

    Ovviamente se il File di Excel non esiste devi Crearlo, sostituendo al metodo OPEN dell'Oggetto WorkBook, il metodo ADD, quindi dovrai inserire il SAVEAS, passando il Nome del File, invece del SAVE… 

  • V
    Valecomme
    Pochi posts
    Iscritto da
    ott, 2022
    Messaggi:
    12

    Re: Trasferire Tabelle e/o query Access in Excel

    Ciao,

    proverò ad inserirlo…anche se non ho ben capito dove devo inserire la posizione….

    E' lo stesso che consigliavi qui?

    Grazie

  • @Alex
    @Alex
    Super Famoso
    Iscritto da
    feb, 2012
    Messaggi:
    8747

    Re: Trasferire Tabelle e/o query Access in Excel

    Non ricordo quell'articolo… ma probabile.

  • V
    Valecomme
    Pochi posts
    Iscritto da
    ott, 2022
    Messaggi:
    12

    Re: Trasferire Tabelle e/o query Access in Excel

    Ciao,

    Ho trovato/modificato questo codice che mi permette di esportare come voglio io….

    Ora dovrei esportare nello stesso modo altre 3 tabelle. E' possibile unirle in un'unica funzione in mdod che schiacciando il pulsante mi esporti tutto automaticamente?

    Grazie

    Private Sub prova_Click()
    Dim xlApp As Object, xlSheet As Object
    Dim RS As Recordset, ExcelTargetRange As Object, FieldNum As Integer
    Dim i As Long, j As Long, k As Long, TableName As String
    Dim ExcelFileName As String, ExcelSheetName As String
    Dim ExcelStartCell As String
    '
    ' Definizioni
    ' ------------------------------
    TableName = "P_Ingegneria"
    TableName = "P_Materiali"
    FieldNum = 22
    ExcelFileName = "C:\Users\PC\Desktop\Costi.xlsx"
    ExcelSheetName = "Ingegneria"
    ExcelStartCell = "A14"
    ' ------------------------------
    Set db = CurrentDb
    Set RS = db.OpenRecordset(TableName, dbOpenDynaset)

    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(ExcelFileName)
    Set ExcelTargetRange = xlSheet.Sheets(ExcelSheetName).Range(ExcelStartCell)
    'xlApp.Visible = True

    Do
    k = k + 1
    If IsEmpty(ExcelTargetRange(k, 1)) Then
    Exit Do
    End If
    Loop

    Do Until RS.EOF
    For i = 1 To FieldNum
    ExcelTargetRange(k, i) = RS.Fields(i - 1)
    Next
    k = k + 1
    RS.MoveNext
    Loop

    xlSheet.Close SaveChanges:=True

    End Sub

  • @Alex
    @Alex
    Super Famoso
    Iscritto da
    feb, 2012
    Messaggi:
    8747

    Re: Trasferire Tabelle e/o query Access in Excel

    Quello che ti avevo proposto sopra, è almeno 10 volte più performante… vedi tu.

  • V
    Valecomme
    Pochi posts
    Iscritto da
    ott, 2022
    Messaggi:
    12

    Re: Trasferire Tabelle e/o query Access in Excel

    Non metto in dubbio che sia performante…ho provato prima del codice che ho pubblicato ma non essendo un'esperta (come giò scritto) e probabilmente non riesco ad associarlo al pulsante o sbaglio qualcos'altro….

    Te lo riporto

    Private Sub Comando247_Click()
    Public Function Comando247(xlFilePath As String, xlSheetName As String, xlCell As String, SourceToExport As String) As Boolean
       On Error GoTo Err_Handler
       Dim wb  As Object
       Dim ws  As Object
       Dim XL  As Object
       Dim rs  As DAO.Recordset

       Set rs = DBEngine(0)(0).OpenRecordset("P_Ingegneria")

       Set XL = CreateObject("Excel.Application")
       XL.displayalerts = False
       Set wb = XL.Workbooks.Open("C:\Users\PC\Desktop\Costiprova.xls")
       Set ws = wb.Sheets("INGEGNERIA")

       ws.Range("A14").CopyFromRecordset rs

       rs.Close
       Set rs = Nothing
       Set ws = Nothing
       wb.Save
       wb.Close
       XL.Quit
       Set wb = Nothing
       Set XL = Nothing

       ExportXLData = True
    Exit_Here:
       Exit Function
    Err_Handler:
       MsgBox Err.Number & " - " & Err.Description
       Resume Exit_Here
    End Function

    End Sub

    Per aggiungere altre tabelle da esportare nel caso devo aggiungere questo passaggio più volte?

       Set rs = DBEngine(0)(0).OpenRecordset("P_Ingegneria")

       Set XL = CreateObject("Excel.Application")
       XL.displayalerts = False
       Set wb = XL.Workbooks.Open("C:\Users\PC\Desktop\Costiprova.xls")
       Set ws = wb.Sheets("INGEGNERIA")

       ws.Range("A14").CopyFromRecordset rs

Devi accedere o registrarti per scrivere nel forum
23 risposte