时间戳

首页 > 解决方案 > Excel VBA 中不必要的值连接和类型错误

excel - Excel VBA 中不必要的值连接和类型错误

问题描述

我的 VBA 代码从特定软件输出（Carlson Survey 软件）中获取一个 .txt 文件并进行一些计算，然后将其转换为 .CSV 文件。我特别遇到了计算组件的问题，其中我的文本文件的一列（使用逗号分隔符带入 excel）没有进行我告诉它的计算，并且似乎正在连接自身（删除小数点后的所有内容） . 我的假设是，因为我将这些值放入一个设置为字符串的数组（必须设置为字符串，否则我会遇到类型错误），这会导致小数点后的连接。我不知道为什么计算似乎没有运行，因为程序似乎执行得很好。

以及用于快速参考的 VBA 脚本（有问题的特定部分是“进行数据转换”部分：

Private Sub Workbook_Open()
Sheets("Sheet1").Cells.ClearContents
'---------------------------------------------------------------------------------------
'Choose and open the .TXT file for conversion
Dim answer As Integer
answer = MsgBox("Do you want to process a .TXT file for use in InfoSWMM?", vbYesNo + vbQuestion, "Select .TXT File")
If answer = vbNo Then
    Exit Sub
End If
Dim Ret
Ret = Application.GetOpenFilename("Text Files (*.txt),*.txt")
If Ret <> False Then
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & Ret, Destination:=Range("$A$1") _
        )
        .Name = "Sample"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End If
'---------------------------------------------------------------------------------------
'Do data conversion, SECTION NEEDS UPDATING LACKING FEATURES, BUGS
Dim row As Integer
Dim col As Integer
Dim i As Integer
Dim tester(3) As String 'Bug[1] related, type error (see below).  String type fixes type error, but causes undesired concatenation
Dim col_test As Integer
Dim rim As Integer

For row = 1 To ActiveSheet.UsedRange.Rows.Count
    If IsEmpty(ActiveSheet.Cells(row, 1).Value) = True Then
        Exit For
    End If
    'Change these values in case feature code library is changed in Carlson, also need to add extra fields
    If ActiveSheet.Cells(row, 5).Value = "SD" Or ActiveSheet.Cells(row, 5).Value = "WQ" Then
        col_test = 20
        rim = ActiveSheet.Cells(row, 4).Value
        For i = 0 To 3
            tester(i) = ActiveSheet.Cells(row, col_test).Value 'Bug[1] here, type error if not a String.
            col_test = col_test + 4
        Next i
        ActiveSheet.Cells(row, 37).Value = rim - Application.Max(tester) 'Bug[2] here, not performing calculation.
    End If
Next row
'---------------------------------------------------------------------------------------
'Save converted file as .CSV
MsgBox "Choose the desired save location for the .CSV file."
Dim InitialName As String
Dim PathName As Variant
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
InitialName = "sfm_output"
PathName = Application.GetSaveAsFilename(InitialFileName:=InitialName, fileFilter:="CSV (Comma delimited) (*.csv), *.csv")
ws.Copy
ActiveWorkbook.SaveAs Filename:=PathName, _
    FileFormat:=xlCSV, CreateBackup:=False
MsgBox "Process completed successfully." & vbNewLine & "File saved to:" & vbNewLine & PathName
'---------------------------------------------------------------------------------------
'Close all Workbooks
Application.DisplayAlerts = False
Application.Quit
End Sub

任何帮助是极大的赞赏。谢谢。

标签： excelvba

解决方案

您是否尝试过 CSTRING 或 CINT 函数？

例如：tester(i) = CString(ActiveSheet.Cells(row, col_test).Value)

推荐阅读