在 Excel 使用macro————仙盟创梦IDE

 

 Dim filePath As StringDim fileContent As StringDim lines() As StringDim dataArray() As StringDim lineCount As LongDim maxCols As LongDim i As Long, j As Long' 文件路径filePath = ""' 检查文件是否存在If Dir(filePath) = "" ThenMsgBox "文件不存在: " & filePath, vbExclamationExit SubEnd If' 读取文件内容On Error Resume NextfileContent = ReadFileContent(filePath)If Err.Number <> 0 ThenMsgBox "读取文件时出错: " & Err.Description, vbCriticalExit SubEnd IfOn Error GoTo 0' 按行分割内容lines = Split(fileContent, vbCrLf)lineCount = UBound(lines) + 1' 确定最大列数maxCols = 0For i = 0 To UBound(lines)If Len(Trim(lines(i))) > 0 ThenDim cols() As Stringcols = Split(lines(i), ",")  ' 假设使用逗号分隔，根据实际情况修改If UBound(cols) + 1 > maxCols ThenmaxCols = UBound(cols) + 1End IfEnd IfNext i' 重新定义数组大小ReDim dataArray(1 To lineCount, 1 To maxCols)' 填充数组For i = 0 To UBound(lines)If Len(Trim(lines(i))) > 0 ThenDim cols() As Stringcols = Split(lines(i), ",")  ' 假设使用逗号分隔，根据实际情况修改For j = 0 To UBound(cols)dataArray(i + 1, j + 1) = cols(j)Next jEnd IfNext i' 在新工作表中显示数据（可选）DisplayDataInWorksheet dataArray, lineCount, maxCols' 现在可以使用dataArray数组进行后续处理MsgBox "文件已成功读取并解析为数组!", vbInformation

显示数据表

 

' 在工作表中显示数组数据的辅助函数
Sub DisplayDataInWorksheet(dataArray() As String, rows As Long, cols As Long)Dim ws As Worksheet' 创建新工作表Set ws = ThisWorkbook.Sheets.Addws.Name = "人员权重数据"' 填充数据Dim i As Long, j As LongFor i = 1 To rowsFor j = 1 To colsws.Cells(i, j).Value = dataArray(i, j)Next jNext i' 自动调整列宽ws.Columns.AutoFit
End Sub

读取文件

' 读取文件内容的辅助函数
Function ReadFileContent(filePath As String) As StringDim fileNum As IntegerDim content As StringfileNum = FreeFileOpen filePath For Input As #fileNumcontent = Input$(LOF(fileNum), #fileNum)Close #fileNumReadFileContent = content
End Function

读取文件

unction 仙盟创梦macro_招标系统_读取文件(filePath As String) As String' 方法一：使用FSOfileContent = ReadTextFile_FSO(filePath)If fileContent <> "" ThenMsgBox "文件内容长度: " & Len(fileContent), vbInformationEnd IfDim fileNum As IntegerDim content As StringDim tempStr As StringfileNum = FreeFileOn Error GoTo ErrorHandlerOpen filePath For Input As #fileNum' 安全读取文件内容Do While Not EOF(fileNum)Line Input #fileNum, tempStrcontent = content & tempStr & vbCrLfLoopClose #fileNumReadFileSafely = contentExit FunctionErrorHandler:Close #fileNum  ' 确保关闭文件MsgBox "读取文件时出错: " & Err.Description, vbCriticalReadFileSafely = ""
End Function

文件读取

Function 仙盟创梦macro_招标系统_读取文件2(filePath As String) As StringDim fso As ObjectDim file As Object' 创建FileSystemObjectSet fso = CreateObject("Scripting.FileSystemObject")' 检查文件是否存在If fso.FileExists(filePath) Then' 打开文件并读取全部内容Set file = fso.OpenTextFile(filePath, 1, False) ' 1 = ForReadingReadTextFile_FSO = file.ReadAllfile.CloseElseMsgBox "文件不存在: " & filePath, vbExclamationReadTextFile_FSO = ""End If' 释放对象Set file = NothingSet fso = Nothing仙盟创梦macro_招标系统_读取文件2 = ReadTextFile_FSO
End Function

数据显示

' 在工作表中显示数组数据的辅助函数
Sub DisplayDataInWorksheet(dataArray() As String, rows As Long, cols As Long)Dim ws As Worksheet' 创建新工作表Set ws = ThisWorkbook.Sheets.Addws.Name = "人员权重数据3"' 填充数据Dim i As Long, j As LongFor i = 1 To rowsFor j = 1 To cols'ws.Cells(i, j).Value = dataArray(i, j)ws.Cells(i, j).Value = dataArray(j)Next jNext i' 自动调整列宽ws.Columns.AutoFit
End Sub