Excel messaggio "L'immagine è troppo grande e verrà troncata"

Question

Buongiorno,

Excel 2013, ho un file che contiene 7 fogli con dati e tabelle pivot, compilato con dati SQL via macro VBA, due fogli sono nascosti.

quando chiudo Excel, sia salvando che non salvando ottengo il messaggio in oggetto, la clipbord è vuota.

Grazie per l'aiuto

Cordiali saluti Marzio

Answer 1

Ciao Mauro,

risolto, ho aggiunto al codice le due istruzioni seguenti:

    Application.CommandBars("Office Clipboard").Visible = True

    Application.CommandBars("Office Clipboard").Visible = False

Grazie

Ciao Marzio

Answer 2

Bene, ciao e alla prossima.

Answer 3

Grazie Mauro per la risposta,

l'istruzione ne codice c'è.

Ecco il codice

'Sub Macro1()

Sub Auto_Open()

    Range("A1").Select

    Application.ScreenUpdating = False

    UsPath = Environ("userprofile")

    Open UsPath + "\MKTSTD.Ini" For Input As #1

    If InStr(UCase(ActiveWorkbook.Path), "\FORM") Then End

    Do While Not EOF(1)

        Line Input #1, a$

        If InStr(a$, "COMPUTERNAME=") Then Cname = Mid(a$, InStr(a$, "=") + 1)

        If OkDb = 1 Then

            If Mid(a$, 1, 1) = "[" Then OkDb = 0

            If InStr(a$, "DSN=") Then DSN = Mid(a$, InStr(a$, "=") + 1)

            If InStr(a$, "DB=") Then Db = Mid(a$, InStr(a$, "=") + 1)

            If InStr(a$, "ISTANZA=") Then ISTANZA = Mid(a$, InStr(a$, "=") + 1)

        End If

        If a$ = "[Clienti]" Then OkDb = 1

    Loop

    Close #1

    Open UsPath + "\ConfigMKT.txt" For Input As #1

    Do While Not EOF(1)

        Ax = Ax + 1

        Line Input #1, a$

        If Ax = 1 Then User = a$

        If Ax = 2 Then TipoUser = a$

    Loop

    Close #1

    If TipoUser = "VE" Then Qry$ = " and OrdRi.Venditore = '" & User & "'"

    If TipoUser = "CA" Then Qry$ = " and OrdRi.CapoArea = '" & User & "'"

    If TipoUser = "" Then Qry$ = ""

    Sheets("Dati").Select

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _

        "ODBC;DSN=" & DSN & ";UID=;PWD=;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=" & Cname & ";DATABASE=" & Db & "" _

        , Destination:=Range("Dati!$A$1")).QueryTable

        .CommandText = Array( _

        "SELECT Ordri.DataOrdine, Ordri.Customer,Ordri.DescrArticolo, Ordri.ImportoNetto, Ordri.NrOrdine, Ordri.Prezzo, Ordri.Quantita, Ordri.Sconto, Ordri.TotaleListino, Ordri.CodiceArticolo, Ordri.GruppoMacchina, Ordri.Mac" _

        , _

        "china, Ordri.CNC, Ordri.GruppoCNC, Ordri.FamigliaProdotto, Ordri.CodiceMacchina, Ordri.DescrizioneMacchina, Ordri.CapoArea, Ordri.Venditore, Ordri.LineaProdotto, Ordri.Anno, Ordri.Mese, Ordri.MeseDesc" _

        , _

        "rittivo, Ordri.ImpNettoNetto, Ordri.ScontoCliente, Ordri.ScontoOrdine, Ordri.AnnoMese, Ordri.Costo, Ordri.CostoUnitario, Ordri.Margine, Ordri.MargineNetto, Ordri.PercMargine, Ordri.PercMargineNetto, O" _

        , _

        "rdri.PercProvvCapoArea, Ordri.PercProvvVend, Ordri.PercProvvigioni, Ordri.PrezzoNetto, Ordri.Provincia, Ordri.ProvvCapoArea, Ordri.ProvvVenditore, Ordri.Regione, Ordri.Stato, Ordri.TipoRiga, Ordri.Tot" _

        , _

        "aleProvvigioni, Ordri.CategCliente, Ordri.DesCategoriaCliente, Ordri.TipoCliente, Ordri.CategoriaLDP" & Chr(13) & "" & Chr(10) & "FROM " & Db & ".dbo.Ordri Ordri" & Chr(13) & "" & Chr(10) & "WHERE (Ordri.Anno>'2000')" & Chr(13) & "" & Chr(10) & "ORDER BY Ordri.Anno, Ordri.Mese, Ordri." _

        , "DataOrdine")

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .BackgroundQuery = True

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .PreserveColumnInfo = True

'        .ListObject.DisplayName = "Tabella_Query_da_SWMissler"

        .Refresh BackgroundQuery:=False

    End With

    Columns("A:A").Select

    Selection.NumberFormat = "m/d/yyyy"

    Columns("D:D").Select

    Selection.NumberFormat = "#,##0.00"

    Columns("F:F").Select

    Selection.NumberFormat = "#,##0.00"

    Columns("I:I").Select

    Selection.NumberFormat = "#,##0.00"

    Columns("X:X").Select

    Selection.NumberFormat = "#,##0.00"

    ActiveWindow.SmallScroll ToRight:=4

    Columns("AB:AE").Select

    Selection.NumberFormat = "#,##0.00"

    ActiveWindow.SmallScroll ToRight:=8

    Columns("AK:AK").Select

    Selection.NumberFormat = "#,##0.00"

    Columns("AM:AN").Select

    Selection.NumberFormat = "#,##0.00"

    ActiveWindow.SmallScroll ToRight:=6

    Columns("AR:AR").Select

    Selection.NumberFormat = "#,##0.00"

'    Range("Tabella_Query_da_SWMissler[[#Headers],[DataOrdine]]").Select

    Range("A1").Select

  Sheets("Dati").Select

    Columns("J:J").Select

    Selection.Copy

    Sheets("SubDati").Select

    Columns("A:A").Select

    ActiveSheet.Paste

    Sheets("Dati").Select

    Columns("C:C").Select

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("SubDati").Select

    Columns("B:B").Select

    ActiveSheet.Paste

    Sheets("Dati").Select

    Application.CutCopyMode = False

    Columns("G:G").Select

    Selection.Copy

    Sheets("SubDati").Select

    Columns("C:C").Select

    ActiveSheet.Paste

    Application.CutCopyMode = False

    Selection.NumberFormat = "0"

    Sheets("Dati").Select

     Columns("U:U").Select

    Selection.Copy

    Sheets("SubDati").Select

    Columns("D:D").Select

    ActiveSheet.Paste

    Sheets("Dati").Select

    Application.CutCopyMode = False

    Columns("V:W").Select

    Selection.Copy

    Sheets("SubDati").Select

    Columns("E:F").Select

    ActiveSheet.Paste

     Sheets("Dati").Select

    Application.CutCopyMode = False

    ActiveWindow.SmallScroll ToRight:=3

    Columns("X:X").Select

    Selection.Copy

    Sheets("SubDati").Select

    Columns("G:G").Select

    ActiveSheet.Paste

    ActiveWindow.SmallScroll Down:=-3

    Sheets("Dati").Select

    Application.CutCopyMode = False

   Columns("O:O").Select

    Selection.Copy

    Sheets("SubDati").Select

    Columns("H:H").Select

    ActiveSheet.Paste

    Sheets("Dati").Select

    Application.CutCopyMode = False

    Columns("T:T").Select

    Selection.Copy

    Sheets("SubDati").Select

    Columns("I:I").Select

    ActiveSheet.Paste

    Columns("C:C").Select

    Application.CutCopyMode = False

    Selection.Copy

    Columns("J:J").Select

    ActiveSheet.Paste

    Application.CutCopyMode = False

    Range("J1").Select

    ActiveCell.FormulaR1C1 = "Quantita1"

    Columns("G:G").Select

    Application.CutCopyMode = False

    Selection.Copy

    Columns("K:K").Select

    ActiveSheet.Paste

    Application.CutCopyMode = False

    Range("K1").Select

    ActiveCell.FormulaR1C1 = "ImpNetto"

    Range("A1").Select

    Sheets("Dati").Select

    ActiveWindow.ScrollColumn = 1

    UR = Sheets("Dati").Range("A1").End(xlDown).Row

    Range("A2").Select

    Sheets("Quantita").Select

    Application.CutCopyMode = False

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

        "Dati!R1C1:R" & UR & "C26", Version:=xlPivotTableVersion12).CreatePivotTable _

        TableDestination:="Quantita!R1C1", TableName:="Tabella_pivot1", _

        DefaultVersion:=xlPivotTableVersion12

    Sheets("Quantita").Select

    Cells(1, 1).Select

    With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("CapoArea")

        .Orientation = xlPageField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("Venditore")

        .Orientation = xlPageField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("FamigliaProdotto")

        .Orientation = xlPageField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("LineaProdotto")

        .Orientation = xlPageField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("CodiceArticolo")

        .Orientation = xlRowField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("DescrArticolo")

        .Orientation = xlRowField

        .Position = 2

    End With

    With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("Anno")

        .Orientation = xlColumnField

        .Position = 1

    End With

    ActiveSheet.PivotTables("Tabella_pivot1").AddDataField ActiveSheet.PivotTables( _

        "Tabella_pivot1").PivotFields("Quantita"), "Somma di Quantita", xlSum

    ActiveSheet.PivotTables("Tabella_pivot1").RowAxisLayout xlOutlineRow

    Columns("A:A").ColumnWidth = 21

    Columns("B:B").ColumnWidth = 64.71

    ActiveWorkbook.ShowPivotTableFieldList = False

    ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("CodiceArticolo"). _

        ShowDetail = False

    Sheets("VariazioniQta").Select

    Range("A1").Select

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

        "SubDati!R1C1:R" & UR & "C10", Version:=xlPivotTableVersion12).CreatePivotTable _

        TableDestination:="VariazioniQta!R1C1", TableName:="Tabella_pivot2", _

        DefaultVersion:=xlPivotTableVersion12

    Sheets("VariazioniQta").Select

    Cells(1, 1).Select

    With ActiveSheet.PivotTables("Tabella_pivot2").PivotFields("FamigliaProdotto")

        .Orientation = xlPageField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("Tabella_pivot2").PivotFields("LineaProdotto")

        .Orientation = xlPageField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("Tabella_pivot2").PivotFields("CodiceArticolo")

        .Orientation = xlRowField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("Tabella_pivot2").PivotFields("DescrArticolo")

        .Orientation = xlRowField

        .Position = 2

    End With

    With ActiveSheet.PivotTables("Tabella_pivot2").PivotFields("Anno")

        .Orientation = xlColumnField

        .Position = 1

    End With

    ActiveSheet.PivotTables("Tabella_pivot2").AddDataField ActiveSheet.PivotTables( _

        "Tabella_pivot2").PivotFields("Quantita1"), "Somma di Quantita1", xlSum

    ActiveSheet.PivotTables("Tabella_pivot2").AddDataField ActiveSheet.PivotTables( _

        "Tabella_pivot2").PivotFields("Quantita"), "Somma di Quantita", xlSum

    Range("A11").Select

    ActiveSheet.PivotTables("Tabella_pivot2").RowAxisLayout xlOutlineRow

    Columns("A:A").ColumnWidth = 13

    Columns("B:B").ColumnWidth = 51

    Range("C6").Select

    ActiveSheet.PivotTables("Tabella_pivot2").DataPivotField.PivotItems( _

        "Somma di Quantita1").Caption = "Q.tà"

    Range("D6").Select

    ActiveSheet.PivotTables("Tabella_pivot2").DataPivotField.PivotItems( _

        "Somma di Quantita").Caption = "Var.%"

    Range("D9").Select

    With ActiveSheet.PivotTables("Tabella_pivot2").PivotFields("Var.%")

        .Calculation = xlPercentDifferenceFrom

        .BaseField = "Anno"

        .BaseItem = "(precedente)"

        .NumberFormat = "0,00%"

    End With

    ActiveWorkbook.ShowPivotTableFieldList = False

    Range("B20").Select

    With ActiveSheet.PivotTables("Tabella_pivot2")

        .DisplayErrorString = True

        .ErrorString = """"""

    End With

    Range("B27").Select

    ActiveSheet.PivotTables("Tabella_pivot2").ErrorString = ""

    Range("A7").Select

    ActiveSheet.PivotTables("Tabella_pivot2").PivotFields("CodiceArticolo"). _

        ShowDetail = False

    Columns("E:AF").Select

    Selection.NumberFormat = "#,##0"

    Columns("F:F").Select

    Selection.NumberFormat = "0%"

    Columns("H:H").Select

    Selection.NumberFormat = "0%"

    Columns("J:J").Select

    Selection.NumberFormat = "0%"

    Columns("L:L").Select

    Selection.NumberFormat = "0%"

    Columns("N:N").Select

    Selection.NumberFormat = "0%"

     Columns("P:P").Select

    Selection.NumberFormat = "0%"

    Columns("R:R").Select

    Selection.NumberFormat = "0%"

    Columns("T:T").Select

    Selection.NumberFormat = "0%"

    ActiveWindow.SmallScroll ToRight:=9

    Columns("V:V").Select

    Selection.NumberFormat = "0%"

    Columns("X:X").Select

    Selection.NumberFormat = "0%"

    Columns("Z:Z").Select

    Selection.NumberFormat = "0%"

    Columns("AB:AB").Select

    Selection.NumberFormat = "0%"

    Columns("AD:AD").Select

    Selection.NumberFormat = "0%"

    ActiveWindow.ScrollColumn = 1

    ActiveWorkbook.ShowPivotTableFieldList = False

    Range("A1").Select

        ActiveWorkbook.Worksheets("VariazioniQta").PivotTables("Tabella_pivot2").PivotCache. _

        CreatePivotTable TableDestination:="VariazioneImporto!R1C1", TableName:= _

        "Tabella_pivot3", DefaultVersion:=xlPivotTableVersion12

    Sheets("VariazioneImporto").Select

    Cells(1, 1).Select

'    ActiveWorkbook.ShowPivotTableFieldList = True

    With ActiveSheet.PivotTables("Tabella_pivot3").PivotFields("FamigliaProdotto")

        .Orientation = xlPageField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("Tabella_pivot3").PivotFields("LineaProdotto")

        .Orientation = xlPageField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("Tabella_pivot3").PivotFields("Quantita")

        .Orientation = xlColumnField

        .Position = 1

    End With

    ActiveSheet.PivotTables("Tabella_pivot3").AddDataField ActiveSheet.PivotTables( _

        "Tabella_pivot3").PivotFields("ImpNettoNetto"), "Conteggio di ImpNettoNetto", _

        xlCount

    With ActiveSheet.PivotTables("Tabella_pivot3").PivotFields( _

        "Conteggio di ImpNettoNetto")

        .Caption = "Somma di ImpNettoNetto"

        .Function = xlSum

    End With

    ActiveSheet.PivotTables("Tabella_pivot3").AddDataField ActiveSheet.PivotTables( _

        "Tabella_pivot3").PivotFields("Quantita"), "Somma di Quantita", xlSum

    With ActiveSheet.PivotTables("Tabella_pivot3").PivotFields("CodiceArticolo")

        .Orientation = xlRowField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("Tabella_pivot3").PivotFields("Anno")

        .Orientation = xlPageField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("Tabella_pivot3").PivotFields("DescrArticolo")

        .Orientation = xlRowField

        .Position = 2

    End With

    Range("A8").Select

    ActiveSheet.PivotTables("Tabella_pivot3").RowAxisLayout xlOutlineRow

    Columns("A:A").ColumnWidth = 16.29

    Columns("B:B").ColumnWidth = 51.57

    Range("A6").Select

    ActiveSheet.PivotTables("Tabella_pivot3").PivotFields("CodiceArticolo"). _

        ShowDetail = False

    ActiveWindow.SmallScroll Down:=-3

    Columns("D:D").Select

    Selection.NumberFormat = "#,##0"

    ActiveWindow.SmallScroll Down:=-3

    Range("C13").Select

    With ActiveSheet.PivotTables("Tabella_pivot3").PivotFields("Anno")

        .Orientation = xlColumnField

        .Position = 2

    End With

    Range("C6").Select

    ActiveSheet.PivotTables("Tabella_pivot3").DataPivotField.PivotItems( _

        "Somma di Quantita").Caption = "Q.tà"

    Range("Q6").Select

    ActiveSheet.PivotTables("Tabella_pivot3").DataPivotField.PivotItems( _

        "Somma di ImpNettoNetto").Caption = "Importo"

    Columns("C:AF").Select

    Selection.ColumnWidth = 10.14

        Range("B15").Select

'    ActiveWorkbook.ShowPivotTableFieldList = True

    ActiveSheet.PivotTables("Tabella_pivot3").PivotFields("Q.tà").Orientation = _

        xlHidden

    ActiveWorkbook.ShowPivotTableFieldList = False

        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

        "SubDati!R1C1:R" & UR & "C11", Version:=xlPivotTableVersion12).CreatePivotTable _

        TableDestination:="ValoreVenduto!R1C1", TableName:="Tabella_pivot5", _

        DefaultVersion:=xlPivotTableVersion12

    Sheets("ValoreVenduto").Select

    Cells(1, 1).Select

    With ActiveSheet.PivotTables("Tabella_pivot5").PivotFields("CodiceArticolo")

        .Orientation = xlRowField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("Tabella_pivot5").PivotFields("DescrArticolo")

        .Orientation = xlRowField

        .Position = 2

    End With

    With ActiveSheet.PivotTables("Tabella_pivot5").PivotFields("Anno")

        .Orientation = xlColumnField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("Tabella_pivot5").PivotFields("FamigliaProdotto")

        .Orientation = xlPageField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("Tabella_pivot5").PivotFields("LineaProdotto")

        .Orientation = xlPageField

        .Position = 1

    End With

    ActiveSheet.PivotTables("Tabella_pivot5").AddDataField ActiveSheet.PivotTables( _

        "Tabella_pivot5").PivotFields("ImpNetto"), "Conteggio di ImpNetto", xlCount

    ActiveSheet.PivotTables("Tabella_pivot5").AddDataField ActiveSheet.PivotTables( _

        "Tabella_pivot5").PivotFields("ImpNettoNetto"), "Conteggio di ImpNettoNetto", _

        xlCount

    With ActiveSheet.PivotTables("Tabella_pivot5").PivotFields( _

        "Conteggio di ImpNetto")

        .Caption = "Somma di ImpNetto"

        .Function = xlSum

    End With

    With ActiveSheet.PivotTables("Tabella_pivot5").PivotFields( _

        "Conteggio di ImpNettoNetto")

        .Caption = "Somma di ImpNettoNetto"

        .Function = xlSum

    End With

    ActiveWorkbook.ShowPivotTableFieldList = False

    Range("A12").Select

    ActiveSheet.PivotTables("Tabella_pivot5").DisplayErrorString = True

    ActiveSheet.PivotTables("Tabella_pivot5").RowAxisLayout xlOutlineRow

    Columns("A:A").ColumnWidth = 15.14

    Columns("B:B").ColumnWidth = 50.29

    Range("C6").Select

    ActiveSheet.PivotTables("Tabella_pivot5").DataPivotField.PivotItems( _

        "Somma di ImpNetto").Caption = "Importo"

    Range("D6").Select

    ActiveSheet.PivotTables("Tabella_pivot5").DataPivotField.PivotItems( _

        "Somma di ImpNettoNetto").Caption = "Var.%"

    Columns("C:AF").Select

    Selection.ColumnWidth = 11.29

     Selection.NumberFormat = "#,##0"

    Range("D9").Select

    With ActiveSheet.PivotTables("Tabella_pivot5").PivotFields("Var.%")

        .Calculation = xlPercentDifferenceFrom

        .BaseField = "Anno"

        .BaseItem = "(precedente)"

        .NumberFormat = "0,00%"

    End With

    Range("A7").Select

    ActiveSheet.PivotTables("Tabella_pivot5").PivotFields("CodiceArticolo"). _

        ShowDetail = False

    ActiveWorkbook.ShowPivotTableFieldList = False

        ActiveWorkbook.Worksheets("ValoreVenduto").PivotTables("Tabella_pivot5").PivotCache. _

        CreatePivotTable TableDestination:="TotaliAnni!R1C1", TableName:= _

        "Tabella_pivot6", DefaultVersion:=xlPivotTableVersion12

    Sheets("TotaliAnni").Select

    Cells(1, 1).Select

 '   ActiveWorkbook.ShowPivotTableFieldList = True

    With ActiveSheet.PivotTables("Tabella_pivot6").PivotFields("Anno")

        .Orientation = xlColumnField

        .Position = 1

    End With

    ActiveSheet.PivotTables("Tabella_pivot6").AddDataField ActiveSheet.PivotTables( _

        "Tabella_pivot6").PivotFields("ImpNettoNetto"), "Conteggio di ImpNettoNetto", _

        xlCount

    With ActiveSheet.PivotTables("Tabella_pivot6").PivotFields( _

        "Conteggio di ImpNettoNetto")

        .Caption = "Somma di ImpNettoNetto"

        .Function = xlSum

    End With

    Range("B1").Select

    ActiveSheet.PivotTables("Tabella_pivot6").CompactLayoutColumnHeader = "Anno"

    Range("A3").Select

    ActiveSheet.PivotTables("Tabella_pivot6").DataPivotField.PivotItems( _

        "Somma di ImpNettoNetto").Caption = "Importo"

    Range("B3:O3").Select

    Columns("B:Z").Select

    Selection.NumberFormat = "#,##0"

    Selection.Columns.AutoFit

    Range("A3").Select

    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select

    Sheets("Dati").Select

    ActiveWindow.SelectedSheets.Visible = False

    Sheets("SubDati").Select

    ActiveWindow.SelectedSheets.Visible = False

    Application.CutCopyMode = False

    Application.DisplayAlerts = False

    ApPath = Application.Path

    ActiveWorkbook.SaveAs Filename:= _

        UsPath & "\ExOrdArt.xlsx", _

        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

     Kill ApPath + "\xlstart\ExOrdArt.xlsm"

    Application.DisplayAlerts = True

End Sub

Ciao Marzio

Answer 4

Senza vedere il codice, difficile rispondere.

Prima di uscire, prova:

Application.CutCopyMode = False