1 year ago

#240296

test-img

AJT56

Merging of two subroutines together

I have two modules running on my VBA excel file. I am wondering how I can merge the two modules together so I don't need to run two separate modules?

The first module extracts data from every .vcf file in a folder and inputs it into each excel cell on the first column A

Option Explicit
Sub getfiledata()
    
    Dim sPath As String
    Dim iRow As Long
    Dim strString  As String
    
    Dim fso As FileSystemObject
    Dim xFile As File
    Dim xFolder As Folder
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Set xFolder = fso.GetFolder("K:\VCF to QR Folder")
    
    iRow = 2 ' Row to start inserting data
    
    For Each xFile In xFolder.Files
        
        If InStr(1, xFile.Name, ".vcf") <> 0 Then
        
            Dim lFile As Long
            Dim szLine As String
            
            lFile = FreeFile()
            
            Open xFile.Path For Input As lFile
            
            strString = ""
            While Not EOF(lFile)
            
                Line Input #lFile, szLine
            
                ' Concatenete lines from text file
                strString = strString & szLine & vbCrLf
                
            Wend
            
            ' Add to cell
            Cells(iRow, 1).Value = strString
            
            iRow = iRow + 1
            
            ' Close the file
            Close lFile
            
            Application.ScreenUpdating = True
                        
        End If
        
    Next ' End of LOOP
    
    MsgBox "Completed!"
End Sub


And the second module, gets the file name of each of those .vcf files and inputs them in the same order in column B.

Sub getfilenames()
    FName = Application.GetOpenFilename(MultiSelect:=True)

    i = 2
    For n = LBound(FName) To UBound(FName)
        FnameInLoop = Right(FName(n), Len(FName(n)) - InStrRev(FName(n), _
        Application.PathSeparator, , 1))
        Cells(i, 2).Value = FnameInLoop
        i = i + 1
    Next n
End Sub

Is there a way I can have one module just run these two 'actions' at once?

Thanks in advance!

excel

vba

vcf-vcard

0 Answers

Your Answer

Accepted video resources