Using Excel VBA to read/write to another Excel Workbook

[amtagrwl]

Hi,

I have been working on a macro to read the file names and last modified dates for a bunch of files and organize it into a worksheet. Now, I need to access and read/write data from another excel workbook. I will specifically be reading rows of data and save it in arrays in VBA and then edit them and put the data back in the other excel workbook. Editing it might include adding more rows of data or deleting rows and also editing some fields. The no of cells I'll be copying will probably be to the order of 8000R X 20C, so efficiency/speed will be of much importance. It'll be very helpful if I can get some code to get started with or some website where I can read about this.

Thanks!

EDIT: Both of these workbooks are saved on a shared drive on the network if that makes any difference.

 

[Ragnar1211]

Welcome to the Board.

Below is code that you can play with to start, it isn't super fast, but gives you a starting point:

Private fileCounter As Long
Private folderCounter As Long
Private activeSht As Worksheet
Dim foldernames(1 To 1000000, 0 To 0)
Dim filenames(1 To 1000000, 1 To 11)
Declare Function GetQueueStatus Lib "user32" (ByVal fuFlags As Long) As Long
Public Const QS_KEY = &H1
Public Const QS_MOUSEMOVE = &H2
Public Const QS_MOUSEBUTTON = &H4
Public Const QS_MOUSE = (QS_MOUSEMOVE Or QS_MOUSEBUTTON)
Public Const QS_INPUT = (QS_MOUSE Or QS_KEY)
'Display all the files in a folder. Searches all the sub folders.

'Prints Folder Names in Column A and and the file Names in Column B

Sub SearchFiles()
    Dim pth As String
    Dim FSO As FileSystemObject
    Dim baseFolder As Folder
    
    
    pth = "C:\Test" 'the base path which has to be searched for Files
    Set FSO = New FileSystemObject
                    
    ''check if the folder actually exists or not
                    
    If (Not (FSO.FolderExists(pth))) Then
        'the folder path is invalid. Exiting.
        MsgBox "Invalid Path"
        Exit Sub
    End If
    
    Set baseFolder = FSO.GetFolder(pth)
    
'Set the counters at 0
    fileCounter = 0
    folderCounter = 0
    
'Error handling info
    'On Error GoTo ErrHandler
    'Application.ScreenUpdating = False
    'Application.Calculation = xlCalculationManual
    
'Call the searcher
    PrintFileNames baseFolder

'Output the results
    Worksheets("FolderList").Range("a2").Resize(folderCounter, 1).Value = foldernames
    Worksheets("FileList").Range("a2").Resize(fileCounter, 11).Value = filenames

ErrHandler:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.StatusBar = False
End Sub
    
    
Sub PrintFileNames(baseFolder As Folder)
    Dim folder_ As Folder
    Dim file_ As File
    
    For Each folder_ In baseFolder.SubFolders
        'call recursive function.
        If GetQueueStatus(QS_INPUT) <> 0 Then DoEvents
        folderCounter = folderCounter + 1
        foldernames(folderCounter, 0) = folder_.Path
        Application.StatusBar = "Folders:  " & folderCounter & "  -  Files:  " & fileCounter & " - " & folder_.Name
        'Worksheets("Report").Cells(folderCounter, 1).Value = folder_.Path
        PrintFileNames folder_
        
    Next folder_
    
    For Each file_ In baseFolder.Files
        'print files here
        'activeSht.Range("A1").Offset(fileCounter, 0).Value = baseFolder.Path
        'activeSht.Range("B1").Offset(fileCounter, 0).Value = file_.Name
        If GetQueueStatus(QS_INPUT) <> 0 Then DoEvents
            fileCounter = fileCounter + 1
            filenames(fileCounter, 1) = baseFolder.Path
            filenames(fileCounter, 2) = file_.Name
            filenames(fileCounter, 3) = file_.Attributes
            filenames(fileCounter, 4) = file_.DateCreated
            filenames(fileCounter, 5) = file_.DateLastAccessed
            filenames(fileCounter, 6) = file_.DateLastModified
            filenames(fileCounter, 7) = file_.Drive
            filenames(fileCounter, 8) = file_.ParentFolder
            filenames(fileCounter, 9) = file_.Path
            filenames(fileCounter, 10) = file_.Size
            filenames(fileCounter, 11) = file_.Type
            
            
            Application.StatusBar = "Folders:  " & folderCounter & "  -  Files:  " & fileCounter & " - " & file_.Name
            'Worksheets("Report").Cells(fileCounter, 2).Value = baseFolder.Path & "\" & file_.Name
        
    Next file_

    

End Sub

 

[amtagrwl]

Hi Gerry,

Thanks for the immediate reply. I think I didn't explain myself very well. I am already able to accomplish reading the file names and their modified date and also putting the data in a worksheet. What I need help with is Reading and writing data from/to another workbook(not worksheet) using a macro from my current workbook. So basically, manipulating data in Workbook1.xls using a macro in Workbook2.xls

Hope you can help me with this.

Thanks again!

 

[Ragnar1211]

There are tons of examples on this site of working with multiple workbooks in a macro.
Below is a simplistic outline:

Sub SampleWB()

    Dim DestBook As Workbook
    Dim SourceBook As Workbook

    Dim DestSht As Worksheet
    Dim SrcSht As Worksheet


' Set the variables
    Set SourceBook = Workbooks("Source.xlsm")
    Set DestBook = Workbooks("Destination.xlsx")
    
    Set SrcSht = SourceBook.Worksheets("Sheet1")
    Set DestSht = DestBook.Worksheets("Sheet1")
    
'Do Some stuff etc....
    SrcSht.Range("A1:E10").Copy DestSht.Range("A1:E10")

End Sub