현재 프로젝트의 단순작업에 걸리는 시간을 줄이기 위해 만들어봤습니다.

단순한 코딩이고요. 개선하실 사안이 있으시면 변경하시거나 의견주셔도 됩니다.

파일 첨부했어요.

 

fab.xls - 초기버전

 

fab_v2.xls - LastRow를 변수로 교체

 

 

즐코딩~

 

코드만 보시려는 분들을 위해 내용도 붙여넣을께요

 

'---------------------------------------------------------------------------------------
' Procedure : Convert DB column names to Camel and Pascal cases
' Author    : Sean
' Purpose   : Reduce repetitive and time consuming converting tasks
' Copyright : I do not claim any copyrights over this code. The following may be altered and reused as you wish
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' DB Column Names   : Place column names in column A of Columns sheet.
'
' Usage:
' ~~~~~~
' ?camelCase()
'       Convert to Camel Case
' ?pascalCase()
'       Convert to Pascal Case
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2016-Mar-26             Initial Release
' **************************************************************************************
' Columns: DB column names
' Components: Nexacro component names
' Variables: VO variable and dataset column names
'
' ToDo.1: For문의 max값은 Range(Selection, Selection.End(xlDown)).Select 등을 이용하여 상수에서 변수로 변경.
'

Sub convert()
    'MsgBox "test"
  
    Worksheets("Components").Range("A1").EntireColumn.EntireRow.clearContents
    Worksheets("Variables").Range("A1").EntireColumn.EntireRow.clearContents
   
    copyRows
    Application.CutCopyMode = False
   
   
    Worksheets("Columns").Select
    Range("A1").Select
   
    pascalCase  'Components
    camelCase   'Variables

End Sub

Sub copyRows()
    Worksheets("Columns").Range("A1").EntireColumn.Select
    Selection.Copy
   
    Sheets("Variables").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
    Sheets("Components").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Sub pascalCase()
    Dim strCase As String
   
    Worksheets("Components").Select
    For x = 1 To 65536
        If (Len(Cells(x, 1)) > 0) Then
            strCase = Application.Substitute(Application.Proper(Cells(x, 1)), "_", "")
            strCase = "_" & strCase
            Cells(x, 1) = strCase
        End If
    Next
   
End Sub

Sub camelCase()
    Dim strCase As String
   
    Worksheets("Variables").Select
    'For Each x In Range("A1").EntireColumn
    For x = 1 To 65536
        'x.Value = LCase(x.Value)
        If (Len(Cells(x, 1)) > 0) Then
            strCase = Application.Substitute(Application.Proper(Cells(x, 1)), "_", "")
       
            'If (Len(strCase) > 0) Then
                Mid(strCase, 1, 1) = LCase(Mid(strCase, 1, 1))
            'End If
            Cells(x, 1) = strCase
        End If
    Next
   
End Sub

Sub clearContents()
    Worksheets("Columns").Range("A1").EntireColumn.EntireRow.clearContents
    Worksheets("Components").Range("A1").EntireColumn.EntireRow.clearContents
    Worksheets("Variables").Range("A1").EntireColumn.EntireRow.clearContents
End Sub


글 보관함

카운터

Total : / Today : / Yesterday :
get rsstistory!