Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

COM Excel Pivot Table



  • Please log in to reply
9 replies to this topic
hot hot 85
  • Members
  • 72 posts
  • Last active: Sep 29 2015 05:40 AM
  • Joined: 27 Jun 2011

Hello AHK

 

I use MACRO from excel and I try to translater to AHK code,but I Stock here

 

macro excel

 

/*
    With ActiveSheet.PivotTables("Tabla dinámica2").PivotFields("linea")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    
    With ActiveSheet.PivotTables("Tabla dinámica2").PivotFields("naed")
        .Orientation = xlRowField
        .Position = 2
    End With
    
    
    With ActiveSheet.PivotTables("Tabla dinámica2").PivotFields("FALLA")
        .Orientation = xlRowField
        .Position = 3
    End With
    
    
    ActiveSheet.PivotTables("Tabla dinámica2").AddDataField ActiveSheet.PivotTables _
        ("Tabla dinámica2").PivotFields("tipo 2"), "Cuenta de tipo 2", xlCount
        
        
    With ActiveSheet.PivotTables("Tabla dinámica2")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
    
    
    With ActiveSheet.PivotTables("Tabla dinámica2").PivotFields("linea")
        .PivotItems("(blank)").Visible = False
    End With
    
    
End Sub


*/

ahk code

 

 
; Excel Constants
xlDatabase := 1
xlPivotTableVersion12 := 3
xlRowField := 1
xlCount := -4112


FileRead, consolidado, % A_ScriptDir "\consolidado.txt"


oExcel := ComObjCreate("Excel.Application")
oExcel.WorkBooks.Add
oExcel.Visible := true


; Create a temporary array to hold each line.
TempArray :={}
Loop, Parse, consolidado, `n,`r
    TempArray.Insert(StrSplit(A_LoopField, ","))


nRows := TempArray.MaxIndex()
nColumns := TempArray.1.MaxIndex()


; Create a safearray with the the correct number of rows and columns
SafeArray := ComObjArray(12, nRows, nColumns)


; populate the safearray from the temporary array
for i, row in TempArray
    for k, val in row
        SafeArray[i-1, k-1] := val
TempArray := "" ; TempArray can now be cleared
   
; place the safearray into the range
MyRange := oExcel.Range(oExcel.Cells(2,1), oExcel.Cells(nRows+1,nColumns)) ; diagnostico.ini is not required. Used this instead.
MyRange.Value := SafeArray


oExcel.Range("a1").Value := "Fecha"
oExcel.Range("b1").Value := "hora"
oExcel.Range("c1").Value := "serial"
oExcel.Range("d1").Value := "tipo"
oExcel.Range("e1").Value := "naed"
oExcel.Range("F1").Value := "FALLA"
oExcel.Range("g1").Value := "defecto"
oExcel.Range("h1").Value := "linea"
oExcel.Range("i1").Value := "FALLA1"
oExcel.Range("j1").Value := "texto"
oExcel.Range("k1").Value := "componente"
oExcel.Range("l1").Value := "comentario"
oExcel.Range("m1").Value := "turno"
oExcel.Range("n1").Value := "numero"
oExcel.Range("o1").Value := "tipo 2"




oExcel.Sheets.Add
oExcel.ActiveWorkbook.PivotCaches.CREATE(xlDatabase, "HOJA1!R1C6:R1048576C6", xlPivotTableVersion12)
    .CreatePivotTable("HOJA2!R3C1", "PivotTable1", , xlPivotTableVersion12)
oExcel.Sheets("HOJA2").Select
oExcel.Cells(3, 1).Select


This := oExcel.ActiveSheet.PivotTables
{
;this.PivotTables("PivotTable1")
this.PivotFields("linea")
this.Orientation := xlRowField
this.Position := 1
}
/*
oExcel.ActiveWorkbook.ShowPivotTableFieldList := True
oExcel.ActiveSheet.PivotTables("PivotTable1").PivotFields("linea").Orientation := xlRowField
oExcel.ActiveSheet.PivotTables("PivotTable1").PivotFields("linea").Position := 1


oExcel.ActiveSheet.PivotTables("PivotTable1").PivotFields("naed").Orientation := xlRowField
oExcel.ActiveSheet.PivotTables("PivotTable1").PivotFields("naed").Position := 2
*/




oExcel.ActiveSheet.PivotTables("PivotTable1").AddDataField(oExcel
    .ActiveSheet.PivotTables("PivotTable1").PivotFields("FALLA"), "Count of falla", xlCount)
oExcel.ActiveWorkbook.ShowPivotTableFieldList := False
return

I think the problem is transalete with and end with

 

thanks in advance

 


Every day exist something new to learn

flyingDman
  • Spam Officer
  • 2186 posts
  • Last active: Nov 07 2015 08:15 AM
  • Joined: 27 Feb 2009
✓  Best Answer

This works here:

#singleinstance, force
SetBatchLines, -1

fileread,oVar,consolidado.txt
clipboard := StrReplace(oVar, ",", a_tab)

Xl := ComObjCreate("Excel.Application") 				            ;create a handle to a new excel application
Xl.Visible := True 							            ;by default excel sheets are invisible
Xl.Workbooks.Add 								    ;add a new workbook w/ standard 3 (default) sheets or # selected in xl options
Xl.Range("A2").PasteSpecial(-4104) 					            ;'-4163' is the constant for values only; XlPasteType = -4104 = all; 

for x,y in ["Fecha","Hora","Serial","Tipo","Naed","Falla","defecto","Linea","Falla1","Texto","Componente","Comentario","Turno","Numero","Tipo 2"]
	xl.cells(1,x).value := y

pvt := xl.ActiveWorkbook.PivotCaches.Create(xlDatabase:=1, "sheet1!R1C1:R205C15",xlPivotTableVersion12:=3).CreatePivotTable("sheet1!R1C17", "PivotTable1", ,xlPivotTableVersion12:=3)
pvt.PivotFields("Linea").Orientation := 1
pvt.PivotFields("Linea").Position := 1
pvt.PivotFields("Linea").PivotItems("blank").Visible := False
pvt.PivotFields("naed").Orientation := 1
pvt.PivotFields("naed").Position := 2
pvt.PivotFields("Falla").Orientation := 1
pvt.PivotFields("Falla").Position := 3

pvt.AddDataField(pvt.PivotFields("Tipo 2"), "Cuenta de Tipo 2", -4112)		     ;xlCount := -4112 
;xl.ActiveWorkbook.ShowPivotTableFieldList := False
xl.ActiveWindow.Zoom := 85

Marine Corps Gen. Joseph Dunford told senators at his Joint Chiefs of Staff confirmation hearing : “If you want to talk about a nation that could pose an existential threat to the United States, I'd have to point to Russia. And if you look at their behavior, it's nothing short of alarming.”


hot hot 85
  • Members
  • 72 posts
  • Last active: Sep 29 2015 05:40 AM
  • Joined: 27 Jun 2011

Hi Flyingdman

 

I run the script and show this error

 

---------------------------
EXCEL.ahk
---------------------------
Error:  0x80070057 - The parameter is incorrect.


Source: (null)
Description: (null)
HelpFile: (null)
HelpContext: 0


Specifically: CreatePivotTable


Line#
005: clipboard := StrReplace(oVar, ",", a_tab)
007: Xl := ComObjCreate("Excel.Application")
008: Xl.Visible := True  
009: Xl.Workbooks.Add  
010: Xl.Range("A2").PasteSpecial(-4104)  
012: For x,y in ["Fecha","Hora","Serial","Tipo","Naed","Falla","defecto","Linea","Falla1","Texto","Componente","Comentario","Turno","Numero","Tipo 2"]
013: xl.cells(1,x).value := y  
---> 015: pvt := xl.ActiveWorkbook.PivotCaches.Create(xlDatabase:=1, "sheet1!R1C1:R205C15",xlPivotTableVersion12:=3).CreatePivotTable("sheet2!R1C17", "PivotTable1", ,xlPivotTableVersion12:=3)
016: pvt.PivotFields("Linea").Orientation := 1  
017: pvt.PivotFields("Linea").Position := 1  
018: pvt.PivotFields("Linea").PivotItems("blank").Visible := False  
019: pvt.PivotFields("naed").Orientation := 1  
020: pvt.PivotFields("naed").Position := 2  
021: pvt.PivotFields("Falla").Orientation := 1  
022: pvt.PivotFields("Falla").Position := 3  


Continue running the script?
---------------------------
Yes   No   
---------------------------

 I should include the file consolidado.txt , I use Excel 2013

 

is this

 

 

13/October/2014 ,00:20,pte por serial,ICT,79631,2.10 Set read,LEVANTADO,L5,PROGRAMACION,SMT,U001      I51L00497S00      IC-ASIC-SMD uCONTROLLER-AT90PWM81-QFN32*,u1 levantado,TURNO3,116783,produccion
13/October/2014 ,00:22,pte por serial,Modular funcional 1,79631,3.18 I out,INVERTIDO,L5,FUNCIONAL,MANUAL,T001      I25L02285S00      MAG-TR-RAD 380uH-5-68:27:7-E25/13/7-N87*D014      I48L01501S00      SEM-DIODE-SMD 200V-0.25A-50ns-SOD80*Q003      I48L01404S00      SEM-NPN-SMD 45V-500mA-SOT23*,T1 invertido daña a Q3 y D14,TURNO3,116783,produccion
13/October/2014 ,00:24,pte por serial,wall-E1,79631,2.10 Set read,INSUFICIENCIA,L5,FUNCIONAL,WAVE SOLDER,D016      I48L01783S00      SEM-S-DIODE-SMD 100V-3A-DO214AA*,d16 insuficiencia,TURNO3,116783,produccion
13/October/2014 ,00:28,pte por serial,Modular funcional 1,79631,3.18 I out,CORTO DE SOLDADURA,L5,FUNCIONAL,WAVE SOLDER,T001      I25L02285S00      MAG-TR-RAD 380uH-5-68:27:7-E25/13/7-N87*,T1 corto soldadura,TURNO3,116783,produccion
13/October/2014 ,01:11,pte por serial,Modular funcional 2,79631,4.20 I out,DAÑO ELECTRICO,L5,FUNCIONAL,FUNCIONAL,U002      I51L00558S00      IC-ASIC SN1102050-SOIC-14*,u2 daño electrico,TURNO3,116783,produccion
13/October/2014 ,03:29,pte por serial,Modular funcional 2,79631,4.20 I out,QUEBRADO,L5,FUNCIONAL,FUNCIONAL,C019      I21L01436S00      CAP-CER-SMD-220nF-10-25V-0603-X7R*,c19 quebrado en desfalteo,TURNO3,116783,produccion
13/October/2014 ,03:33,pte por serial,ICT,79631,2.10 Set read,LEVANTADO,L5,PROGRAMACION,SMT,U001      I51L00497S00      IC-ASIC-SMD uCONTROLLER-AT90PWM81-QFN32*,u1 levantado,TURNO3,116783,produccion
13/October/2014 ,03:37,pte por serial,Modular funcional 2,79631,3.18 I out,DAÑO ELECTRICO,L5,FUNCIONAL,FUNCIONAL,D014      I48L01501S00      SEM-DIODE-SMD 200V-0.25A-50ns-SOD80*,d14 abierto,TURNO3,116783,produccion
13/October/2014 ,03:43,pte por serial,Modular funcional 2,79631,3.18 I out,DAÑO ELECTRICO,L5,FUNCIONAL,FUNCIONAL,D014      I48L01501S00      SEM-DIODE-SMD 200V-0.25A-50ns-SOD80*,d14  valor bajo,TURNO3,116783,produccion
13/October/2014 ,03:44,pte por serial,Modular funcional 2,79631,3.18 I out,DAÑO ELECTRICO,L5,FUNCIONAL,FUNCIONAL,U001      I51L00497S00      IC-ASIC-SMD uCONTROLLER-AT90PWM81-QFN32*,u1 dañado,TURNO3,116783,produccion
13/October/2014 ,03:45,pte por serial,small funcional,79631,3.18 I out,LEVANTADO,L5,FUNCIONAL,MANUAL,L005      I25L02035S00      MAG-TRANS-RAD 13mH +30/-20-E16/8/5-N30*L005      I25L02035S00      MAG-TRANS-RAD 13mH +30/-20-E16/8/5-N30*,L5 levantada,TURNO3,116783,produccion
13/October/2014 ,03:55,pte por serial,Modular funcional 2,79631,3.18 I out,DAÑO ELECTRICO,L5,FUNCIONAL,FUNCIONAL,D020      I48L01256S00      SEM-DIODE-SMD  75V-0.15A-4ns-QUADRO MELF*,d20 valor bajo,TURNO3,116783,produccion
13/October/2014 ,03:57,pte por serial,Modular funcional 2,79631,3.18 I out,DAÑO MECANICO,L5,FUNCIONAL,MANUAL,L005      I25L02035S00      MAG-TRANS-RAD 13mH +30/-20-E16/8/5-N30*,L5 enbobinado abierto,TURNO3,116783,produccion
13/October/2014 ,03:58,pte por serial,Modular funcional 2,79631,3.18 I out,DAÑO MECANICO,L5,FUNCIONAL,MANUAL,L002      I25L02246S00      MAG-CHOKE-RAD 680uH-10-DRUM-LS5*,L2 abierta,TURNO3,116783,produccion
13/October/2014 ,04:05,pte por serial,wall-E1,79631,Programacion,UNIDAD DESPROGRAMADA,L5,FUNCIONAL,ESTACION DE PROGRAMACION,,unidad desprogramada,TURNO3,116783,produccion
13/October/2014 ,04:05,pte por serial,wall-E1,79631,Programacion,FALLA FALSA POR EQUIPO,L5,FUNCIONAL,ESTACION DE PROGRAMACION,,unidad desprogramada,TURNO3,116783,produccion
13/October/2014 ,04:05,pte por serial,wall-E1,79631,Programacion,FALLA FALSA POR EQUIPO,L5,FUNCIONAL,ESTACION DE PROGRAMACION,,unidad desprogramada,TURNO3,116783,produccion
13/October/2014 ,04:06,pte por serial,Modular funcional 2,79631,Programacion,FALLA FALSA POR EQUIPO,L5,FUNCIONAL,ESTACION DE PROGRAMACION,,unidad desprogramada,TURNO3,116783,produccion
13/October/2014 ,04:08,pte por serial,Modular funcional 2,79631,3.18 I out,INVERTIDO,L5,FUNCIONAL,MANUAL,T001      I25L02285S00      MAG-TR-RAD 380uH-5-68:27:7-E25/13/7-N87*D014      I48L01501S00      SEM-DIODE-SMD 200V-0.25A-50ns-SOD80*Q003      I48L01404S00      SEM-NPN-SMD 45V-500mA-SOT23*,T1 invertido daña a Q3 y D14,TURNO3,116783,produccion
13/October/2014 ,04:09,pte por serial,Modular funcional 2,79631,6.5 Power Factor,INSUFICIENCIA,L5,FUNCIONAL,WAVE SOLDER,D008      I48L01367S00      SEM-DIODE-SMD 1000V-1A -2us-DO214AC*,D8 con insuficiencia,TURNO3,116783,produccion
13/October/2014 ,04:15,pte por serial,Modular funcional 2,79631,3.18 I out,DAÑO ELECTRICO,L5,FUNCIONAL,FUNCIONAL,D014      I48L01501S00      SEM-DIODE-SMD 200V-0.25A-50ns-SOD80*,D14 abierto,TURNO3,116783,produccion
13/October/2014 ,04:17,pte por serial,Modular funcional 2,79631,3.18 I out,DAÑO ELECTRICO,L5,FUNCIONAL,FUNCIONAL,D014      I48L01501S00      SEM-DIODE-SMD 200V-0.25A-50ns-SOD80*,d14 abierto,TURNO3,116783,produccion
13/October/2014 ,04:28,pte por serial,Modular funcional 1,79631,THD,DAÑO ELECTRICO,L5,FUNCIONAL,FUNCIONAL,U003      I51L00515S00      IC-A-SMD PFC CONTROLLER-L6562AD-SO8*,u3 dañado,TURNO3,116783,produccion
13/October/2014 ,04:29,pte por serial,wall-E1,79631,3.18 I out,CORTO DE SOLDADURA,L5,FUNCIONAL,WAVE SOLDER,SCR1      I48L01700S00      SEM-TRIAC-SMD 600V-4A-0.5W-DPAK*,scr1 corto de soldadura,TURNO3,116783,produccion
13/October/2014 ,04:34,pte por serial,Modular funcional 1,79631,3.18 I out,DAÑO ELECTRICO,L5,FUNCIONAL,FUNCIONAL,U002      I51L00558S00      IC-ASIC SN1102050-SOIC-14*,U2 dañado,TURNO3,116783,produccion
13/October/2014 ,04:38,pte por serial,Modular funcional 2,79631,3.18 I out,DAÑO MECANICO,L5,FUNCIONAL,MANUAL,L003      I25L02250S01      MAG-EMI-RAD-24mH-30-120:120-E16/8/5-N30*L005      I25L02035S00      MAG-TRANS-RAD 13mH +30/-20-E16/8/5-N30*L003      I25L02250S01      MAG-EMI-RAD-24mH-30-120:120-E16/8/5-N30*L005      I25L02035S00      MAG-TRANS-RAD 13mH +30/-20-E16/8/5-N30*,L3 levantada L5 enbobinado abierto,TURNO3,116783,produccion
13/October/2014 ,04:40,pte por serial,Modular funcional 2,79631,Programacion,FALLA FALSA POR EQUIPO,L5,FUNCIONAL,ESTACION DE PROGRAMACION,,unidad sin programar,TURNO3,116783,produccion
13/October/2014 ,04:41,pte por serial,Modular funcional 2,79631,3.18 I out,DAÑO MECANICO,L5,FUNCIONAL,MANUAL,L005      I25L02035S00      MAG-TRANS-RAD 13mH +30/-20-E16/8/5-N30*,L5 bobina abierta,TURNO3,116783,produccion
13/October/2014 ,04:48,pte por serial,Modular funcional 1,79631,6.5 Power Factor,LEVANTADO,L5,FUNCIONAL,SMT,C025      I21L01446S00      CAP-CER-SMD 1U-10-25-0603-X7R*,c25 levantado,TURNO3,116783,produccion
13/October/2014 ,04:49,pte por serial,Modular funcional 2,79631,3.18 I out,DAÑO MECANICO,L5,FUNCIONAL,PREFORME,C013      I23L01059S00      CAP-EL-RAD 680U-20-105C-80-LS7.5*,c13 quebrado terminal,TURNO3,116783,produccion
13/October/2014 ,04:51,pte por serial,Modular funcional 2,79631,3.18 I out,CORTO DE SOLDADURA,L5,FUNCIONAL,WAVE SOLDER,F001      I65L00119S01      EM-FUSE-AX  3A-350V-FASTACTING-5.5x15.5*D008      I48L01367S00      SEM-DIODE-SMD 1000V-1A -2us-DO214AC*,corto de soldadura D8 daña fusible,TURNO3,116783,produccion
13/October/2014 ,05:10,pte por serial,Modular funcional 2,79631,Programacion,FALLA FALSA POR EQUIPO,L5,FUNCIONAL,ESTACION DE PROGRAMACION,,unidad sin programar,TURNO3,116783,produccion
13/October/2014 ,05:13,pte por serial,Modular funcional 2,79631,3.18 I out,INSUFICIENCIA,L5,FUNCIONAL,WAVE SOLDER,L004      I25L02244S01      MAG-CHOKE-RAD 220uH-5-E20/10/6-N87*,L4 insuficiencia,TURNO3,116783,produccion
13/October/2014 ,05:17,pte por serial,Modular funcional 1,79631,3.18 I out,LEVANTADO,L5,FINAL,MANUAL,L005      I25L02035S00      MAG-TRANS-RAD 13mH +30/-20-E16/8/5-N30*,L5 levantada,TURNO3,116783,produccion
13/October/2014 ,05:17,pte por serial,Modular funcional 2,79631,3.18 I out,LEVANTADO,L5,FINAL,MANUAL,L005      I25L02035S00      MAG-TRANS-RAD 13mH +30/-20-E16/8/5-N30*,L5 levantada,TURNO3,116783,produccion
13/October/2014 ,05:17,pte por serial,large funcional,79631,3.18 I out,DAÑO MECANICO,L5,FINAL,MANUAL,L005      I25L02035S00      MAG-TRANS-RAD 13mH +30/-20-E16/8/5-N30*,L5 enbobinado abierto ,TURNO3,116783,produccion
13/October/2014 ,05:19,pte por serial,Modular funcional 2,79631,3.18 I out,CORTO DE SOLDADURA,L5,FUNCIONAL,MANUAL,Q001      I48L01786S00      SEM-N-MOS-RAD 900V-6.9A-0R8-TO220AB*Q001      I48L01786S00      SEM-N-MOS-RAD 900V-6.9A-0R8-TO220AB*,Q1 toca al T1 daña F1,TURNO3,116783,produccion
13/October/2014 ,05:30,pte por serial,Modular funcional 2,79631,3.18 I out,CORTO DE SOLDADURA,L5,FUNCIONAL,MANUAL,F001      I65L00119S01      EM-FUSE-AX  3A-350V-FASTACTING-5.5x15.5*Q001      I48L01786S00      SEM-N-MOS-RAD 900V-6.9A-0R8-TO220AB*,Q1 toca al T1 daña F1,TURNO3,116783,produccion
13/October/2014 ,05:31,pte por serial,Modular funcional 2,79631,3.18 I out,CORTO DE SOLDADURA,L5,FUNCIONAL,MANUAL,F001      I65L00119S01      EM-FUSE-AX  3A-350V-FASTACTING-5.5x15.5*Q001      I48L01786S00      SEM-N-MOS-RAD 900V-6.9A-0R8-TO220AB*,Q1 toca al T1 daña F1,TURNO3,116783,produccion
13/October/2014 ,05:31,pte por serial,Modular funcional 2,79631,3.18 I out,CORTO DE SOLDADURA,L5,FUNCIONAL,WAVE SOLDER,L005      I25L02035S00      MAG-TRANS-RAD 13mH +30/-20-E16/8/5-N30*,L5 corto de soldadura,TURNO3,116783,produccion
13/October/2014 ,05:32,pte por serial,Modular funcional 2,79631,2.10 Set read,DAÑO ELECTRICO,L5,FUNCIONAL,FUNCIONAL,U001      I51L00497S00      IC-ASIC-SMD uCONTROLLER-AT90PWM81-QFN32*,u1 en corto,TURNO3,116783,produccion
13/October/2014 ,05:33,pte por serial,Modular funcional 1,79631,3.18 I out,FALTANTE,L5,FUNCIONAL,SMT,D009      I48L01577S00      SEM-DIODE-Z-SMD 20V-2%-5mA-0.5W-SOD80*,D9 faltante,TURNO3,116783,produccion
13/October/2014 ,05:34,pte por serial,large funcional,79631,2.10 Set read,LEVANTADO,L5,FUNCIONAL,SMT,D019      I48L01794S00      SEM-S-D-DIODE-SMD 30V-200mA-5ns-SOT323*,D19 levantado,TURNO3,116783,produccion
13/October/2014 ,07:49,pte por serial,Modular funcional 1,49889,[email protected],INSUFICIENCIA,L5,FUNCIONAL,WAVE SOLDER,U003      I51L00477S00      IC-A-SMD  OP-AMPLIFIER-SOT23*U003      I51L00477S00      IC-A-SMD  OP-AMPLIFIER-SOT23*,.,TURNO1,118895,produccion
13/October/2014 ,07:50,pte por serial,Modular funcional 1,49889,Chknocal,CORTO DE SOLDADURA,L5,FUNCIONAL,WAVE SOLDER,R009      I06L01325S18      RES SMD 15.0K OHM 1% 0805 125mW TC100*,R10,TURNO1,118895,produccion
13/October/2014 ,08:00,pte por serial,wall-E1,79631,6.5 Power Factor,INVERTIDO,L5,FUNCIONAL,MANUAL,C005      I23L01034S01      CAP-EL-RAD 33U-20-105C-35-LS2.0*C005      I23L01034S01      CAP-EL-RAD 33U-20-105C-35-LS2.0*,se coloco mal,TURNO1,118733,produccion
13/October/2014 ,08:02,pte por serial,final 1,49889,Ponom @ 100%,UNIDAD DESCALIBRADA,L5,FINAL,FUNCIONAL,AP01      I84L01447D00      PCB-D-OT DIM-FR4-ROHS*,.,TURNO1,118895,produccion
13/October/2014 ,08:04,pte por serial,final 1,49889,Ponom @ 100%,UNIDAD DESCALIBRADA,L5,FINAL,FUNCIONAL,AP01      I84L01447D00      PCB-D-OT DIM-FR4-ROHS*,.,TURNO1,118895,produccion
13/October/2014 ,08:04,pte por serial,final 1,49889,Ponom @ 100%,UNIDAD DESCALIBRADA,L5,FINAL,FUNCIONAL,AP01      I84L01447D00      PCB-D-OT DIM-FR4-ROHS*,.,TURNO1,118895,produccion

Thanks in advance from Mexico


Every day exist something new to learn

flyingDman
  • Spam Officer
  • 2186 posts
  • Last active: Nov 07 2015 08:15 AM
  • Joined: 27 Feb 2009

I ran this again with no problems here (except for the line containing "blank" which I commented out). Could you try again using hoja1 instead of sheet1 (or hoja2)? 

I am using Excel 2007, and do not get the error on this or other spreadsheets / pivot tables. Googling the error code reveals that this error is not uncommon with Excel 2013 but in a cursory review of the responses, I could not find a solution. Wonder if anyone else can duplicate the error.


Marine Corps Gen. Joseph Dunford told senators at his Joint Chiefs of Staff confirmation hearing : “If you want to talk about a nation that could pose an existential threat to the United States, I'd have to point to Russia. And if you look at their behavior, it's nothing short of alarming.”


hot hot 85
  • Members
  • 72 posts
  • Last active: Sep 29 2015 05:40 AM
  • Joined: 27 Jun 2011

I find the error I Think is from MS , The Excel is in Spanish , I Replace Sheet instead  of Hoja.

 

But do you know how to fix the problem no matter what is the language of Excel?

 

this line show and error but I skip and the script run

 

pvt.PivotFields("Linea").PivotItems("blank").Visible := False

 

 

I Try to Add this lines but the second show and error

 

pvt.InGridDropZones := True
;pvt.RowAxisLayout xlTabularRow

 

 

thanks in advance


Every day exist something new to learn

flyingDman
  • Spam Officer
  • 2186 posts
  • Last active: Nov 07 2015 08:15 AM
  • Joined: 27 Feb 2009

Try this:

rng := xl.Sheets(1).UsedRange.address
trgt := xl.Sheets(1).range("q1")
pvt := xl.ActiveWorkbook.PivotCaches.Create(xlDatabase:=1, rng, xlPivotTableVersion12:=3).CreatePivotTable(trgt, "PivotTable1", ,xlPivotTableVersion12:=3)


Marine Corps Gen. Joseph Dunford told senators at his Joint Chiefs of Staff confirmation hearing : “If you want to talk about a nation that could pose an existential threat to the United States, I'd have to point to Russia. And if you look at their behavior, it's nothing short of alarming.”


hot hot 85
  • Members
  • 72 posts
  • Last active: Sep 29 2015 05:40 AM
  • Joined: 27 Jun 2011

the final code

 

#singleinstance, force
SetBatchLines, -1


fileread,oVar,consolidado.txt
clipboard := StrReplace(oVar, ",", a_tab)


Xl := ComObjCreate("Excel.Application")              ;create a handle to a new excel application
Xl.Visible := True              ;by default excel sheets are invisible
Xl.Workbooks.Add      ;add a new workbook w/ standard 3 (default) sheets or # selected in xl options
Xl.Range("A2").PasteSpecial(-4104)              ;'-4163' is the constant for values only; XlPasteType = -4104 = all; 


for x,y in ["Fecha","Hora","Serial","Tipo","Naed","Falla","defecto","Linea","Falla1","Texto","Componente","Comentario","Turno","Numero","Tipo 2"]
xl.cells(1,x).value := y


rng := xl.Sheets(1).UsedRange.address
trgt := xl.Sheets(1).range("q1")
pvt := xl.ActiveWorkbook.PivotCaches.Create(xlDatabase:=1, rng, xlPivotTableVersion12:=3).CreatePivotTable(trgt, "PivotTable1", ,xlPivotTableVersion12:=3)
pvt.PivotFields("Linea").Orientation := 1
pvt.PivotFields("Linea").Position := 1
;pvt.PivotFields("Linea").PivotItems("blank").Visible := False
pvt.PivotFields("naed").Orientation := 1
pvt.PivotFields("naed").Position := 2
pvt.PivotFields("Falla").Orientation := 1
pvt.PivotFields("Falla").Position := 3






pvt.InGridDropZones := True
pvt.RowAxisLayout xlTabularRow


pvt.AddDataField(pvt.PivotFields("Tipo 2"), "Cuenta de Tipo 2", -4112)      ;xlCount := -4112 
;xl.ActiveWorkbook.ShowPivotTableFieldList := False
xl.ActiveWindow.Zoom := 85

this is he error for pvt.PivotFields("Linea").PivotItems("blank").Visible := False

 

---------------------------
EXCEL.ahk
---------------------------
Error:  0x800A03EC - 
Source: Microsoft Excel
Description: No se puede obtener la propiedad PivotItems de la clase PivotField.
HelpFile: xlmain11.chm
HelpContext: 0


Specifically: PivotItems


Line#
012: For x,y in ["Fecha","Hora","Serial","Tipo","Naed","Falla","defecto","Linea","Falla1","Texto","Componente","Comentario","Turno","Numero","Tipo 2"]
013: xl.cells(1,x).value := y  
015: rng := xl.Sheets(1).UsedRange.address
016: trgt := xl.Sheets(1).range("q1")
017: pvt := xl.ActiveWorkbook.PivotCaches.Create(xlDatabase:=1, rng, xlPivotTableVersion12:=3).CreatePivotTable(trgt, "PivotTable1", ,xlPivotTableVersion12:=3)
018: pvt.PivotFields("Linea").Orientation := 1  
019: pvt.PivotFields("Linea").Position := 1  
---> 020: pvt.PivotFields("Linea").PivotItems("blank").Visible := False  
021: pvt.PivotFields("naed").Orientation := 1  
022: pvt.PivotFields("naed").Position := 2  
023: pvt.PivotFields("Falla").Orientation := 1  
024: pvt.PivotFields("Falla").Position := 3  
028: pvt.InGridDropZones := True  
031: pvt.AddDataField(pvt.PivotFields("Tipo 2"), "Cuenta de Tipo 2", -4112)  
033: xl.ActiveWindow.Zoom := 85  


Continue running the script?
---------------------------
Yes   No   
---------------------------

and this for the line pvt.RowAxisLayout xlTabularRow

 

>"C:\Program Files\AutoHotkey\AutoHotkey.exe" /ErrorStdOut "E:\DARIO\excel\EXCEL.ahk"    
E:\DARIO\excel\EXCEL.ahk (29) : ==> This line does not contain a recognized action.
     Specifically: pvt.RowAxisLayout xlTabularRow
>Exit code: 2    Time: 0.1523
 
 
Thanks for four Help :)

Every day exist something new to learn

flyingDman
  • Spam Officer
  • 2186 posts
  • Last active: Nov 07 2015 08:15 AM
  • Joined: 27 Feb 2009

If there is no "blank" in the column linea, it will throw an error.  If there are cases when there is a "blank" that you do want to use in the sampling and cases where there is no "blank", you'll need to filter these out beforehand or use a line ComObjError(false) before the offending line.

 

I have not tried RowAxisLayout but knw that xlTabularRow is a constant. Replace by its value, i.e. 1.


Marine Corps Gen. Joseph Dunford told senators at his Joint Chiefs of Staff confirmation hearing : “If you want to talk about a nation that could pose an existential threat to the United States, I'd have to point to Russia. And if you look at their behavior, it's nothing short of alarming.”


hot hot 85
  • Members
  • 72 posts
  • Last active: Sep 29 2015 05:40 AM
  • Joined: 27 Jun 2011

Hi FlyingDman

 

I try to change as consatan but I have the same error.

 

 

I add this code too but I do not know if exist some way easier

#singleinstance, force
SetBatchLines, -1


fileread,oVar,consolidado.txt
clipboard := StrReplace(oVar, ",", a_tab)


Xl := ComObjCreate("Excel.Application")              ;create a handle to a new excel application
Xl.Visible := True              ;by default excel sheets are invisible
Xl.Workbooks.Add      ;add a new workbook w/ standard 3 (default) sheets or # selected in xl options
Xl.Range("A2").PasteSpecial(-4104)              ;'-4163' is the constant for values only; XlPasteType = -4104 = all; 


for x,y in ["Fecha","Hora","Serial","Tipo","Naed","Falla","defecto","Linea","Falla1","Texto","Componente","Comentario","Turno","Numero","Tipo 2","componentes"]
xl.cells(1,x).value := y






;~ XL.Range("P1").Value := "componentes"


constatess=1
loop ,1000  ; 1000 times because I do not know where is the last row


{
++constatess
;~ datosssss.=
XL.Range("P"constatess).Value := "=LEFT(k" constatess ",4)"


}
;~ R205C15


rng := xl.Sheets(1).UsedRange.address
trgt := xl.Sheets(1).range("q1")
pvt := xl.ActiveWorkbook.PivotCaches.Create(xlDatabase:=1, rng, xlPivotTableVersion12:=3).CreatePivotTable(trgt, "PivotTable1", ,xlPivotTableVersion12:=3)


;pvt := xl.ActiveWorkbook.PivotCaches.Create(xlDatabase:=1, "sheet1!R1C1:R1048576C16",xlPivotTableVersion12:=3).CreatePivotTable("sheet1!R1C17", "PivotTable1", ,xlPivotTableVersion12:=3)
pvt.PivotFields("Numero").Orientation := 1
pvt.PivotFields("Numero").Position := 1
;pvt.PivotFields("Linea").PivotItems("blank").Visible := False
pvt.PivotFields("Turno").Orientation := 1
pvt.PivotFields("Turno").Position := 2


pvt.PivotFields("Falla1").Orientation := 1
pvt.PivotFields("Falla1").Position := 3


pvt.PivotFields("defecto").Orientation := 1
pvt.PivotFields("defecto").Position := 4


pvt.PivotFields("componentes").Orientation := 1
pvt.PivotFields("componentes").Position := 5


;~ pvt.InGridDropZones = true




pvt.AddDataField(pvt.PivotFields("Tipo 2"), "Cuenta de Tipo 2", -4112)      ;xlCount := -4112 
;~ Sxl.ActiveWorkbook.ShowPivotTableFieldList := False
xl.ActiveWindow.Zoom := 85

I would like to remove the empty lines but show and error

http://www.subirimag...05565139476.jpg

 

 

 

I would like the table like the picture

http://www.subirimag...55647584008.jpg

 

this is the code in VB for those things

 

/*
 
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Numero")
        .PivotItems("(blank)").Visible = False
    End With
 
    With ActiveSheet.PivotTables("PivotTable1")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
 
*/
 
 
Thanks a Lot 

Every day exist something new to learn

flyingDman
  • Spam Officer
  • 2186 posts
  • Last active: Nov 07 2015 08:15 AM
  • Joined: 27 Feb 2009

try this:

pvt.InGridDropZones := true
pvt.RowAxisLayout(1)               ;xlCompactRow = 0 ;  xlOutlineRow = 2

Regarding the "(blank)".  See here. As there is no "On Error Resume Next" equivalent (as far as I know), I suggest that you use ComObjError(false).
The following resulting script works fine here. I was not able to reproduce this error:

#singleinstance, force
SetBatchLines, -1

fileread,oVar,consolidado.txt
clipboard := StrReplace(oVar, ",", a_tab)

xl := ComObjCreate("Excel.Application") 				            ;create a handle to a new excel application
xl.Visible := True 							            ;by default excel sheets are invisible
xl.Workbooks.Add 								    ;add a new workbook w/ standard 3 (default) sheets or # selected in xl options
xl.ActiveWindow.Zoom := 85
xl.Range("A2").PasteSpecial(-4104) 					            ;'-4163' is the constant for values only; XlPasteType = -4104 = all; 
xl.range("a1").select

for x,y in ["Fecha","Hora","Serial","Tipo","Naed","Falla","Defecto","Linea","Falla1","Texto","Componentes","Comentario","Turno","Numero","Tipo 2"]
xl.cells(1,x).value := y

cnt := xl.Sheets(1).UsedRange.rows.count                ;count the used rows
loop, % cnt
xl.Range("P" a_index).Value := SubStr(XL.Range("k" a_index).Value,1,4)

rng := xl.Sheets(1).UsedRange.address
trgt := xl.Sheets(1).range("q1")

;pvt := xl.ActiveWorkbook.PivotCaches.Create(xlDatabase:=1, "sheet1!R1C1:R205C15",xlPivotTableVersion12:=3).CreatePivotTable("sheet1!R1C17", "PivotTable1", ,xlPivotTableVersion12:=3)
pvt := xl.ActiveWorkbook.PivotCaches.Create(xlDatabase:=1, rng, xlPivotTableVersion12:=3).CreatePivotTable(trgt, "PivotTable1", ,xlPivotTableVersion12:=3)

ComObjError(false) 					;disable COM error messages for some non available properties 
pvt.PivotFields("Numero").PivotItems("(blank)").Visible := False

pvt.PivotFields("Numero").Orientation := 1
pvt.PivotFields("Numero").Position := 1
pvt.PivotFields("Turno").Orientation := 1
pvt.PivotFields("Turno").Position := 2
pvt.AddDataField(pvt.PivotFields("Tipo 2"), "Cuenta de Tipo 2", -4112)	;xlCount := -4112 
pvt.PivotFields("Falla1").Orientation := 1
pvt.PivotFields("Falla1").Position := 3
pvt.PivotFields("Defecto").Orientation := 1
pvt.PivotFields("Defecto").Position := 4
pvt.PivotFields("Comp").Orientation := 1
pvt.PivotFields("Comp").Position := 5
pvt.InGridDropZones := true
pvt.RowAxisLayout(1)                           		;xlCompactRow = 0 ;  xlOutlineRow = 2
;xl.ActiveWorkbook.ShowPivotTableFieldList := False
xl.Columns("Q:V").AutoFit
return

Marine Corps Gen. Joseph Dunford told senators at his Joint Chiefs of Staff confirmation hearing : “If you want to talk about a nation that could pose an existential threat to the United States, I'd have to point to Russia. And if you look at their behavior, it's nothing short of alarming.”