excel VBA编程，想运行宏，出来浏览文件的对话框，选中文件导入，并去掉|分隔符

frankjackie

想通过VBA选择导入一个文件后，去掉|分隔符，以下是我写的代码，但只能运行指定运行一个指定的文件，但是想要的是一个可选择文件：

1. <p>Sub 导入302()
   
2. '
   
3. ' 导入302 Macro
   
4. '
   
5. '关闭屏幕闪动</p><p>Application.ScreenUpdating = False</p><p>
   
6.     Sheets("Sheet3").Select
   
7.    
   
8.     Cells.Select
   
9.     Selection.ClearContents
   
10.    
    
11.     Range("A1").Select
    
12.     With ActiveSheet.QueryTables.Add(Connection:= _
    
13.         "TEXT;C:\Users\Administrator\Desktop\小谭\301,302\BEF302_19200300_01.20150708", _
    
14.         Destination:=Range("$A$1"))
    
15.         .Name = "BEF302_19200300"
    
16.         .FieldNames = True
    
17.         .RowNumbers = False
    
18.         .FillAdjacentFormulas = False
    
19.         .PreserveFormatting = True
    
20.         .RefreshOnFileOpen = False
    
21.         .RefreshStyle = xlInsertDeleteCells
    
22.         .SavePassword = False
    
23.         .SaveData = True
    
24.         .AdjustColumnWidth = True
    
25.         .RefreshPeriod = 0
    
26.         .TextFilePromptOnRefresh = False
    
27.         .TextFilePlatform = 936
    
28.         .TextFileStartRow = 1
    
29.         .TextFileParseType = xlDelimited
    
30.         .TextFileTextQualifier = xlTextQualifierDoubleQuote
    
31.         .TextFileConsecutiveDelimiter = False
    
32.         .TextFileTabDelimiter = True
    
33.         .TextFileSemicolonDelimiter = False
    
34.         .TextFileCommaDelimiter = False
    
35.         .TextFileSpaceDelimiter = False
    
36.         .TextFileOtherDelimiter = "|"
    
37.         .TextFileColumnDataTypes = Array(9, 9, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
    
38.         1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    
39.         .TextFileTrailingMinusNumbers = True
    
40.         .Refresh BackgroundQuery:=False
    
41.     End With
    
42.     Rows("1:1").Select
    
43.     Selection.Delete Shift:=xlUp
    
44.     Sheets("Sheet1").Select</p><p>End Sub</p><p>
    
45. </p><p> </p>

复制代码

phsu

(1)將Sub 导入302()改成Sub 导入302(FileName As String)
(2)將
"TEXT;C:\Users\Administrator\Desktop\小谭\301,302\BEF302_19200300_01.20150708", _
改成
"TEXT;C:\Users\Administrator\Desktop\小谭\301,302\" & FileName, _
(3)寫一個程式
Sub Main()
    Dim strNowPath As String
    Dim strFileName As String
    Dim strFileExt As String
    Dim n As Integer
    Dim Arr() As String   
    strNowPath = "C:\Users\Administrator\Desktop\小谭\301,302"
    strFileExt = "*.*"
    n = 0
    If Right(strNowPath, 1) = "\" Then
        strFileName = Dir(strNowPath & strFileExt, vbDirectory)
    Else
        strFileName = Dir(strNowPath & "\" & strFileExt, vbDirectory)
    End If
    While strFileName <> ""
        If strFileName <> "." And strFileName <> ".." Then
            ReDim Preserve Arr(n)
            Arr(n) = strFileName
            Call 导入302(Arr(n))
            n = n + 1
        End If
        strFileName = Dir()
    Wend
End Sub