How to import the entire row(s) containing current (today's) date from a excel file into another excel file automatically without opening with VBA

0
votes

I am new to VBA programming. I want to import the entire row/(s)(example range from column "A" to "BT")that contains only current (today's) date (example, date is in column "B5") from a source excel sheet into current(backup excel)excel sheet automatically without opening the source excel.So, that I can backup my data regularly at the end of the day from backup excel file. Actually, the source worksheet is being updated regularly and in a day I have 5-10 new rows with the same date(current/today's) and so I want to copy all the new data(s) to backup excel sheet in rows next to previous day's backup in a backup excel file every day. Thank you The below VBA code seems working but it is copying the only column of interest but I need the entire rows to be copied. Do you have any idea to modify the below VBA code so that, it will also copy entire rows?

Option Explicit

Private Sub Workbook_Open() Call ReadDataFromCloseFile End Sub

Sub ReadDataFromCloseFile() On Error GoTo ErrHandler Application.ScreenUpdating = False

Dim src As Workbook

' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
Set src = Workbooks.Open("C:\Users\HAMENDRA\Desktop\Equation\Otarry.xlsm", True, True)

' GET THE TOTAL ROWS FROM THE SOURCE WORKBOOK.
Dim iTotalRows As Integer
iTotalRows = src.Worksheets("Records").Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Rows.Count

' COPY DATA FROM SOURCE (CLOSE WORKGROUP) TO THE DESTINATION WORKBOOK.
Dim iCnt As Integer         ' COUNTER.
For iCnt = 1 To iTotalRows
    Worksheets("Sheet1").Range("B" & iCnt).Formula = src.Worksheets("Records").Range("B" & iCnt).Formula
Next iCnt

' CLOSE THE SOURCE FILE.
src.Close False             ' FALSE - DON'T SAVE THE SOURCE FILE.
Set src = Nothing

ErrHandler: Application.EnableEvents = True Application.ScreenUpdating = True End Sub

1
vbaexcel
look at using ADO and SQL – Nathan_Sav
The above vba code is working correctly, but the only thing is it is pulling only single column. Where as I need the entire row to get updated. Can you throught a light to the above vba code. – dibyendu
How is it working? Your post is how to get data from a workbook without opening it, you are opening it with Workbooks.Open("C:\Users\HAMENDRA\Desktop\Equation\Otarry.xlsm", True, True) change your question title. – Nathan_Sav
Yes, you are correct in the sense. I just want to mean that not physically. – dibyendu

1 Answers

0
votes

Replace:

Worksheets("Sheet1").Range("B" & iCnt).Formula = src.Worksheets("Records").Range("B" & iCnt).Formula

With:

Worksheets("Sheet1").Range("B" & iCnt).EntireRow.Formula = src.Worksheets("Records").Range("B" & iCnt).EntireRow.Formula

But, you should also edit the title of this post. As the previous commentor said, you are opening the workbook. I'd suggest something like "How to copy entire row using VBA". Of course, if you had just googled "How to copy entire row VBA," you probably would have found the answer. :-)