현재 프로젝트의 단순작업에 걸리는 시간을 줄이기 위해 만들어봤습니다.
단순한 코딩이고요. 개선하실 사안이 있으시면 변경하시거나 의견주셔도 됩니다.
파일 첨부했어요.
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 'VariablesEnd 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 SubSub 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 SubSub 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 SubSub clearContents()
Worksheets("Columns").Range("A1").EntireColumn.EntireRow.clearContents
Worksheets("Components").Range("A1").EntireColumn.EntireRow.clearContents
Worksheets("Variables").Range("A1").EntireColumn.EntireRow.clearContents
End Sub
'SI개발 > VBA' 카테고리의 다른 글
[VBA] Format Yahoo Fantasy Players List (0) | 2023.02.03 |
---|---|
[VBA]디렉토리 안의 파일 목록 구하기2 (0) | 2022.12.11 |
[VBA] 차트 범례 추가, 삭제 (0) | 2021.03.27 |
[VBA]디렉토리 안의 파일 목록 구하기 (0) | 2017.10.28 |
[VBA] 엑셀 디아블로2 my log(엑셀), jimmy's log(본문) (0) | 2016.03.27 |