XLS_file_path3 := A_WorkingDir . "\매입매출2.xls"
Xl := ComObjCreate("Excel.Application") ;// create a handle to a new excel application
Xl := ComObjActive("Excel.Application") ;// creates a handle to your currently active excel sheet
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;저장하기
엑셀03.ActiveWorkbook.Save()
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;다른 이름으로 저장하기
BookName:= A_WorkingDir . "\매입매출원본3.xls"
px1.ActiveWorkbook.SaveAs(BookName) ;'bookname' is a variable with the path and name of the file you desire
px1_Workbook := px1.Workbooks.Open(BookName) ;handle to specific workbook
px1_Workbook.Save()
px1.Visible := True
;%%%%%%%%%%%%%%%%%%%%
XL.ActiveWorkbook.SaveAs(BookName) ;'bookname' is a variable with the path and name of the file you desire
Xl_Workbook := Xl.Workbooks.Open(Path) ;handle to specific workbook
Xl_Workbook.Save() ;quick save already existing file
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;끝내기
엑셀03.ActiveWorkBook.Close
엑셀03.Quit
X1.Workbooks("BookName.xls".Close SaveChanges:= False
X1.Workbooks.Close(False)
X1.Workbooks.Close
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;셀 다루기
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;기록하기
엑셀03.Range("A4").Value := "hello world!"
val7 := "hello!"
엑셀03.Range("A5").Value := val7
엑셀03.Range( "A1").Value := 3
엑셀03.Range( "A2").Value := 7
엑셀03.Range( "B" . k ).Value := Title
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;수식넣기
엑셀03.Range( "A3").Formula := "=SUM(A1:A2)"
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;셀 꾸미기
엑셀03.Range( "A1:A3").Interior.ColorIndex := 19
엑셀03.Range( "A3").Borders(8).LineStyle := 1
엑셀03.Range( "A3").Borders(8).Weight := 2
엑셀03.Range( "A3").Font.Bold := 1
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;셀 서식
Xl.Range("A:A").NumberFormat := "@" ;change the column format to 'text'
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;선택하기
엑셀03.Range( "A4").Select
;엑셀03.Range("A4:C". endRow2 ).Select
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;값 알아내기
Val5 := 엑셀03.Range( "A5").Value
val2 :=px1.Sheets("재고").Range("B1").Value
ValueF :=엑셀03.Range("F" . num ).Value
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;복사
엑셀03.Range["K4:K" . Valueff ].Copy
;엑셀03.Range["K4:K" . Valueff ].Select
;엑셀03.Selection.Copy
엑셀03.Sheets("날짜별통계").Select
엑셀03.Range("A4").Select
엑셀03.ActiveSheet.Paste
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;유효줄수
endRow := 엑셀03.ActiveSheet.UsedRange.Rows.Count
endCol := 엑셀03.ActiveSheet.UsedRange.Columns.Count
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;열넓이 조정하기
엑셀03.Columns("A").ColumnWidth := 40, 엑셀03.Columns("B").ColumnWidth := 20
엑셀03.Columns("C").ColumnWidth := 30, 엑셀03.Columns("D").ColumnWidth := 30
엑셀03.Columns("E").ColumnWidth := 40, 엑셀03.Columns("F").ColumnWidth := 30
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;정렬하기
;Sort sheet by column:
엑셀03.Range("H1:Q100").Sort(엑셀03.Columns(1), 1) ;A열을 기준으로 올림차순 sort sheet by data in the 'a' column
엑셀03.Range("A2:I2000").Sort(엑셀03.Columns(8), 1) ;H열을 기준으로 올림차순 sort sheet by data in the 'H` column
엑셀03.Range("A2:I2000").Sort(엑셀03.Columns(8), 2) ;H열을 기준으로 내림차순 sort sheet by data in the 'H` column
objRange1 :=8
엑셀03.Range("A2:I2000").Sort(엑셀03.Columns(objRange1), 1) ;H열을 기준으로 올림차순sort sheet by data in the 'a' column
엑셀03.Range("A2:I2000").Sort(엑셀03.Columns(objRange1), 2) ;H열을 기준으로 내림차순sort sheet by data in the 'a' column
objRange1:=8
엑셀03.Range["4:" . Valueff ].Sort(엑셀03.Columns(objRange1), 1) ;오름차순
엑셀03.Range["A4:I" . Valueff ].Sort(엑셀03.Columns(objRange1), 1) ;오름차순
엑셀03.Range("AO" . aphegitaline2w . ":AS" . g).Sort(엑셀03.Columns(objRange1),1)
;%%%%%%%%%%%%%%%%%%
;삭제
엑셀03.ActiveSheet.Range( "C2:D10").Select
엑셀03.Selection.Delete
엑셀03.ActiveSheet.Range("E:E").Delete
엑셀03.ActiveSheet.Range("F:F").Select.Delete
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;시트 다루기
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;시트추가
px2.Sheets.Add
px2.Sheets("Sheet1").Select
;시트추가 이동
;&&&&&&&&&&&&&&&&&&
_ := ComObjMissing()
Sheet := 엑셀03.ActiveSheet
;엑셀03.Sheets.Add(_, 엑셀03.Sheets(엑셀03.Sheets.Count), _,_) ; adds a sheet after the last sheet
엑셀03.Sheets.Add(_, 엑셀03.Sheets(엑셀03.Sheets.Count)) ; adds a sheet after the last sheet
;엑셀03.Sheets.Add(_, 엑셀03.Sheets(2), _,_) ; adds a sheet after the second sheet
;엑셀03.Sheets.Add(_, 엑셀03.Sheets(2)) ; adds a sheet after the second sheet
;&&&&&&&&&&&&&&&&&&
_ := ComObjMissing()
Book:= 엑셀03.Workbooks.Open(xlFile)
엑셀03.Visible:=True
Sheet := 엑셀03.ActiveSheet
;Book.Sheets.Add(_, Book.Sheets(Book.Sheets.Count), _,_) ; adds a sheet after the last sheet
Book.Sheets.Add(_, Book.Sheets(Book.Sheets.Count)) ; adds a sheet after the last sheet
;Book.Sheets.Add(_, Book.Sheets(2), _,_) ; adds a sheet after the second sheet
;Book.Sheets.Add(_, Book.Sheets(2)) ; adds a sheet after the second sheet
;&&&&&&&&&&&&&&&&&&&&&
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;시트이름변경
엑셀03.Sheets("Sheet1").Name := "폴더순"
엑셀03.ActiveSheet.Name := "폴더순2"
엑셀03.Sheets["폴더순2"].Activate
엑셀03.Sheets("재고2").Select
;;엑셀03.ActiveSheet.Name("폴더순")
;;px3.ActiveSheet.Paste
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;시트선택하기
amms:="폴더별목차"
엑셀03.Sheets[ amms ].Activate
엑셀03.Sheets[ amms ].Select
Val1 :=엑셀03.Range("C1").Value
val2 :=엑셀03.Sheets("폴더별목차").Range("B1").Value
px1.Sheets["재고"].Activate
px1.Sheets("재고").Select
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;while구문이용하기
while (px1.Range("A" . A_Index).Value != "") {
px1.Range("A" . A_Index).Value := value
MsgBox,4100,%A_Index%,%value%
ifmsgbox,no,exitapp
}
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;시트전체복사
엑셀03.Cells.Select
엑셀03.Selection.Copy
엑셀03.Sheets("폴더이동").Select
엑셀03.ActiveSheet.Paste
px2.Cells.Select
px2.Cells.Copy ;시트전체 카피
;px2.ActiveSheet.Copy ;새로운 엑셀파일로 시트카피
;px2.ActiveSheet.UsedRange.Copy ;유효시트내용만 카피
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;시트수
sheetCount := 엑셀03.Sheets.Count, sheetIndex := "", sheetName := ""
sheetCount := 엑셀03.Sheets.Count
MsgBox,4100,%sheetCount%시트수,%endRow%줄수
ifmsgbox,no,exitapp
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;시트 이동 복사
;원래 시트 순서 123폴더순
엑셀03.ActiveSheet.Move(엑셀03.Sheets(1)) ;선택시트를 첫번째로 이동;;폴더순123;;# move active sheet to front
엑셀03.ActiveSheet.Move(엑셀03.Sheets(3)) ;선택시트를 세번째로 이동;;12폴더순3;;# move active sheet to front
엑셀03.ActiveSheet.Copy(엑셀03.Sheets(1)) ;선택시트를 첫번째로 복사;;폴더순(2)123폴더순;;# move active sheet to front
엑셀03.ActiveSheet.Copy(엑셀03.Sheets(3)) ;선택시트를 세번째로 복사;;12폴더순(2)3폴더순;;# move active sheet to front
;첫번째 시트를 선택 시트 뒤로 이동;;;23폴더순1// .Move(Before, After)
엑셀03.Sheets(1).Move(ComObjMissing(),엑셀03.Sheets("폴더순"))
;세번째 시트를 선택 시트 뒤로 이동;;;12폴더순3// .Move(Before, After)
엑셀03.Sheets(3).Move(ComObjMissing(),엑셀03.Sheets("폴더순"))
;세번째 시트를 선택 시트 뒤로 복사;;;123폴더순3(2)// .Move(Before, After)
엑셀03.Sheets(3).Copy(ComObjMissing(),엑셀03.Sheets("폴더순"))
;새 시트를 추가하여 전체시트의 맨끝으로 이동하기
_ := ComObjMissing()
;엑셀03.= 엑셀03.Work엑셀03..Open(path)
엑셀03.Visible:=True
Sheet := 엑셀03.ActiveSheet
;엑셀03.Sheets.Add(_, 엑셀03.Sheets(엑셀03.Sheets.Count), _,_) ; adds a sheet after the last sheet
엑셀03.Sheets.Add(_, 엑셀03.Sheets(엑셀03.Sheets.Count)) ; adds a sheet after the last sheet
;엑셀03.Sheets.Add(_, 엑셀03.Sheets(2), _,_) ; adds a sheet after the second sheet
;엑셀03.Sheets.Add(_, 엑셀03.Sheets(2)) ; adds a sheet after the second sheet
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
; 2번째 시트이름 알아내기
sheetName2 := 엑셀03.Sheets(2).Name, sheetIndex .= sheetName
sheetName2 := 엑셀03.Sheets(2).Name
msgbox,4100,두번째 시트이름,%sheetName2%
ifmsgbox,no,exitapp
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;전체시트이름
sheetCount := 엑셀03.Sheets.Count, sheetIndex := "", sheetName := ""
Loop, %sheetCount%
{
sheetName := 엑셀03.Sheets(A_Index).Name, sheetIndex .= sheetName
If A_Index > %sheetCount%
Break
allsheetName=%allsheetName%%sheetName%`,
} ; Loop, %sheetCount%
msgbox,4100,%sheetCount%,%sheetName%`n`n%allsheetName%
ifmsgbox,no,exitapp
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;전체 시트 정렬
msgbox,4100,시트정렬, A->Z순으로 정렬할려면 yes를 아니면 No를 선택하세요.
ifmsgbox,yes
Ascending := TRUE
ifmsgbox,no
px3 := ComObjActive("Excel.Application")
Opt1:=px3.ScreenUpdating, Opt2:=px3.EnableEvents, Opt3:=px3.Calculation, Opt4:=px3.Cursor ; store default setting
px3.ScreenUpdating:=False, px3.EnableEvents:=False, px3.Calculation:=-4135, px3.Cursor:=1 ; performance setting
sheetCount := px3.Sheets.Count, sheetIndex := "", sheetName := ""
Loop, %sheetCount%
{
sheetName := px3.Sheets(A_Index).Name, sheetIndex .= sheetName
Indexing := (A_Index/sheetCount)*100
; Progress, %Indexing%, Indexing. %A_Index%/%sheetCount%,, Sort Sheet %updateInterval%
If A_Index = %sheetCount%
Break
sheetIndex .= "/"
}
;Progress, 100, Checking,, Sort Sheet
If Ascending
Sort sheetIndex, F StrCmpLogicalA D/
Else
Sort sheetIndex, F StrCmpLogicalD D/
Sleep, 100
Loop, parse, sheetIndex, /
{
targetIndex := px3.Sheets(A_LoopField).Index
If targetIndex*1 <> A_Index
{
px3.Sheets(targetIndex).Move(px3.Sheets(A_Index))
Sorting := (A_Index/sheetCount)*100
;Progress, %Sorting%, Sorting. %A_Index%/%sheetCount%,, Sort Sheet
}
}
px3.Cursor:=Opt4, px3.Calculation:=Opt3, px3.EnableEvents:=Opt2, px3.ScreenUpdating:=Opt1 ; restore default setting
StrCmpLogicalA(str1, str2){
Loop, 2
MultiByteToWideChar(str%A_Index%)
return DllCall("ShlWapi\StrCmpLogicalW", "UInt", &str1, "UInt", &str2)
}
StrCmpLogicalD(str2, str1){
Loop, 2
MultiByteToWideChar(str%A_Index%)
return DllCall("ShlWapi\StrCmpLogicalW", "UInt", &str1, "UInt", &str2)
}
MultiByteToWideChar(ByRef str){
ostr := str, size = 0
Loop, 2 {
VarSetCapacity(str, size*2)
size := DllCall("MultiByteToWideChar", "UInt", 65001, "UInt", 0, "UInt", &ostr, "int", -1, "UInt", &str, "int", size)
}
}
;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%