Adding data to the end of existing data.

[SteynBS]

Hi Guys,

So I use VBA to add the file names in a folder to excel. Everything works fine, but now I added a folder with other files. I now need the other folder file names to be added to the end of my existing data.

This is the VBA I currently use.

Sub ListAllFileNames1()

Dim strTargetFolder As String, strFileName As String, nCountItem As Integer

' Initialization
nCountItem = 1
strTargetFolder = "Z:\TC Reporting & Certification\Certificates Consolidated\House Brands Foods" & "\"
strFileName = Dir(strTargetFolder, vbDirectory)

' Get the file name
Do While strFileName <> ""
If strFileName <> "." And strFileName <> ".." Then
Cells(nCountItem + 3, 2) = strFileName
nCountItem = nCountItem + 3
End If
strFileName = Dir
Loop

End Sub

this is the other folder directory "Z:\TC Reporting & Certification\Certificates Consolidated\Appliances Medical Devices Cosmetics" & "\"

So basically I need the file names to be combined. The one following after the other.

 

[Alex Blakenburg]

I haven't been able to test this but give this a try:

Sub ListAllFileNames1()

Dim strTargetFolder As String, strFileName As String, nCountItem As Integer
Dim i As Long, arrFolders As Variant

' Initialization
nCountItem = 1

arrFolders = Array("Z:\TC Reporting & Certification\Certificates Consolidated\House Brands Foods" & "\", _
                    "Z:\TC Reporting & Certification\Certificates Consolidated\Appliances Medical Devices Cosmetics" & "\")

For i = LBound(arrFolders) To UBound(arrFolders)
    strTargetFolder = arrFolders(i)
    strFileName = Dir(strTargetFolder, vbDirectory)
    
    ' Get the file name
    Do While strFileName <> ""
        If strFileName <> "." And strFileName <> ".." Then
            Cells(nCountItem + 3, 2) = strFileName
            nCountItem = nCountItem + 3
        End If
        strFileName = Dir
    Loop

Next i

End Sub

 

[SteynBS]

I haven't been able to test this but give this a try:

Sub ListAllFileNames1()

Dim strTargetFolder As String, strFileName As String, nCountItem As Integer
Dim i As Long, arrFolders As Variant

' Initialization
nCountItem = 1

arrFolders = Array("Z:\TC Reporting & Certification\Certificates Consolidated\House Brands Foods" & "\", _
                    "Z:\TC Reporting & Certification\Certificates Consolidated\Appliances Medical Devices Cosmetics" & "\")

For i = LBound(arrFolders) To UBound(arrFolders)
    strTargetFolder = arrFolders(i)
    strFileName = Dir(strTargetFolder, vbDirectory)
   
    ' Get the file name
    Do While strFileName <> ""
        If strFileName <> "." And strFileName <> ".." Then
            Cells(nCountItem + 3, 2) = strFileName
            nCountItem = nCountItem + 3
        End If
        strFileName = Dir
    Loop

Next i

End Sub

I haven't been able to test this but give this a try:

Sub ListAllFileNames1()

Dim strTargetFolder As String, strFileName As String, nCountItem As Integer
Dim i As Long, arrFolders As Variant

' Initialization
nCountItem = 1

arrFolders = Array("Z:\TC Reporting & Certification\Certificates Consolidated\House Brands Foods" & "\", _
                    "Z:\TC Reporting & Certification\Certificates Consolidated\Appliances Medical Devices Cosmetics" & "\")

For i = LBound(arrFolders) To UBound(arrFolders)
    strTargetFolder = arrFolders(i)
    strFileName = Dir(strTargetFolder, vbDirectory)
   
    ' Get the file name
    Do While strFileName <> ""
        If strFileName <> "." And strFileName <> ".." Then
            Cells(nCountItem + 3, 2) = strFileName
            nCountItem = nCountItem + 3
        End If
        strFileName = Dir
    Loop

Next i

End Sub

@Alex Blakenburg, you are a genius. It works perfectly, thank you​

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.

 

[SteynBS]

Thanks for letting me know. Glad I could help.

@Alex Blakenburg just for interest sake. in those folders, there are not only file names, there are other folders in it as well. So this formula copies all the file names in that folder including the folder names. Is there any way to avoid that or should I just AutoFilter the folder names and delete it afterward?

 

[Alex Blakenburg]

Can you try it without the vbDirectory first.

 

[SteynBS]

Yes i

Can you try it without the vbDirectory first.

Yes, indeed it works. Thank you so much. Can't thank you enough.

I have posted this question, not received any feedback as yet. But, what do you know about importing/copying data from a text document pasting into excel, but its done with multiple text documents with the same name all the ext documents data should be pasted in excel but it should not override the existing data. it should keep adding to the data. If you can assist me, I will send you more info.

 

[Alex Blakenburg]

Send me the link. I can take a look. No promises though

 

[SteynBS]

Send me the link. I can take a look. No promises though

Importing data from text file, but adding data at the end of the existing data in excel.

Hi Guys, So I am using this to simplify our stock takes. Our scanners dock and download the counts in a text document. The program only writes to one file named CIPHER.OUT.txt we use multiple scanners. so after every download, it rewrites the .txt file with the last download. So I have to import...

www.mrexcel.com

Sub ImportTextFileToExcel()
Dim textFileNum, rowNum, colNum As Integer
Dim textFileLocation, textDelimiter, textData As String
Dim tArray() As String
Dim sArray() As String
textFileLocation = "C:\Users\200327\Desktop\CIPHER.OUT.txt"
textDelimiter = "|"
textFileNum = FreeFile
Open textFileLocation For Input As textFileNum
textData = Input(LOF(textFileNum), textFileNum)
Close textFileNum
tArray() = Split(textData, vbLf)
For rowNum = LBound(tArray) To UBound(tArray) - 1
If Len(Trim(tArray(rowNum))) <> 0 Then
sArray = Split(tArray(rowNum), textDelimiter)
For colNum = LBound(sArray) To UBound(sArray)
ActiveSheet.Cells(rowNum + 2, colNum + 1) = sArray(colNum)
Next colNum
End If
Next rowNum

End Sub

I need to somehow change (in red) so the data is added at the bottom of the existing data something like this - Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[Alex Blakenburg]

I have responded to the new topic on the new thread. You might want to close this one out, assuming that you have a solution that works for you,