|
楼主 |
发表于 2020-1-14 21:23
|
显示全部楼层
本帖最后由 blue_li 于 2020-1-16 21:35 编辑
我是想实现无限的循环嵌套,我的代码只实现了一层的循环。- Option Explicit
- '进度条函数
- Function GetProgress(curValue, maxValue)
- Dim i As Single, j As Integer, s As String, m As Single, n As Single
- i = maxValue / 20
- j = curValue / i
-
- For m = 1 To j
- s = s & "■"
- Next m
- For n = 1 To 20 - j
- s = s & "□"
- Next n
- GetProgress = s & FormatNumber(curValue / maxValue * 100, 2) & "%"
- End Function
- '创建txt文件
- Sub CreateCim()
-
- Dim c_row, c_column, maxrow, Maxcolumn, c_start, c_end, c_lnnbr, option_column, option_colend As Long
- Dim strCell() As String
- Dim option_arr() As String
- Dim strString, sLineEnd As String
- Dim CreateText As Object
- Dim SelectSheet As Object
- Dim WorkbookName As String
- Dim WorkSheetName As String
- Dim CurrValue As String
- Dim DataArr() '用于存放单元格数值,提升效率
-
-
- Set SelectSheet = ActiveWorkbook.ActiveSheet
- WorkbookName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)
- WorkSheetName = ActiveSheet.Name
- Set CreateText = CreateObject("ADODB.Stream")
-
- On Error GoTo ErrorMessage
- c_row = 1
- Maxcolumn = 0
- Do While SelectSheet.Cells(6, c_row) <> ""
- Maxcolumn = Maxcolumn + 1
- c_row = c_row + 1
- Loop
-
- ReDim strCell(Maxcolumn) As String
-
- If LCase(Panel.LineFeed.Text) = "chui" Then
- sLineEnd = vbLf
- ElseIf LCase(Panel.LineFeed.Text) = "gui" Then
- sLineEnd = vbCrLf
- End If
-
-
- c_start = 0
- c_lnnbr = 0
- c_end = Maxcolumn + 1
-
- For c_column = 1 To Maxcolumn
- If SelectSheet.Cells(7, c_column) = "loop_start" Then
- c_start = c_column
- c_lnnbr = SelectSheet.Cells(8, c_column)
- End If
- If SelectSheet.Cells(7, c_column) = "loop_end" Then
- c_end = c_column
- End If
-
- Next c_column
-
- c_row = CLng(Panel.StartLine.Text)
- maxrow = CLng(Panel.EndLine.Text)
-
-
- '将单元格数据赋于DataArr二维数组
- ReDim DataArr(1 To maxrow + 1, 1 To Maxcolumn + 1)
- DataArr = SelectSheet.Cells(1, 1).Resize(maxrow + 1, Maxcolumn + 1).Value
-
-
- With CreateText
- .Type = 2
- .Charset = Panel.CodePage.Text
- .Open
-
- For c_row = c_row To maxrow
-
- If c_lnnbr = 0 Then
- strString = "@@batchload " & Panel.QadProgram.Text & sLineEnd
- ElseIf LCase(DataArr(c_row, c_lnnbr)) <> LCase(DataArr(c_row - 1, c_lnnbr)) Then
- strString = "@@batchload " & Panel.QadProgram.Text & sLineEnd
- End If
-
- For c_column = 1 To Maxcolumn
-
- '根据内容判断是否输出“内容判断开始”与“内容判断结束”之间的数据
- option_start:
- If LCase(DataArr(7, c_column)) = "option_start" Then
-
- option_column = c_column
- If DataArr(8, c_column) <> "" Then
- option_column = DataArr(8, c_column)
- End If
- option_arr = Split(LCase(DataArr(10, c_column)), ",")
-
-
- '如果在第10行没有找到该值,则执行跳过。
- If UBound(VBA.Filter(option_arr, LCase(DataArr(c_row, option_column)))) < 0 Or LCase(DataArr(c_row, option_column)) = "" Then
-
- For option_colend = c_column To Maxcolumn
- If LCase(DataArr(7, option_colend)) = "option_end" Then
- c_column = option_colend
- Exit For
- End If
- Next option_colend
-
- End If
- End If
-
- If LCase(DataArr(7, c_column)) = "option_end" Or LCase(DataArr(7, c_column)) = "option_start" Then
- If LCase(DataArr(7, c_column + 1)) = "option_start" Then
- c_column = c_column + 1
- GoTo option_start
- End If
- c_column = c_column + 1
- End If
-
-
- '判断是否输出".","-"或单元格内容
- If Trim(Replace(Replace(DataArr(10, c_column), Chr(10), ""), Chr(13), "")) = "." Then
- CurrValue = "."
- ElseIf Trim(Replace(Replace(DataArr(c_row, c_column), Chr(10), ""), Chr(13), "")) = "" Or Trim(Replace(Replace(DataArr(c_row, c_column), Chr(10), ""), Chr(13), "")) = "-" Then
- CurrValue = "-"
- Else:
- CurrValue = Replace(Replace(DataArr(c_row, c_column), Chr(10), ""), Chr(13), "")
- End If
-
- '清除首尾空格
- If Panel.trimoption.Value = True And CurrValue <> "." And CurrValue <> "-" And CurrValue <> """""" Then
- CurrValue = Trim(CurrValue)
- End If
-
- '替换"字符为指定字符
- If Panel.Suboption.Value = True And CurrValue <> "." And CurrValue <> "-" And CurrValue <> """""" Then
- CurrValue = Replace(CurrValue, Chr(34), Panel.subtext.Value)
- End If
-
- '字符前后添加双引号
- If DataArr(10, c_column) = "chr" And CurrValue <> "." And CurrValue <> "-" And CurrValue <> """""" Then
- CurrValue = Chr(34) & CurrValue & Chr(34)
- End If
-
- '转为日期为指定格式
- If DataArr(10, c_column) = "dat" And CurrValue <> "." And CurrValue <> "-" And CurrValue <> """""" And CurrValue <> "?" Then
- CurrValue = convDate2String(CDate(CurrValue))
- End If
- '空值指定为""显示
- If CurrValue = "-" And Panel.Reoption1.Value = True And DataArr(10, c_column) = "chr" Then
- CurrValue = Chr(34) & Chr(34)
- End If
-
- strCell(c_column) = CurrValue
-
- '如果该列存在"enterline"则输出一个回车键
- If DataArr(11, c_column) = "enterline" Then
- strCell(c_column) = strCell(c_column) & sLineEnd
- Else
- strCell(c_column) = strCell(c_column) & Chr(9)
- End If
-
- If c_lnnbr = 0 Then
- ElseIf c_column < c_start And LCase(DataArr(c_row, c_lnnbr)) <> LCase(DataArr(c_row - 1, c_lnnbr)) Then
- strString = strString & strCell(c_column)
- End If
-
- If c_column > c_start And c_column < c_end Then
- strString = strString & strCell(c_column)
- End If
- If c_lnnbr = 0 Then
- ElseIf c_column > c_end And LCase(DataArr(c_row, c_lnnbr)) <> LCase(DataArr(c_row + 1, c_lnnbr)) Then
- strString = strString & strCell(c_column)
- ElseIf c_column > c_end And c_row >= maxrow And LCase(DataArr(c_row, c_lnnbr)) = LCase(DataArr(c_row + 1, c_lnnbr)) Then
- strString = strString & strCell(c_column)
- End If
- Next c_column
-
- If c_lnnbr = 0 Then
- strString = strString & "@@end" & sLineEnd
- .WriteText strString
- ElseIf (LCase(DataArr(c_row, c_lnnbr)) <> LCase(DataArr(c_row + 1, c_lnnbr)) Or c_row >= maxrow) Then
- strString = strString & "@@end" & sLineEnd
- .WriteText strString
- End If
-
- 'Application.StatusBar显示进度,但严重影响性能,弃用
- 'Application.StatusBar = c_row - Panel.StartLine.Text & " of the processing record! "
-
- 'Panel.Messagetext.Caption = "Please wait! [ " & c_row - Panel.StartLine.Text & " ] of the processing record!" & vbCrLf & "Selected [ " & WorkbookName & " ][ " & WorkSheetName & " ]"
- Panel.progress.Caption = GetProgress(c_row - Panel.StartLine.Text + 1, maxrow - Panel.StartLine.Text + 1)
- 'Application.StatusBar = GetProgress(c_row - Panel.StartLine.Text + 1, maxrow - Panel.StartLine.Text + 1)
- '响应取消,返回界面窗体
- If Panel.CreateFile.Enabled = True Then
- Panel.Messagetext.Caption = "Running process has been cancelled."
- Exit Sub
- End If
-
- DoEvents
-
- Next c_row
-
- .SaveToFile Panel.Pathtext.Text & Panel.Nametext.Text, 2
-
- End With
-
- Set CreateText = Nothing
- Set SelectSheet = Nothing
- Panel.Messagetext.ForeColor = &H0&
- Panel.Messagetext.Caption = "Created successfully! Save in " + Panel.Pathtext.Text & Panel.Nametext.Text & " ."
- Exit Sub
- ErrorMessage:
- Panel.Messagetext.ForeColor = &HFF&
- Panel.Messagetext.Caption = "Created failed!Please check the data in Column " & c_column & " Row " & c_row & " ."
- SelectSheet.Cells(c_row, c_column).Select
- Set CreateText = Nothing
- Set SelectSheet = Nothing
- Call LoadPane.InterfaceEnabledToTrue
-
- End Sub
复制代码
|
|