Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 524
  • Last Modified:

how to use DIR() twice in a procedure

I have two sub procedures which are inside a loop. They each use the DIR() function. I am getting a problem. It seems that when I write
sName=DIR() at the end of the first procedure it is assigning sName to a file that was found in the second procedure.
Can anyone suggest how to fix this?
I will put the first and second procedures below:

first:
 Sub CSV_Import_Loop()


Dim sh As Worksheet, sPath As String, sName As String
Dim ws As Worksheet
Dim cumFile As String


Set sh = ActiveWorkbook.Sheets("perStockTweets") 'set to current worksheet name
sPath = "C:\Users\Bill\Desktop\testLongTermTest\"
sName = Dir(sPath & "*.csv")
Do While sName <> ""
 'Set cumFile = sPath & sName
 cumFile = sPath & sName
 
  Workbooks.Add Template:= _
        "C:\Users\Bill\AppData\Roaming\Microsoft\Templates\sizesWithFormV2.xltm" 'for each file in the directory, I want to open it in a new instance of the template.
 Set sh = ActiveWorkbook.Sheets("perStockTweets")


  With sh.QueryTables.Add(Connection:="TEXT;" & cumFile, Destination:=sh.Range("A1"))
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
 End With


'The rest comes for the "DO ALL" button on the form (except for the last line which says sName=Dir()
'Dim ws As Worksheet
Set ws = Worksheets("GoogleData")
MsgBox ("need to set ws.Cells(4,2) etc some way in tweetsLoop")
ws.Cells(4, 2) = "Coke"
ws.Cells(3, 2) = Format(Now, "yyyy/mm/dd")
ws.Cells(2, 2) = "2009-07-15"
'If TextBox3.Value = "" Then TextBox3.Text = Format(Now, "yyyy/mm/dd")

Application.Run "generateDatesAndMbFormbPerFileFromPerl"
Application.Run "Macro9"
Application.Run "fromSvetToCleanUP2"

sName = Dir()
 
Loop

 End Sub

Open in new window


second one
Sub generateDatesAndMbFormbPerFileFromPerl()
    
    Dim objFSO      As Object
    Dim MyFolder    As String
    Dim fName       As String
    Dim i           As Long
    Dim Fyle        As Object
    Dim f()
    
    'MyFolder = "C:\Users\Bob\Desktop\Twb"            '<< adjust to suit
    MyFolder = UserForm1.TextBox5.Text
    
    If Right$(MyFolder, 1) <> "\" Then MyFolder = MyFolder & "\"
    
    If Len(Dir(MyFolder, vbDirectory)) Then
        fName = Dir(MyFolder & "*.csv")
        If Len(fName) Then
            
            Set objFSO = CreateObject("scripting.filesystemobject")
            ReDim f(1 To objFSO.getfolder(MyFolder).Files.Count, 1 To 2)
            
            For Each Fyle In objFSO.getfolder(MyFolder).Files
                i = i + 1
                f(i, 1) = Mid(Fyle.Name, 8, 8)
                f(i, 2) = Fyle.Size / 1024
            Next
        End If
        
        If i Then
            Worksheets("mbPerFileFromPerl").Range("a1:b1") = [{"Dates", "Mb"}]
            Worksheets("mbPerFileFromPerl").Range("a2").Resize(i, 2) = f
            With Worksheets("mbPerFileFromPerl").Range("a2").Resize(i)
                .TextToColumns Destination:=.Cells(1), DataType:=1, FieldInfo:=Array(1, 4)
                .NumberFormat = "mm-dd-yyyy"
            End With
        End If
    End If

End Sub

Open in new window

0
willie108
Asked:
willie108
  • 2
1 Solution
 
krishnakrkcCommented:
Hi,

try this one.

Sub CSV_Import_Loop()

    Dim sh As Worksheet, sPath As String, sName As String
    Dim ws As Worksheet
    Dim cumFile As String
    Dim CSVs()  As String, i As Long
    
    Set sh = ActiveWorkbook.Sheets("perStockTweets") 'set to current worksheet name
    sPath = "C:\Users\Bill\Desktop\testLongTermTest\"
    sName = Dir(sPath & "*.csv")
    
    '// Store each CSV file in an array
    Do While sName <> ""
        cumFile = sPath & sName
        i = i + 1
        ReDim Preserve CSVs(1 To i)
        CSVs(i) = cumFile
        sName = Dir()
    Loop
    
    If i Then
        'loop the CSVs array
        For i = 1 To UBound(CSVs)
            cumFile = CSVs(i)
            Workbooks.Add Template:= _
                  "C:\Users\Bill\AppData\Roaming\Microsoft\Templates\sizesWithFormV2.xltm" 'for each file in the directory, I want to open it in a new instance of the template.
            Set sh = ActiveWorkbook.Sheets("perStockTweets")
            
            
            With sh.QueryTables.Add(Connection:="TEXT;" & cumFile, Destination:=sh.Range("A1"))
               .TextFileParseType = xlDelimited
               .TextFileCommaDelimiter = True
               .Refresh
            End With
            
            
            'The rest comes for the "DO ALL" button on the form (except for the last line which says sName=Dir()
            'Dim ws As Worksheet
            Set ws = Worksheets("GoogleData")
            MsgBox ("need to set ws.Cells(4,2) etc some way in tweetsLoop")
            ws.Cells(4, 2) = "Coke"
            ws.Cells(3, 2) = Format(Now, "yyyy/mm/dd")
            ws.Cells(2, 2) = "2009-07-15"
            'If TextBox3.Value = "" Then TextBox3.Text = Format(Now, "yyyy/mm/dd")
            
            Application.Run "generateDatesAndMbFormbPerFileFromPerl"
            Application.Run "Macro9"
            Application.Run "fromSvetToCleanUP2"
        Next
    End If

End Sub

Open in new window


Kris
0
 
willie108Author Commented:
Great. Thanks!
0
 
willie108Author Commented:
Great Kris!<br />Thanks.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now