다음 코드에 문제가 있습니다. Vba 코드를 실행할 때마다 CleanFileName 및 CleanUsedRange 코드가 Vlook 수식을 삭제합니다.
vlook 수식을 삭제하지 않고 CleanFileName 및 CleanUsedRange를 사용할 수 있는 방법이 있습니까? 코드는 아래에 있습니다
Private Sub CommandButton1_Click()
Const FULL_PATH = "C:\Documents\test\quot.txt"
Dim fId As String, txt As String, txtLen As Long, d As Object, dc As Long
fId = FreeFile
Open FULL_PATH For Input As fId
txt = Input(LOF(fId), fId) 'Read entire file (not line-by-line)
Close fId
txtLen = Len(txt)
Set d = CreateObject("Scripting.Dictionary")
d("Name") = "C11" 'Same as: d.Add Key:="Name", Item:="C11"
d("Phone") = "H13"
d("Address1") = "C15"
d("Email") = "C13"
d("Postcode") = "H16"
d("SR") = "C10"
d("MTM") = "H14"
d("Serial") = "H15"
d("Problem") = "C17"
d("Action") = "C18"
d("Dated") = "H10"
dc = d.Count
Dim i As Long, k As String, sz As Long, found As Long
With ThisWorkbook.Worksheets("Sheet1") '<--- Update sheet name
For i = 0 To dc - 1 'd.Keys()(i) is a 0-based array
k = d.Keys()(i) 'Name, Phone, etc
found = InStr(txt, k) + Len(k) + 1 'Find the (first) key in file
If found > 0 Then 'Determine item length by finding the next key
If i < dc - 1 Then sz = InStr(txt, d.Keys()(i + 1)) Else sz = txtLen + 2
.Range(d(k)).Value2 = Trim$(Mid$(txt, found, sz - found - 1))
End If
Next
End With
End Sub
클린파일
Public Function CleanFileName(ByVal fName As String) As String
Dim b() As Byte, specialChars As Variant, i As Long
b = "\/:*?|<>" & Chr(34) & Chr(8) & Chr(9) & Chr(10) & Chr(13)
specialChars = Split(StrConv(b, vbUnicode), Chr(0))
fName = Trim$(fName) 'Trim, then remove \ / : * ? | < > " Backspace Tab LF CR
For i = 0 To UBound(specialChars)
fName = Replace(fName, specialChars(i), vbNullString)
Next
CleanFileName = fName
End Function
두 번째 코드
Public Sub CleanUsedRange(ByRef ur As Range)
Dim arr As Variant, r As Long, c As Long
arr = ur.Formula
For r = 1 To UBound(arr, 1)
For c = 1 To UBound(arr, 2)
arr(r, c) = CleanFileName(arr(r, c))
Next
Next
ur.Formula = arr
End Sub
내보내기 코드
Private Sub CommandButton2_Click()
Dim ws As Worksheet, fPath As String, fName As String, dt As String
Set ws = ThisWorkbook.Worksheets("Sheet1")
fPath = "C:\Documents\test\"
dt = Format(Date, " - MM-DD-YYYY")
CleanUsedRange ws.UsedRange
fName = fPath & ws.Range("C10") & dt & " - Quotation"
ws.Range("A1:I60").ExportAsFixedFormat Type:=xlTypePDF, FileName:=fName
End Sub
Vlook 수식은 다른 시트에서 데이터를 가져오는 데 사용되므로 하나씩 입력할 필요가 없습니다. vlook 수식을 제거하지 않고 클린파일을 비틀 수 있는 방법이 있습니까?
제안한대로 코드를 편집 한 후
Private Sub CommandButton2_Click()
Dim ws As Worksheet, fPath As String, fName As String, dt As String
Set ws = ThisWorkbook.Worksheets("Sheet1")
fPath = "C:\Users\Documents\test\\"
dt = Format(Date, " - MM-DD-YYYY")
Range("C10") = CleanFileName(Range("C10"))
fName = fPath & ws.Range("C10") & dt & " - Quotation"
ws.Range("A1:I60").ExportAsFixedFormat Type:=xlTypePDF, FileName:=fName
End Sub
그러나 내보낸 파일에는 아래와 같은 외부 기호가 있는 것으로 보입니다..
다른 해결책은 없나요?
제안한 대로 코드를 변경했지만 VBA를 실행할 수 없습니다. 이것을 추가한 후:
.Range(d(k)).Value2 = Trim$(Mid$(txt, found, sz - found - 2))
오류가 가리키는 곳이 바로 여기입니다.
Open FULL_PATH For Input As fId
사진 보기새로운 오류
또한 요청한 16진수 코드를 찾으세요.
코드를 다음으로 변경했습니다.
Else sz = txtLen + 3
하지만 여전히 오류 76이 표시됩니다.오류 76 그리고 디버그는 이 줄을 가리키고 있습니다.
Open FULL_PATH For Input As fId
답변1
CleanUsedRange()나는 귀하의 하위 사용자 가 CleanFileName()실제로 귀하의 Vlook수식(무엇이든)을 (완전히) 삭제하고 있다고 생각하지 않지만 CleanFileName()특정 문자를 제거하면 귀하의 수식이 파괴될 수 있습니다.
현재 코드가 문제를 일으키는 이유를 설명하기 위해 몇 줄을 분석해 보겠습니다.
당신 은 CommandButton2_Click()전화합니다 CleanUsedRange ws.UsedRange. 여기서는 의 사용된 범위를 ws참조 "Sheet1"하고 나타냅니다 . 즉, 첫 번째와 마지막 열(포함)과 첫 번째와 마지막 행(포함) 사이에 내용(예: 데이터 또는 수식)이 있거나 있는 셀 범위를 의미합니다.UsedRangews
에서는 CleanUsedRange()사용된 범위( 로 전달됨)의 모든 셀(각 행과 열)을 순회 ur As Range하고 CleanFileName()이러한 모든 셀의 내용을 호출합니다. 이 함수( CleanUsedRange())는 워크시트의 수식을 문자열로 함수에 전달하므로 수식을 파괴하는 주된 이유입니다 CleanFileName().
에서는 CleanFileName()전달된 인수에서 파일 이름에 유효하지 않은 특정 문자가 있는지 확인합니다. 수정된 인수는 함수의 결과로 반환됩니다.
수정: 사용된 전체 범위를 CleanUsedRange()및 에 전달하지 마세요 CleanFileName(). 실제로 CleanUsedRange()서브를 완전히 버릴 수 있습니다.
정리가 필요할 수 있는 파일 이름이 포함된 하나의 셀(C10 ?)만 에 전달합니다 CleanFileName().
IOW, CommandButton2_Click()교체 중
CleanUsedRange ws.UsedRange
~와 함께
Range("C10") = CleanFileName(Range("C10")).
(셀에 C10사용할 파일 이름이 있다고 가정합니다.)
"박스형 물음표" 관련 편집
"박스형 물음표" 문제의 경우 코드가 32 미만인 모든 문자가 .pdfExcel에서 생성된 파일에서 문제를 일으키는 것으로 나타났습니다(내 Excel에서는 Chr(12)만 "박스형 물음표"로 표시됨) . 분명히 각 필드 사이에는 두 개의 문자가 있으며 아마도 "캐리지 리턴 - 줄 바꿈"(CRLF) 쌍일 가능성이 있지만 아직 해당 정보를 제공하지 않았기 때문에 이를 확인할 수 있는 사람은 없습니다.
문자열에서 값을 읽을 때 txt다음 코드를 사용합니다.
.Range(d(k)).Value2 = Trim$(Mid$(txt, found, sz - found - 1))
그것을로 변경
.Range(d(k)).Value2 = Trim$(Mid$(txt, found, sz - found - 2))
내가 의견에서 제안한 것처럼 문제를 해결합니다.
오류 7.8.2018 관련 편집
먼저 파일 감사드립니다. 이는 파일의 필드 사이에 CRLF 쌍이 있다는 의심을 분명히 확인시켜 줍니다. 또한 함수로 추출한 데이터의 길이가 Mid$()1이 아닌 2로 줄어들어야 함을 확인합니다.
이전 수정 시 겪었던 오류를 재현할 수 없었는데, 실제로 마지막 필드("날짜")에 여전히 오류가 하나 있습니다. 아마도 귀하의 환경에서는 오류가 발생했지만 내 환경에는 없지만 연도가 201로 잘못 표시되었을 수 있습니다.
마지막 필드의 오류는 sz데이터 추출에서 수행한 이전 변경 사항( sz - found - 1으로 변경 sz - found - 2)을 보상하기 위해 변수가 커져야 한다는 것입니다.
그러니 바꿔라
Else sz = txtLen + 2
에게
Else sz = txtLen + 3
물론 이는 파일의 마지막 필드("날짜")를 읽을 때 오류가 발생하는 경우에만 도움이 됩니다. 이것이 도움이 되지 않으면 디버그하여 읽고 있는 필드와 변수에 사용되는 값 k, 실패할 때의 값 found을 알려주십시오 . sz또한 표시될 수 있는 팝업 메시지에 대해서도 알려주세요.