（希望格式沒問題。我是在手機上做的，所以很難說。）
因此，我正在嘗試創建一些 VBA 來自動清理從現場工作人員那裡獲得的資料。下面的所有內容都工作完美，但我試圖添加它以使其在最後導出一個 .txt和將檔案的啟用巨集的工作簿儲存到與匯入原始檔案的路徑相同的路徑 ( \\Atlas\Projects\[fileName]\Survey\In\)。問題是，無論我如何嘗試更改匯出和儲存的程式碼，我都會不斷收到「檔案路徑不存在」錯誤或物件錯誤。我不明白為什麼，我想也許這裡有人可以提供一些見解。預先感謝您的任何建議或指導！
匯出/儲存我嘗試新增的程式碼
注意：當我添加它時，我嘗試將 Dims 保留在原來的位置，並嘗試將它們與功能代碼中的其餘 Dims 一起向上移動。
' Export CleanDataTBL to a comma-delimited .txt file
Dim exportFileName As String
Dim exportFilePath As String
exportFileName = fileName & "-AllPoint-" & Format(Date, "YYYYMMDD") & "-DeDuped.txt"
exportFilePath = "\\Atlas\Projects\" & fileName & "\Survey\In\" & exportFileName
tbl.DataBodyRange.Copy
Workbooks.Add(1).Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs exportFilePath, FileFormat:=xlText, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close False
' Save macro-enabled workbook with the same name format but with "-Workbook" suffix
Dim workbookFileName As String
Dim workbookFilePath As String
workbookFileName = fileName & "-AllPoint-" & Format(Date, "YYYYMMDD") & "-DeDuped-Workbook.xlsm"
workbookFilePath = "\\Atlas\Projects\" & fileName & "\Survey\In\" & workbookFileName
ThisWorkbook.SaveAs workbookFilePath, FileFormat:=52 ' xlOpenXMLWorkbookMacroEnabled
我正在嘗試添加的功能代碼
Sub RawFDCleanup()
Dim RawDataWS As Worksheet
Dim fileName As String
Dim filePath As String
Dim fileContent As String
Dim fileLine As Variant
Dim lastRow As Long
Dim tbl As ListObject
Dim rng As Range
Dim i As Long
Dim j As Long
Dim k As Long
Dim pointColRaw As Range
Dim cellRaw As Range
Dim duplicateRaw As Boolean
Dim CleanDataWS As Worksheet
Dim cleanDataTbl As ListObject
Dim rowCount As Long
Dim colCount As Long
Dim l As Long
Dim m As Long
Dim deleteRow As Boolean
Dim pointColClean As Range
Dim cellClean As Range
Dim duplicateClean As Boolean
' Create RawDataWS worksheet
Set RawDataWS = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
RawDataWS.Name = "RawDataWS"
' Add headers
With RawDataWS
.Cells(1, 1).Value = "Point #"
.Cells(1, 2).Value = "Northing"
.Cells(1, 3).Value = "Easting"
.Cells(1, 4).Value = "Elevation"
.Cells(1, 5).Value = "Description"
End With
' Prompt user for file name
fileName = InputBox("Enter a job # to search for on \\Atlas\Projects\:")
' Construct file path
filePath = "\\Atlas\Projects\" & fileName & "\Survey\In\"
' Check if directory exists
If Dir(filePath, vbDirectory) <> "" Then
' Loop through all .txt files in the directory
fileName = Dir(filePath & "*.txt")
' Initialize lastRow to the first available row
lastRow = 2 ' Start from row 2 to skip headers
Do While fileName <> ""
' Open file and read content
Open filePath & fileName For Input As #1
Do Until EOF(1)
Line Input #1, fileLine
fileContent = fileContent & fileLine & vbCrLf
Loop
Close #1
' Split content by newline and paste into worksheet
Dim lines() As String
lines = Split(fileContent, vbCrLf)
For i = 0 To UBound(lines)
Dim rowData() As String
rowData = Split(lines(i), ",")
' Convert text-formatted values to numbers
RawDataWS.Cells(lastRow, 1).Resize(1, 5).Value = rowData
' Convert text to numbers in the Point #, Northing, Easting, and Elevation columns
RawDataWS.Cells(lastRow, 1).Resize(1, 4).Value = RawDataWS.Cells(lastRow, 1).Resize(1, 4).Value
lastRow = lastRow + 1
Next i
' Reset file content for next file
fileContent = ""
' Move to the next file
fileName = Dir
Loop
Else
MsgBox "Directory not found."
End If
' After importing all data, format as a table
If lastRow > 2 Then
' Convert the data to a table
Set tbl = RawDataWS.ListObjects.Add(xlSrcRange, RawDataWS.Range("A1").Resize(lastRow - 1, 5), , xlYes)
tbl.Name = "RawDataTBL"
tbl.TableStyle = "TableStyleMedium4"
' Format Northing, Easting, and Elevation columns to three decimal places
With tbl.ListColumns("Northing").DataBodyRange
.NumberFormat = "0.000"
End With
With tbl.ListColumns("Easting").DataBodyRange
.NumberFormat = "0.000"
End With
With tbl.ListColumns("Elevation").DataBodyRange
.NumberFormat = "0.000"
End With
' Set the width of columns A to D to specified values
RawDataWS.Columns("A").ColumnWidth = 8
RawDataWS.Columns("B:C").ColumnWidth = 14
RawDataWS.Columns("D").ColumnWidth = 10
' Autofit column E to the width of its content
RawDataWS.Columns("E").AutoFit
' Align text of Columns B-D to center
RawDataWS.Columns("B:D").HorizontalAlignment = xlCenter
' Turn off filter button
tbl.ShowAutoFilter = False
' Remove blank rows from the table
Set rng = tbl.DataBodyRange
For i = rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Rows(i)) = 0 Then
rng.Rows(i).Delete
End If
Next i
End If
' Check for duplicates in RawDataTBL
Set RawDataWS = ThisWorkbook.Worksheets("RawDataWS")
Set tbl = RawDataWS.ListObjects("RawDataTBL")
Set pointColRaw = tbl.ListColumns("Point #").DataBodyRange
For Each cellRaw In pointColRaw
If WorksheetFunction.CountIf(pointColRaw, cellRaw.Value) > 1 Then
If WorksheetFunction.CountIf(tbl.ListColumns("Point #").DataBodyRange, cellRaw.Value) = 1 Then
tbl.Range.Rows(cellRaw.Row).Delete
Else
tbl.Range.Rows(cellRaw.Row).Interior.Color = RGB(255, 192, 192)
tbl.Range.Rows(cellRaw.Row).Font.Color = RGB(192, 0, 0)
End If
duplicateRaw = True
End If
Next cellRaw
If Not duplicateRaw Then
MsgBox "No duplicate Point #’s were found in the field data.", vbInformation
End If
' Duplicate RawDataWS worksheet
RawDataWS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
' Rename the duplicated worksheet
ActiveSheet.Name = "CleanDataWS"
' Explicitly set CleanDataWS worksheet
Set CleanDataWS = ThisWorkbook.Worksheets("CleanDataWS")
' Rename the table on CleanDataWS worksheet
On Error Resume Next
Set cleanDataTbl = CleanDataWS.ListObjects("RawDataTBL3")
On Error GoTo 0
If Not cleanDataTbl Is Nothing Then
cleanDataTbl.Name = "CleanDataTBL"
Else
MsgBox "No table found on CleanDataWS worksheet.", vbExclamation
End If
' Change table style of CleanDataTBL
If Not cleanDataTbl Is Nothing Then
cleanDataTbl.TableStyle = "TableStyleMedium3"
Else
MsgBox "No table found on CleanDataWS worksheet.", vbExclamation
End If
' Analyze and remove duplicate rows in CleanDataTBL
If Not cleanDataTbl Is Nothing Then
rowCount = cleanDataTbl.ListRows.Count
colCount = cleanDataTbl.ListColumns.Count
' Iterate through each row
For l = rowCount To 2 Step -1
deleteRow = False
' Iterate through each previous row to compare
For m = l - 1 To 1 Step -1
' Compare each cell of the current row with the previous row
Dim rowMatch As Boolean
rowMatch = True
For n = 1 To colCount
If cleanDataTbl.DataBodyRange.Cells(l, n).Value <> cleanDataTbl.DataBodyRange.Cells(m, n).Value Then
rowMatch = False
Exit For ' Exit loop if any cell is different
End If
Next n
If rowMatch Then
deleteRow = True
Exit For ' Exit loop if duplicate row found
End If
Next m
' If duplicate row found, delete the current row
If deleteRow Then
cleanDataTbl.ListRows(l).Delete
End If
Next l
Else
MsgBox "No table found on CleanDataWS worksheet.", vbExclamation
End If
' Analyze Point # column for remaining duplicates and apply formatting
Set CleanDataWS = ThisWorkbook.Worksheets("CleanDataWS")
Set tbl = CleanDataWS.ListObjects("CleanDataTBL")
Set pointColClean = tbl.ListColumns("Point #").DataBodyRange
For Each cellClean In pointColClean
If WorksheetFunction.CountIf(pointColClean, cellClean.Value) > 1 Then
tbl.Range.Rows(cellClean.Row).Interior.Color = RGB(255, 192, 192)
tbl.Range.Rows(cellClean.Row).Font.Color = RGB(192, 0, 0)
duplicateClean = True
Else
tbl.Range.Rows(cellClean.Row).Interior.ColorIndex = xlNone
tbl.Range.Rows(cellClean.Row).Font.Color = RGB(0, 0, 0)
End If
Next cellClean
If Not duplicateClean Then
MsgBox "No duplicate Point #’s were found in the field data.", vbInformation
End If
End Sub