°ìÈÌ †
Tips †ÅºÉÕ¥Õ¥¡¥¤¥ë¤ò¤Ä¤±¤Æ¥á¡¼¥ë †²èÌ̤ò¥¥ã¥×¥Á¥ã¤¹¤ë †Google¥Þ¥Ã¥×ɽ¼¨ †CSV¤äExcel¤ËSQL¤ò¼Â¹Ô †¥Þ¥¯¥í¤Î¼«Æ°¼Â¹Ô¤òÍÞÀ© †
·Ù¹ðÍÞÀ© †Application.DisplayAlerts = False '·Ù¹ðÍÞÀ© ¥Õ¥¡¥¤¥ë¥À¥¤¥¢¥í¥°¡Ê¥ª¡¼¥×¥ó¡Ë †Dim dlg As FileDialog Set dlg = Application.FileDialog(msoFileDialogOpen) dlg.InitialFileName = "C:\Windows\" dlg.Show 'execute ¤À¤È³«¤¯½ê¤Þ¤Ç¤ä¤Ã¤Æ¤¯¤ì¤ë Cells(1, 1) = dlg.SelectedItems(1)
¥¹¥¯¥ì¥¤¥Ô¥ó¥° †¥¢¥É¥¤¥ó´ØÏ¢ †Excel¤ËGoogle Maps API¤ÇÃϿޤòËä¤á¹þ¤ß¡¢½»½ê¸¡º÷¡¢³ÈÂç¡¿½Ì¾®¡¢ÃϿ޼ïÎàÊѹ¹ †³«¤¤¤Æ¤¤¤ë¥Õ¥¡¥¤¥ë¤ÎÃæ¤«¤é̾Á°¤Îº¸Â¦¤¬°ú¿ô¤È¹çÃפ¹¤ë¥Õ¥¡¥¤¥ë¤òõ¤¹ †'£²¤Ä°Ê¾å¤¢¤Ã¤¿¤é¥¨¥é¡¼ Function schFile(name As String) As Workbook Dim cnt As Integer cnt = 0 Set schFile = Nothing Dim bk As Workbook For Each bk In Workbooks Dim ln As Integer ln = Len(name) Debug.Assert (ln > 0) If StrConv(Left(bk.name, ln), vbUpperCase) = StrConv(name, vbUpperCase) Then If Not (schFile Is Nothing) Then MsgBox ("[" & name & "]¤Ç»Ï¤Þ¤ë¥Õ¥¡¥¤¥ë¤¬£²¤Ä°Ê¾å¤¢¤ê¤Þ¤¹") End End If Debug.Assert (schFile Is Nothing) 'Ʊ¤¸Ì¾Á°¤Ç»Ï¤Þ¤ë¥Õ¥¡¥¤¥ë¤¬£²¤Ä°Ê¾å¤¢¤Ã¤¿¤é¥¨¥é¡¼¤Ë¤Ê¤ê¤Þ¤¹ Set schFile = bk '¤³¤³¤Ç¤¹¤°¤ÏÊÖ¤é¤Ê¤¤¡¡£²¤Ä°Ê¾å¤¢¤Ã¤¿¤é¤Þ¤º¤¤¤«¤é End If Next If schFile Is Nothing Then MsgBox ("[" & name & "]¤Ç»Ï¤Þ¤ë¥Õ¥¡¥¤¥ë¤¬¸«¤Ä¤«¤ê¤Þ¤»¤ó") End End If End Function countifs¤Î¿ô¼°¤ò¸µ¤Ë¡¢Æ±¤¸¾ò·ï¤Ç¥Õ¥£¥ë¥¿¤ò¤«¤±¤ë †Sub ¿ô¼°¤«¤é¥Õ¥£¥ë¥¿¡¼(r As Range) Dim strSuusiki As String Dim varPrm As Variant Dim sh As Worksheet Dim ac As Worksheet Dim lngLoop As Long Dim lngColNo As Long Dim strJoken As String Dim rngFil As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set sh = Sheets("hoge") Set ac = r.Worksheet strSuusiki = r.Formula ''³ºÅö¥»¥ë¤Î¿ô¼°¤ò¼è¤ê½Ð¤¹ strSuusiki = Replace(strSuusiki, "=countifs(", "", , , vbTextCompare) '¡Ç;ʬ¤Ê¤È¤³¤í¤ò¾Ã¤¹ strSuusiki = Replace(strSuusiki, "=countif(", "", , , vbTextCompare) '¡Ç;ʬ¤Ê¤È¤³¤í¤ò¾Ã¤¹ strSuusiki = Replace(strSuusiki, ")", "") '¡Ç;ʬ¤Ê¤È¤³¤í¤ò¾Ã¤¹ varPrm = Split(strSuusiki, ",") '¡ÇCountifs¤Î¥Ñ¥é¥á¡¼¥¿¤Î¤ß¤òÇÛÎó¤Ëʬ³ä¤¹¤ë If Not sh.AutoFilterMode Then ''¥ª¡¼¥È¥Õ¥£¥ë¥¿¡¼¤¬ÉÕ¤¤¤Æ¤Ê¤¤¾ì¹ç Intersect(sh.UsedRange, sh.Range("10:65535")).AutoFilter '¡Ç¥Õ¥£¥ë¥¿¡¼¤ò¤Ä¤±¤ë End If If sh.FilterMode Then '¡Ç¥Õ¥£¥ë¥¿¡¼¤¬¤«¤«¤Ã¤Æ¤¤¤ë¾õÂ֤ΤȤ¤Ï¥Õ¥£¥ë¥¿¡¼¥¯¥ê¥¢ sh.ShowAllData End If Set rngFil = sh.AutoFilter.Range For lngLoop = 0 To UBound(varPrm) Step 2 lngColNo = Range(varPrm(lngLoop)).Column ''ÇÛÎó¤Î¶ö¿ôÈÖÌܡḡº÷¾ò·ïÈϰϤΥ«¥é¥àÈÖ¹æ¤ò¼èÆÀ strJoken = varPrm(lngLoop + 1) ''ÇÛÎó¤Î´ñ¿ôÈÖÌܡḡº÷¾ò·ï¤ò¼èÆÀ If InStr(strJoken, """") > 0 Then strJoken = Replace(strJoken, """", "") '¡Ç""¤¬¤¢¤ë¾ì¹ç¤ÏÄê¿ô¡Ê¡É¡É¤Ï¾Ã¤¹¡Ë Else strJoken = ac.Range(strJoken).Value '¡Ç¡É¡É¤¬¤Ê¤¤¾ì¹ç¤Ï¥»¥ë¤Ø¤Î»²¾È¤Î¤¿¤á»²¾ÈÀè¤ÎÃͤò¼èÆÀ End If rngFil.AutoFilter Field:=lngColNo, Criteria1:=strJoken Next Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub ¥Ï¥¤¥Ñ¡¼¥ê¥ó¥¯¤ò¥¯¥ê¥Ã¥¯¤·¤¿¤é¾å¤Î¹Ô¡Ê£³¹ÔÌܡˤˤ¢¤²¤ë †Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Dim r As Integer r = Target.Range.row If r = 3 Then Exit Sub Else ThisWorkbook.Worksheets(1).Activate Rows("3:3").Select Selection.EntireRow.Insert r = r + 1 Rows(r & ":" & r).Select Selection.Cut Range("A3").Select ActiveSheet.Paste Range("A" & r).Select Selection.EntireRow.Delete End If End Sub ¥»¥ë¤ÎÃͤòʸ»úÎó¤È¤·¤Æ¼èÆÀ¤·¤¿¤¤¾ì¹ç †Cells(i,j).Text ¥Õ¥£¥ë¥¿·Ï †
¸½ºß¸«¤Æ¤¤¤ë¥Ö¥Ã¥¯¤ÎÁ´¥·¡¼¥È¤«¤é_YMDHMS¤È¤¤¤¦ÃÍ¤ÎÆþ¤Ã¤¿¥»¥ë¤ò¤¹¤Ù¤ÆÃµ¤·¡¢¤½¤Î²¼¤Ë¤¢¤ë¥»¥ë¤Ë¥Õ¥©¡¼¥Þ¥Ã¥È¤òÀßÄꤹ¤ë †Sub ÆüÉդΥե©¡¼¥Þ¥Ã¥È½¤Àµ() Debug.Print ("¥Õ¥©¡¼¥Þ¥Ã¥È½¤Àµ³«»Ï") Dim bk As Workbook Dim sht As Worksheet Set bk = ActiveWorkbook Dim i As Integer For i = 1 To bk.Worksheets.Count Set sht = bk.Worksheets(i) With sht .Activate .Cells(1, 1).Select Dim r As Range Set r = Cells.Find(What:="_YMDHMS", After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, MatchByte:=False, SearchFormat:=False) If Not r Is Nothing Then Dim firstPos As String firstPos = r.Address Do Dim s As String .Cells(r.row, r.Column).Select If Trim(.Cells(r.row + 1, r.Column)) <> "" Then s = .Cells(r.row, r.Column) Debug.Print (.Name & ":" & r.row & "," & r.Column & "," & s) .Cells(r.row + 1, r.Column).NumberFormatLocal = "yyyy-mm-dd hh:mm:ss" .Cells(r.row + 1, r.Column).HorizontalAlignment = xlLeft Dim rcol As Range Set rcol = .Columns(r.Column) rcol.AutoFit End If Set r = Cells.FindNext(r) DoEvents Loop While (r.Address <> firstPos) Else '¤½¤â¤½¤â¤Ê¤¤ End If End With Next MsgBox ("OK") End Sub º£ÁªÂò¤·¤Æ¤¤¤ë¥·¡¼¥È †
¡Ö̾Á°¡×¤ÎÁ´¾Ã¤· †
¥Ñ¥¹¥ï¡¼¥ÉÉÕ¤¤Ç¥Ö¥Ã¥¯¤òÊݸ¤¹¤ë †ActiveWorkbook.SaveAs Filename:= _ "hoge.xls", FileFormat:= _ xlExcel8, Password:="anestec", WriteResPassword:="", ReadOnlyRecommended _ :=False, CreateBackup:=False ¼ê¤Ã¼è¤êÁ᤯Excel¥·¡¼¥È¤Î°ìÍ÷¤òÆÀ¤ë¤Ë¤Ï †
¡ÎEsc¡Ï¥¡¼¤Ë¤è¤ëExcel VBA¤Î¼Â¹ÔÃæÃǤòËɻߤ¹¤ë †
¸ß´¹À¥Á¥§¥Ã¥¯¤Î¥À¥¤¥¢¥í¥°¤¬½Ð¤ë¤Î¤òÍÞÀ© †
Excel¤ÇÆÃÄê¤ÎÃͤˤʤ俤Ȥ¤Î¥¢¥¯¥·¥ç¥ó †
¥Ä¥ê¡¼¥Ó¥å¡¼¥³¥ó¥È¥í¡¼¥ë †
VBA ¥³¥ó¥Ñ¥¤¥ë¥¨¥é¡¼¤È¼Â¹Ô»þ¥¨¥é¡¼ †
CSV¤òUTF-8¤ÇÊݸ¤¹¤ë †
Shift JISʸ»úÎó¤òUTF-8¤Ë †Public Function encodeUTF8(ByRef strUni As String) As Byte() encodeUTF8 = ADOS_Encode("UTF-8", strUni) End Function Private Function ADOS_Encode(ByVal cset As String, ByRef strUni As String) As Byte() Dim objStm As ADODB.Stream Set objStm = New ADODB.Stream objStm.Open objStm.Type = adTypeText objStm.Charset = cset objStm.WriteText strUni objStm.Position = 0 objStm.Type = adTypeBinary Select Case UCase(cset) Case "UNICODE", "UTF-16" objStm.Position = 2 Case "UTF-8" objStm.Position = 3 End Select ADOS_Encode = objStm.Read() objStm.Close End Function ÉÁ²èÍÞÀ© †Application.ScreenUpdating = false ¥·¡¼¥ÈʤÙÂØ¤¨¥µ¥ó¥×¥ë †
Sub ¥·¡¼¥ÈʤÙÂØ¤¨() Dim bk As Workbook Set bk = ThisWorkbook Dim sht As Worksheet Set sht = ActiveSheet Dim names() As String Dim i As Integer '¥·¡¼¥È̾ÆÉ¤ß¹þ¤ß Dim j As Integer j = 0 For i = 2 To 1000 '¤È¤ê¤¢¤¨¤º1000¥·¡¼¥È¤Þ¤Ç If sht.Cells(i, 2) <> "" Then ReDim Preserve names(j) names(j) = sht.Cells(i, 2) j = j + 1 Else Exit For End If Next For i = 0 To UBound(names) Dim wsht As Worksheet Set wsht = bk.Worksheets(names(i)) Call wsht.Move(After:=bk.Worksheets(1 + i)) Next MsgBox ("OK") End Sub Excel¥·¡¼¥È¥¢¥ë¥Õ¥¡¥Ù¥Ã¥È½çʤÙÂØ¤¨ †¥·¡¼¥È¾å¤Î¥·¥§¥¤¥×¤Î¿§¤ò¤Þ¤È¤á¤Æ¹õ¤ËÌ᤹Îã †Sub Macro1() Dim i As Integer Dim acs As Worksheet Set acs = ActiveSheet For i = 1 To acs.Shapes.Count Dim shap As Shape Set shap = acs.Shapes(i) With shap acs.Activate shap.Select DoEvents If (shap.Type = msoAutoShape) Then If shap.AutoShapeType = msoShapeMixed Then '¥³¥ó¥Ô¥å¡¼¥¿¿Þ¤È¤« ElseIf shap.AutoShapeType = msoShapeRoundedRectangle Then '³Ñ¤Î´Ý¤¤»Í³Ñ Selection.Font.ColorIndex = xlAutomatic End If ElseIf shap.Type = msoLine Then Selection.ShapeRange.Line.ForeColor.SchemeColor = 64 Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255) ElseIf shap.Type = msoTextBox Then Selection.Font.ColorIndex = xlAutomatic End If End With Next End Sub ÎóÉý¤ò¹ç¤ï¤»¤ë †'ost ¤ÏÂоݥ·¡¼¥È ost.Activate ost.Range(ost.Cells(1, 1), ost.Cells(osr, 8)).Columns.AutoFit
¥Þ¥¯¥í¤Î¤¢¤ë¥Ö¥Ã¥¯ †
¥·¥§¥¤¥×¤òºÇÇØÌÌ¤Ë°ÜÆ° †Selection.ShapeRange.ZOrder msoSendToBack 'ºÇÇØÌÌ¤Ë°ÜÆ° AddTextBox ¤ËÅϤ¹ºÂɸ¤ÎÅϤ·Êý †
¥Ö¥Ã¥¯¤Î¥·¡¼¥È¥¤¥ó¥Ç¥Ã¥¯¥¹¤òºî¤ë †
¥·¡¼¥È¤Ë²èÁü¤ò¼è¤ê¹þ¤ß¡¢¤½¤ÎÀê¤á¤Æ¤¤¤ëÈϰϤᦲ¼¥»¥ë¤òÆÀ¤ë †Dim acs As Worksheet Set acs = ActiveSheet acs.Cells(1, 1).Select acs.Pictures.Insert(²èÁü¤Îpath).Select Dim r As Range Set r = acs.Pictures(1).BottomRightCell ¤¤¤é¤Ê¤¤¥·¡¼¥È¤ò¾Ã¤¹ †Application.DisplayAlerts = False '·Ù¹ðÍÞÀ© With outbook 'ËÜÅö¤Ï£³¤Ä¤È¤Ï¸Â¤é¤Ê¤¤¤¬¡ÊExcel¤ÎÀßÄê¤Ë¤è¤Ã¤ÆÊѤï¤ë¤Î¤Ç¡Ë¡¢¤È¤ê¤¢¤¨¤º .Worksheets("Sheet1").Delete .Worksheets("Sheet2").Delete .Worksheets("Sheet3").Delete End With
¥×¥í¥°¥é¥à¤ÎƱ´üŪ¤Ê¼Â¹Ô †
ɽ¼¨ÇÜΨ¤òÊѤ¨¤ë †ActiveWindow.Zoom = 75 ¥Ï¥¤¥Ñ¡¼¥ê¥ó¥¯ºîÀ® †É½¼¨Ê¸»úÎó = "hoge" ¥¢¥É¥ì¥¹ = "http://www.kernel-net.ne.jp/tech/" Range("A1").Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, _ Address:=¥¢¥É¥ì¥¹, TextToDisplay:=ɽ¼¨Ê¸»úÎó
°õºþÀßÄê ±¦²¼¤Ë¥Ú¡¼¥¸¿ô¤òÆþ¤ì¤ë †ActiveSheet.PageSetup.RightFooter = "&P/&N" °õºþÈϰϤÎÀßÄê †ActiveSheet.PageSetup.PrintArea = "$A$1:$N$83" 'A1·Á¼° Range¤Ç¤Ï¥À¥á¤Ç¤¹ Range¤«¤éA1·Á¼°¤Îʸ»úÎó¤òÆÀ¤ë¤Ë¤Ï †str = Cells(row, col).Address Worksheets.Add¤ÎAfter°ú¿ô¤Ê¤É¤ÇÅϤ¹¤Î¤Ï¥·¡¼¥È̾¤Ç¤Ï¤Ê¤¤ †
VBA¤Ç¥Ç¡¼¥¿¤¬ÊѤï¤Ã¤¿ÀÚ¤ìÌܤDzþ¥Ú¡¼¥¸¤·¤¿¤¤ †
°õºþ²þ¥Ú¡¼¥¸¤òÄ´À°¤¹¤ë †'°õºþ¥Ö¥ì¡¼¥¯°ÌÃÖÈùÄ´À° 13·åÌܤÀ¤Ã¤¿¤é±¦¤Ë£±¤Ä¤º¤é¤¹ 'ľÀÜ»ØÄê¤Ç¤¤Ê¤¤¤Î¤À¤í¤¦¤«? Function adjust_print(st As Worksheet) As Integer Debug.Print (st.Name) st.Activate st.Cells(1, 14).Select '¤³¤ì¤Ç¤ÏÊѤï¤Ã¤Æ¤¯¤ì¤Ê¤¤¤è¤¦¤À If st.VPageBreaks.Count = 0 Then st.VPageBreaks.Add st.Range("N1") Else st.VPageBreaks(1).Location = st.Range("N1") End If '¤³¤¦¤·¤Ê¤¤¤È¤¢¤ï¤»¤é¤ì¤Ê¤¤¤Î¤À¤í¤¦¤«? If st.VPageBreaks(1).Location.Column = 13 Then ActiveWindow.View = xlPageBreakPreview ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1 ActiveWindow.View = xlNormalView End If st.Cells(1, 1).Select adjust_print = 0 End Function Sub °õºþ°ÌÃÖ¹ç¤ï¤»() ' Dim shts As Sheets Set shts = ActiveWorkbook.Worksheets Dim i As Integer Dim st As Worksheet 'Âоݥ·¡¼¥ÈËè For i = 4 To shts.Count Set st = shts(i) Call adjust_print(st) DoEvents Next End Sub CSV·Á¼°¤ÇÊݸ¤¹¤ë †ActiveWorkbook.SaveAs Filename:="hoge.csv", FileFormat:=xlCSV, CreateBackup:=False ¥Ç¡¼¥¿¤Î¤¢¤ëºÇ¸å¤Î¹Ô¤òÊÖ¤¹ †e=Worksheets("sheet1").Range("A1").End(xlDown).Row ¤â¤·¤¯¤Ï e=Worksheets("sheet1").Range("A65536").End(xlUp).Row '¥Ç¡¼¥¿Ãæ¤Ë¶õ¤¹Ô¤¬¤¢¤ë¾ì¹ç
¥´¡¼¥ë¥·¡¼¥¯¤ò¼Â¹Ô¤µ¤»¤ë †'E3¤ÎÃͤ¬0¤Ë¤Ê¤ë¤è¤¦¤ËB3¤òÊѲ½¤µ¤»¤ë¾ì¹ç Range("E3").GoalSeek Goal:=0, ChangingCell:=Range("B3") ¥Ö¥Ã¥¯¤ò³«¤¤¤Æ¥·¡¼¥ÈËè¤Ë¤Ê¤Ë¤«¤¹¤ë¥µ¥ó¥×¥ë †Function prcBook(bk As String) As Integer Debug.Print ("-------book:" & bk & "¥¹¥¿¡¼¥È---------------------") Dim wbk As Workbook '»ØÄê xls ¤ò³«¤¯ 'Set wbk = Application.Workbooks(bk) Set wbk = Workbooks.Open(bk) wbk.Activate Dim cnt As Integer cnt = wbk.Worksheets.Count Debug.Assert (cnt > 0) Dim i As Integer For i = 1 To cnt Dim wst As Worksheet Set wst = wbk.Worksheets(i) Call prcSheet(wst) Next wbk.Close Debug.Print ("-------½èÍý½ª¤ï¤ê---------------------") End Function VBA¥×¥í¥¸¥§¥¯¥È¤ò¥í¥Ã¥¯¤¹¤ë †
VBA¥Õ¥¡¥¤¥ë¤Î¥Ç¡¼¥¿¹½Â¤ † |