Category Archives: Office

open external program from java for mac os x

Posted on 2017 年 03 月 24 日 by cowman.chiang

String[] cmds = {“open”, tempFile.getAbsolutePath(), “-a”, “/Applications/Microsoft Office 2011/Microsoft Excel.app”}; Runtime.getRuntime().exec(cmds);

Posted in Java, Mac, Office | Leave a comment

protect data validation to fail while copy and paste data

Posted on 2017 年 01 月 25 日 by cowman.chiang

Private Sub Worksheet_Change(ByVal Target As Range) ‘Does the validation range still have validation? Set range1 = Range(“B2:B6500”) If HasValidation(range1) Then Exit Sub Else Application.Undo MsgBox “您的操作將會被取消, ” & vbCrLf & “請使用下拉選單進行選擇”, vbCritical End If End Sub Private Function HasValidation(r) As … Continue reading →

Posted in Excel | Leave a comment

[Java] Write xls files

Posted on 2015 年 11 月 02 日 by cowman.chiang

public String createFile(Object object) { try { Excel excel = (Excel) object; HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); for (SheetContent sheetContent:excel.getSheets()) { HSSFSheet sheet = hssfWorkbook.createSheet(sheetContent.getName()); List<HSSFCellStyle> formats = new ArrayList<>(); /** format */ for (String format:sheetContent.getFormats()) { HSSFCellStyle cs = … Continue reading →

Posted in Excel, Java | Leave a comment

[Excel] Find the latest value

Posted on 2015 年 03 月 04 日 by cowman.chiang

If we want use excel to get the value F. F = A * B * C * D * E The common method is using the upper mathematical formulas. But if we adjust the columns, we may get the … Continue reading →

Posted in Excel | Leave a comment

物件名稱包含變數 + 應用紀錄

Posted on 2013 年 05 月 15 日 by cowman.chiang

Dim s1, s2 As Integer Sub OnSlideShowPageChange() s1 = 1 s2 = 1 With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex) If .Shapes.Title.TextFrame.TextRange.Text = “Windows 安裝Splunk” Then allhide selectphoto (s1) ElseIf .Shapes.Title.TextFrame.TextRange.Text = “Windows 設定 Forward” Then allhide_1 selectphoto_1 (s2) End If End With End Sub … Continue reading →

Posted in PowerPoint | Leave a comment

設定元件Visible屬性

Posted on 2013 年 02 月 08 日 by cowman.chiang

Sub Picture25_visible() With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex) If .Shapes.Title.TextFrame.TextRange.Text = “Splunk 優點” Then .Shapes(“圖片 25”).Visible = -1 .Shapes(“圖片 25”).ZOrder msoBringToFront End If End With End Sub Sub Picture25_hide() With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex) If .Shapes.Title.TextFrame.TextRange.Text = “Splunk 優點” Then .Shapes(“圖片 25”).Visible = msoFalse End If End … Continue reading →

Posted in Office, PowerPoint | Leave a comment

當投影片切換時 (VBA)

Posted on 2013 年 02 月 08 日 by cowman.chiang

Sub OnSlideShowPageChange() MsgBox ActivePresentation.Slides(ActiveWindow.Selection.SlideRange.SlideNumber).Shapes.Title.TextFrame.TextRange.Text End Sub

Posted in Office, PowerPoint | Leave a comment

Powerpoint VBA: 取得當前Slide編號

Posted on 2013 年 02 月 08 日 by cowman.chiang

主要有兩種 1. ActiveWindow.Selection.SlideRange.SlideNumber 此處主要是在一般狀態下 (非簡報播放) 適合用來Debug 2. SlideShowWindows(1).View.Slide.SlideIndex 此處主要是當簡報播放情況下 ( 所以一般Debug模式會出錯 ) 以下為實際應用 Sub Picture76_4() With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex) If .Shapes.Title.TextFrame.TextRange.Text = “Splunk for Unix and Linux” Then .Shapes(“Picture 4”).ZOrder msoBringToFront End If End With End Sub Sub Picture76_5() With ActivePresentation.Slides(ActiveWindow.Selection.SlideRange.SlideNumber) MsgBox … Continue reading →

Posted in Office, PowerPoint | Leave a comment

PowerPoint巨集，將物件移到最前面

Posted on 2013 年 01 月 31 日 by cowman.chiang

查詢物件名稱 Sub Who_AM_I() With ActiveWindow.Selection.ShapeRange(1) MsgBox .Name End With End Sub 將物件移到最前面 Sub Bringtofront() ActivePresentation.Slides(第幾張投影片).Shapes(“物件名稱”).ZOrder msoBringToFront End Sub

Posted in Office, PowerPoint | Leave a comment

Excel 跳格計算

Posted on 2012 年 01 月 11 日 by cowman.chiang

這是怪獸兩個多禮拜以前問的… 當時在首都上就沒辦法幫他找了 剛剛才想到有這個問題…Orz 真的老了 網路上找了一下相關的問題 使用下列就可以解決了 =SUM(IF(MOD(ROW(A1:A65535),5)=2,A1:B65535)) 按下ALT+SHIFT+ENTER變成陣列運算 其中主要是用MOD 5餘2的儲存格來計算 後面的A1:B65535主要就是要計算的範圍

Posted in Excel | Leave a comment