Copy Paste Multiple Rows of Data from One WorkBook to Another Using Excel VB

[EileenJohn]

Hi, I'm a beginner in macros and I'm trying to:
1. Get the latest workbook from the DailyRecord folder.
2. Copy the latest workbook data to another workbook (Record.xlsx)
3. Set it on timer (daily auto update)

for now, i only manage to open latest workbook in DailyRecord folder. But i do wish to copy the data without opening the workbook

Here is my code (i get it from website) :

Sub CopyLatestFile()


'Declare the variables
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date
Dim src As Workbook

'Specify the path to the folder
MyPath = "C:\Users\user\Desktop\DailyRecord"

'Make sure that the path ends in a backslash
If Right(MyPath, 1) <> "" Then MyPath = MyPath & ""

'Get the first Excel file from the folder
MyFile = Dir(MyPath & "*.xls", vbNormal)

'If no files were found, exit the sub
If Len(MyFile) = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If

'Loop through each Excel file in the folder
Do While Len(MyFile) > 0

'Assign the date/time of the current file to a variable
LMD = FileDateTime(MyPath & MyFile)

'If the date/time of the current file is greater than the latest
'recorded date, assign its filename and date/time to variables
If LMD > LatestDate Then
LatestFile = MyFile
LatestDate = LMD
End If

'Get the next Excel file from the folder
MyFile = Dir

Loop

'Open the latest file
Set src = Workbooks.Open(MyPath & LatestFile, True, True)



End Sub


[h=1][/h]

 

[EssoExplJoe]

I don't think you can copy data or write data to a workbook unless it is opened unless you use Database functions to access the workbook. Below is an example of using ADO to access an excel workbook:

Public Function Get_DataUsingADO(wbfile As String, shtname As String, records As String, size As Integer) As Double
'Gets data from workbook without opening it using ADO and records time required
'   wbfile is full path and file name of the Dataworkbook
'   size is the number of times the arrays are filled
'   shtname is the worksheet name, i.e., "Sheet1"
'   records is the range for the record set, i.e., "A1:D5000"
'Notations:
'  
  Dim oRecs As Object, oConn As Object, starttime As Double, SecondsElapsed As Double, i As Integer, j As Integer
  Dim sConnString As String, b() As String, d() As String, sqlstring As String
  'Define Connection String and open connection and record set
  sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & wbfile & ";Extended Properties=""Excel 12.0 Xml;HDR=No;IMEX=1"";"
  Set oConn = CreateObject("ADODB.Connection")
  Set oRecs = CreateObject("ADODB.Recordset")
  'Open the ADO connection
  oConn.Open sConnString
  'Open the record set for shtname and records range
  sqlstring = "SELECT * FROM [" + shtname + "$" + records + "];"
  oRecs.Open sqlstring, oConn, adOpenStatic, adLockReadOnly
  
  Dim varray() As Variant
  varray() = oRST.GetRows()
  'Set up dummy arrays to hold data from Data workbook using the actual number of record sets returned
  ReDim b(oRecs.RecordCount, size)
  ReDim d(oRecs.RecordCount, size)
  'Start timer and move to the first record
  starttime = Timer
  oRecs.MoveFirst: i = 0
  'Fill the arrays with data from the data workbook
  For j = 1 To size
    For i = 1 To oRecs.RecordCount
      i = i + 1
      b(i, j) = oRecs.Fields(1).value
      d(i, j) = oRecs.Fields(3).value
      oRecs.MoveNext
    Next i
    oRecs.MoveFirst
  Next j
  'Determine how many seconds code took to run
  SecondsElapsed = Round(Timer - starttime, 4)
  Get_DataUsingADO = SecondsElapsed
adoexit:
  Set oRecs = Nothing:  Set oConn = Nothing
  Exit Function
adolongtime:
  Get_DataUsingADO = 9999999
  GoTo adoexit
End Function