PDA

View Full Version : Not enough memory + Excel 2016


Romulo Avila
01-30-2017, 05:01 AM
Good Morning,


I need a help, I have some VBA macros that worked perfectly in EXCEL 2013, I started to use EXCEL 2016 and from there I can not execute the macros, it returns with the following error:


"There is not enough memory to complete this action"


This macro generates PDF files and saves to a certain folder.


The machine I use has 4 Gb of memory and 32 bits


What can it be ?
mancubus
01-30-2017, 10:04 AM
?
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other/microsoft-excel-cannot-open-or-save-any-more/961a153e-6c68-4b22-b896-94601a28d7e2
Paul_Hossler
01-30-2017, 11:04 AM
Good Morning,


I need a help, I have some VBA macros that worked perfectly in EXCEL 2013, I started to use EXCEL 2016 and from there I can not execute the macros, it returns with the following error:


"There is not enough memory to complete this action"


This macro generates PDF files and saves to a certain folder.


The machine I use has 4 Gb of memory and 32 bits


What can it be ?


What's the macro look like?
Romulo Avila
01-30-2017, 12:03 PM
Paul,

Good afternoon,

As requested below


Sub Gera_PDF_Representantes()


Dim Rep As String
Dim Cont As Integer
Dim i As Integer
Dim Data As String
Dim StatusBar As String
Dim fim As Integer
Dim ini As Integer

ini = 1
fim = 0

Cont = 16
i = 2


Data = Plan1.Cells(2, 4).Value


Workbooks.Open Filename:="\\srvarq\chb\ROTINAS\INFORMAR\VENDAS\" & Data & "\Premio_Rep.xlsx", WriteResPassword:="cpdolded", UpdateLinks:=3
'Windows("Premio_Rep.xlsx").Activate

Rep = Worksheets("financeiro").Cells(Cont, 1).Value

Do While Not Rep = ""
fim = fim + 1
Rep = Worksheets("financeiro").Cells(Cont, 1).Value
Cont = Cont + 1
Loop

Cont = 16
Rep = Worksheets("financeiro").Cells(Cont, 1).Value

Sheets("ranking").Select
Range("A2:E250").Select
Selection.ClearContents


'Rep = "='Vol Fisico'!R[1]C[12]"
Rep = Worksheets("financeiro").Cells(Cont, 1).Value


Do While Not Rep = ""
Application.StatusBar = sStatusProcesso & Format(ini / fim, "0.0%") & " Concluído"


'Sheets("Inativos").Select

'ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("Cod_Rep").CurrentPage _
' = Rep

Sheets("Dashboard").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = Rep
'Sheets("Dashboard").Select
'Range("C1").Select = Rep
'ActiveCell.FormulaR1C1 = Rep


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\email\" & Rep & " Relatorio" & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
ActiveWorkbook.Saved = True

Sheets("Geral").Select
ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("Rep").CurrentPage = Rep

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\email\" & Rep & " Vendas" & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
ActiveWorkbook.Saved = True


Worksheets("Ranking").Cells(i, 1).Value = Worksheets("Dashboard").Cells(1, 3).Value
Worksheets("Ranking").Cells(i, 2).Value = Worksheets("Prêmio").Cells(11, 7).Value
Worksheets("Ranking").Cells(i, 3).Value = Worksheets("Prêmio").Cells(31, 4).Value
Worksheets("Ranking").Cells(i, 4).Value = Worksheets("Vol Fisico").Cells(8, 46).Value
Worksheets("Ranking").Cells(i, 5).Value = Worksheets("Vol Fisico").Cells(8, 37).Value

i = i + 1
ini = ini + 1
Cont = Cont + 1
Rep = Worksheets("Financeiro").Cells(Cont, 1).Value

Loop


Application.StatusBar = False
ActiveWorkbook.Save
ActiveWindow.Close


End Sub
Romulo Avila
02-01-2017, 06:34 AM
Paul_Hossler,


Good Morning,


By the VBA code did you get to see something? Is there anything I can change?


Thank you.


Hugs
snb
02-01-2017, 07:24 AM
Remove all 'selects' and 'activates'
Remove all default arguments.
Romulo Avila
02-01-2017, 12:42 PM
snb,

Good afternoon,


I am a beginner in macros, in case I remove them according to their orientation


"Remove all 'selects' and 'activates'"
"Remove all default arguments."


Would it simply remove or have to replace the commands?


In this case "ActiveSheet.ExportAsFixedFormat Type: = xlTypePDF, Filename: = _" would look like this "Sheet.ExportAsFixedFormat Type: = xlTypePDF, Filename: = _"?


Thank you very much for your support.
snb
02-01-2017, 01:26 PM
This suffices:


ActiveSheet.ExportAsFixedFormat 0, "G:\OF\example.pdf"
Romulo Avila
02-02-2017, 12:49 PM
Snb,

Good afternoon,

I made the changes as directed "see macro below" and even then continued to give the memory error, is there anything else I can try?


Thanks again for the support.


Sub Gera_PDF_Representantes()


Dim Rep As String
Dim Cont As Integer
Dim i As Integer
Dim Data As String
Dim StatusBar As String
Dim fim As Integer
Dim ini As Integer


ini = 1
fim = 0


Cont = 16
i = 2


Data = Plan1.Cells(2, 4).Value


Workbooks.Open Filename:="\\srvarq\chb\ROTINAS\INFORMAR\VENDAS\JANEIRO2017" & "\Premio_Rep.xlsx", WriteResPassword:="cpdolded", UpdateLinks:=3
'Windows("Premio_Rep.xlsx").Activate

Rep = Worksheets("financeiro").Cells(Cont, 1).Value

Do While Not Rep = ""
fim = fim + 1
Rep = Worksheets("financeiro").Cells(Cont, 1).Value
Cont = Cont + 1
Loop

Cont = 16
Rep = Worksheets("financeiro").Cells(Cont, 1).Value

Sheets("ranking").Select
Range("A2:E250").Select
Selection.ClearContents


Rep = Worksheets("financeiro").Cells(Cont, 1).Value


Do While Not Rep = ""
Application.StatusBar = sStatusProcesso & Format(ini / fim, "0.0%") & " Concluído"



Sheets("Dashboard").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = Rep
'Sheets("Dashboard").Select
'Range("C1").Select = Rep
'ActiveCell.FormulaR1C1 = Rep


ActiveSheet.ExportAsFixedFormat 0, "C:\email\" & Rep & " Relatorio" & ".pdf"

ActiveWorkbook.Saved = True

Sheets("Geral").Select
ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("Rep").CurrentPage = Rep

ActiveSheet.ExportAsFixedFormat 0, "C:\email\" & Rep & " Vendas" & ".pdf"

ActiveWorkbook.Saved = True

Worksheets("Ranking").Cells(i, 1).Value = Worksheets("Dashboard").Cells(1, 3).Value
Worksheets("Ranking").Cells(i, 2).Value = Worksheets("Prêmio").Cells(11, 7).Value
Worksheets("Ranking").Cells(i, 3).Value = Worksheets("Prêmio").Cells(31, 4).Value
Worksheets("Ranking").Cells(i, 4).Value = Worksheets("Vol Fisico").Cells(8, 46).Value
Worksheets("Ranking").Cells(i, 5).Value = Worksheets("Vol Fisico").Cells(8, 37).Value

i = i + 1
ini = ini + 1
Cont = Cont + 1
Rep = Worksheets("Financeiro").Cells(Cont, 1).Value

Loop


Application.StatusBar = False
ActiveWorkbook.Save
ActiveWindow.Close


End Sub
snb
02-02-2017, 03:22 PM
Avoid any do ... loop loops.



Sheets("ranking").Range("A2:E250").ClearContents

And start with the mastering of VBA fundamentals first.
Romulo Avila
02-03-2017, 04:04 AM
SNB,

Good Morning,


What strikes me the most is that in excel 2007 and excel 2013 it works normally. Only in Excel 2016 does this message of lack of memory appear.
Romulo Avila
02-24-2017, 12:11 PM
Good afternoon everyone,
I made the changes as indicated by the SNB and even then the error "There is not enough memory to complete this action" still happens, if possible I would like an aid to analyze this macro and what can be improved.


Thank you in advance for the help.


Sub Gera_PDF_Representantes()

Dim Rep As String
Dim Cont As Integer
Dim i As Integer
Dim Data As String
Dim StatusBar As String
Dim fim As Integer
Dim ini As Integer


ini = 1
fim = 0


Cont = 16
i = 2


Data = Plan1.Cells(2, 4).Value


Workbooks.Open Filename:="\\srvarq\chb\ROTINAS\INFORMAR\VENDAS\JANEIRO2017" & "\Premio_Rep.xlsx", WriteResPassword:="cpdolded", UpdateLinks:=3
'Windows("Premio_Rep.xlsx").Activate

Rep = Worksheets("financeiro").Cells(Cont, 1).Value

Do While Not Rep = ""
fim = fim + 1
Rep = Worksheets("financeiro").Cells(Cont, 1).Value
Cont = Cont + 1
Loop

Cont = 16
Rep = Worksheets("financeiro").Cells(Cont, 1).Value

Sheets("ranking").Select
Range("A2:E250").Select
Selection.ClearContents


Rep = Worksheets("financeiro").Cells(Cont, 1).Value


Do While Not Rep = ""
Application.StatusBar = sStatusProcesso & Format(ini / fim, "0.0%") & " Concluído"



Sheets("Dashboard").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = Rep
'Sheets("Dashboard").Select
'Range("C1").Select = Rep
'ActiveCell.FormulaR1C1 = Rep


ActiveSheet.ExportAsFixedFormat 0, "C:\email\" & Rep & " Relatorio" & ".pdf"

ActiveWorkbook.Saved = True

Sheets("Geral").Select
ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("Rep").CurrentPage = Rep

ActiveSheet.ExportAsFixedFormat 0, "C:\email\" & Rep & " Vendas" & ".pdf"

ActiveWorkbook.Saved = True

Worksheets("Ranking").Cells(i, 1).Value = Worksheets("Dashboard").Cells(1, 3).Value
Worksheets("Ranking").Cells(i, 2).Value = Worksheets("Prêmio").Cells(11, 7).Value
Worksheets("Ranking").Cells(i, 3).Value = Worksheets("Prêmio").Cells(31, 4).Value
Worksheets("Ranking").Cells(i, 4).Value = Worksheets("Vol Fisico").Cells(8, 46).Value
Worksheets("Ranking").Cells(i, 5).Value = Worksheets("Vol Fisico").Cells(8, 37).Value

i = i + 1
ini = ini + 1
Cont = Cont + 1
Rep = Worksheets("Financeiro").Cells(Cont, 1).Value

Loop


Application.StatusBar = False
ActiveWorkbook.Save
ActiveWindow.Close


End Sub