有人能帮我理解我的VBA代码中的错误吗？
基本上，我有一个纯文本文件，我想用它作为产生CSV文件的源。
文本文件的格式如下：
VBA代码应使用源文件应用逻辑并将文件另存为csv。
逻辑是：
1)航班号是以SF开头的代码，后面是编号。示例：SF 1234
2)航班日期计算如下：如果字符串包含19 APR 23 19 APR 23，则意味着它只是一个日期19/04/2023。如果字符串包含19 APR 23 21 APR 23，那么对于相同的航班号，应该有三行，因为它们是三天。
总结如下：
| 航班号|航班日期|模板|小部件|
| --------------|--------------|--------------|--------------|
| SF1234|2019 - 04 - 19|||
| SF1234|2019 - 04 - 19|||
| SF1234|2023年4月20日|||
| SF1234|2023年4月21日|||
我尝试了以下代码，但由于某种原因，生成了一个只有“Flight Number，Flight Date，Template，Widget”的csv，没有数据：
Option Explicit
Sub ImportSSMData()
Dim SSMFileName As Variant
Dim SSMFile As Integer
Dim SSMContent As String
Dim SSMData() As String
Dim FlightNum As String
Dim FlightDates As String
Dim StartDate As Date
Dim EndDate As Date
Dim CurrentDate As Date
Dim OutputWorkbook As Workbook
Dim OutputWorksheet As Worksheet
Dim OutputRow As Long
Dim i As Long
SSMFileName = Application.GetOpenFilename(FileFilter:="All Files (*.*), *.*", Title:="Select the SSM file")
If SSMFileName = False Then
Exit Sub
End If
SSMFile = FreeFile
Open SSMFileName For Input As SSMFile
SSMContent = Input(LOF(SSMFile), SSMFile)
Close SSMFile
SSMData = Split(SSMContent, vbCrLf)
Set OutputWorkbook = Workbooks.Add
Set OutputWorksheet = OutputWorkbook.Worksheets(1)
OutputRow = 1
OutputWorksheet.Cells(OutputRow, 1).Value = "Flight Number"
OutputWorksheet.Cells(OutputRow, 2).Value = "Flight Date"
OutputWorksheet.Cells(OutputRow, 3).Value = "Template"
OutputWorksheet.Cells(OutputRow, 4).Value = "Widget"
For i = LBound(SSMData) To UBound(SSMData)
If Left(SSMData(i), 2) = "SF" Then
FlightNum = SSMData(i)
ElseIf Len(SSMData(i)) >= 7 And IsDate(Left(SSMData(i), 7)) Then
FlightDates = SSMData(i)
If Len(FlightDates) >= 14 Then
StartDate = DateSerial(Right(FlightDates, 2), MonthNameToNumber(Mid(FlightDates, 5, 3)), Left(FlightDates, 2))
EndDate = DateSerial(Right(FlightDates, 2), MonthNameToNumber(Mid(FlightDates, 12, 3)), Mid(FlightDates, 10, 2))
For CurrentDate = StartDate To EndDate
OutputRow = OutputRow + 1
OutputWorksheet.Cells(OutputRow, 1).Value = FlightNum
OutputWorksheet.Cells(OutputRow, 2).Value = Format(CurrentDate, "dd/mm/yyyy")
Next CurrentDate
ElseIf Len(FlightDates) >= 7 Then
StartDate = DateSerial(Right(FlightDates, 2), MonthNameToNumber(Mid(FlightDates, 5, 3)), Left(FlightDates, 2))
OutputRow = OutputRow + 1
OutputWorksheet.Cells(OutputRow, 1).Value = FlightNum
OutputWorksheet.Cells(OutputRow, 2).Value = Format(StartDate, "dd/mm/yyyy")
End If
End If
Next i
Dim CSVFileName As Variant
CSVFileName = Application.GetSaveAsFilename( _
InitialFileName:=Replace(SSMFileName, ".txt", ".csv"), _
FileFilter:="CSV Files (*.csv), *.csv", _
Title:="Save the CSV file")
If CSVFileName <> False Then
OutputWorkbook.SaveAs fileName:=CSVFileName, FileFormat:=xlCSV, CreateBackup:=False
OutputWorkbook.Close SaveChanges:=False
Else
MsgBox "No file name was provided. The file was not saved.", vbExclamation, "Save Canceled"
End If
End Sub
Function MonthNameToNumber(MonthName As String) As Integer
Dim MonthNumber As Integer
Dim MonthNames As Variant
MonthNames = Array("JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC")
For MonthNumber = LBound(MonthNames) To UBound(MonthNames)
If UCase(MonthName) = MonthNames(MonthNumber) Then
Exit For
End If
Next MonthNumber
MonthNameToNumber = MonthNumber + 1
End Function有什么建议吗？
2条答案
按热度按时间7gcisfzg1#
你的代码中有几个问题：
压缩日期
您不能将
IsDate用于语法为19APR23的日期。你可以使用类似这样的东西：字符串提取
Right有一个重复出现的错误，它应该是Mid。Right(FlightDates,2)只有当你真的想要那个字符串的最后两个字符时才起作用。在这个问题的大多数情况下，你在最后没有年份。使用Mid。此外，还有一些与此相关的逻辑错误。我在此基础上对你的循环做了一些修改。不确定它是否完全正确，但它会把你送往正确的方向，我希望。wqnecbli2#
对于那些正在寻找类似解决方案的人，我已经修复了现在工作的代码：