• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copying excel data from closed excel to another excel sheet at different location

R

Ratish

New Member
Hi Chandoo,

I am new to Excel VBA, I want to copy data from excelsheet (Source Workbook (Local drive)) to another excelsheet with overwriting feature, that is where the below code is running. But unfortunately its open a new excel sheet every time i run it.

I have a tool which generates excel report every 5 min and with a VBA code its getting saved to my local drive. Now I want to copy data from the file which is saved locally to my excel file to cross verify both files.

Below is the working code.

Sub extractDataFromClosedFile()

OnErrorGoTo ErrHandler
Application.ScreenUpdating =False

Dim src As WorkbookSet src = Workbooks.Open("C:\Users\test.xls",True,True)

Dim iTotalRows AsInteger
iTotalRows = src.Worksheets("Sheet1").Range("A1:A"& Cells(Rows.Count,"A").End(xlUp).Row).Rows.Count

Dim iCnt AsInteger

For iCnt =1To iTotalRows
Worksheets("Sheet6").Range("A3"& iCnt).Formula = src.Worksheets("Sheet1").Range("b"& iCnt).Formula
Next iCnt

src.Close FalseSet src =Nothing

ErrHandler:
Application.EnableEvents =True
Application.ScreenUpdating =True
Debug.Print Err.Description

EndSub

Basically you want to copy data from WorkBook1 (Source Workbook (Local drive)) to another workbook, that is where the above code is running
 
Why do You ask first 'Copying excel data from closed excel to another excel sheet at different location' and You try to use code which will open Your source?
If need to open then could You use something like (not tested) ...
WorkBook(WB1).Sheets(WS1).UsedRange.Copy Destination:= _
WorkBook(WB2).Sheets(WS2).Range("A1")
... and ... Upload Sample Files to get a quicker response.
 
vletm said:
Why do You ask first 'Copying excel data from closed excel to another excel sheet at different location' and You try to use code which will open Your source?
If need to open then could You use something like (not tested) ...
WorkBook(WB1).Sheets(WS1).UsedRange.Copy Destination:= _
WorkBook(WB2).Sheets(WS2).Range("A1")
... and ... Upload Sample Files to get a quicker response.
Click to expand...

My requirement is to copy data from one excel sheet to another excel sheet saved in two different location. I am not trying to open any file. But trying to copy data from closed file.

As mention i am new to vba and I may have made mistake in above code which might be opening data in new excel sheet, instead of copying the data.
 
Could You name the Range from 'source' which data You would like to copy?
Now ... You copy 'formula's.
 
For testing ...
There is one macro with notes...
>> Ideas ... Questions?
 

Attachments

  • ratish.xlsb
    30.8 KB · Views: 9
Hi Ratish,

Why not use Power Query to extract data instead of writing macros? It can extract information from various sources including the workbooks stored in a specific placeholder so long as you've access to the file.

Kind regards,
Anand

Ratish said:
Hi Chandoo,

I am new to Excel VBA, I want to copy data from excelsheet (Source Workbook (Local drive)) to another excelsheet with overwriting feature, that is where the below code is running. But unfortunately its open a new excel sheet every time i run it.

I have a tool which generates excel report every 5 min and with a VBA code its getting saved to my local drive. Now I want to copy data from the file which is saved locally to my excel file to cross verify both files.

Below is the working code.

Sub extractDataFromClosedFile()

OnErrorGoTo ErrHandler
Application.ScreenUpdating =False

Dim src As WorkbookSet src = Workbooks.Open("C:\Users\test.xls",True,True)

Dim iTotalRows AsInteger
iTotalRows = src.Worksheets("Sheet1").Range("A1:A"& Cells(Rows.Count,"A").End(xlUp).Row).Rows.Count

Dim iCnt AsInteger

For iCnt =1To iTotalRows
Worksheets("Sheet6").Range("A3"& iCnt).Formula = src.Worksheets("Sheet1").Range("b"& iCnt).Formula
Next iCnt

src.Close FalseSet src =Nothing

ErrHandler:
Application.EnableEvents =True
Application.ScreenUpdating =True
Debug.Print Err.Description

EndSub

Basically you want to copy data from WorkBook1 (Source Workbook (Local drive)) to another workbook, that is where the above code is running
Click to expand...
 
rrocker1405 said:
Hi Ratish,

Why not use Power Query to extract data instead of writing macros? It can extract information from various sources including the workbooks stored in a specific placeholder so long as you've access to the file.

Kind regards,
Anand
Click to expand...
Hi Anand,

I am not sure how power query works.

Regards
Ratish
 
Ratish said:
Hi Anand,

I am not sure how power query works.

Regards
Ratish
Click to expand...
Hi Ratish,

If you're using Office 2013 then download the add-in Powerquery from url as mentioned below:

https://www.microsoft.com/en-gb/download/details.aspx?id=39379

Choose version 32/64bit depending on your office suite. if you do not have administration access to install the add-in ask the IT team to have it installed for you.

Note: if you're using office 2016 its already built-in as a feature within the Data tab in the ribbon.

Once done, add the same in your excel application by going file > options > add-ins > com-addins > Power Query

The above will get you power query in your excel application ribbon. Now click on From file > excel workbook > navigate to the location where the source of the file is and then you can load, edit information which you need and you can also add it the same data model for further analysis.

Regards,
A!
 
rrocker1405 said:
Hi Ratish,

If you're using Office 2013 then download the add-in Powerquery from url as mentioned below:

https://www.microsoft.com/en-gb/download/details.aspx?id=39379

Choose version 32/64bit depending on your office suite. if you do not have administration access to install the add-in ask the IT team to have it installed for you.

Note: if you're using office 2016 its already built-in as a feature within the Data tab in the ribbon.

Once done, add the same in your excel application by going file > options > add-ins > com-addins > Power Query

The above will get you power query in your excel application ribbon. Now click on From file > excel workbook > navigate to the location where the source of the file is and then you can load, edit information which you need and you can also add it the same data model for further analysis.

Regards,
A!
Click to expand...
Pretty cool feature. BTW can we fix the refresh time in power query ?
 
Hi Ratish,

You can have it refreshed with a schedule refresh using Power BI. However, you might need a user license for the same.

However, you can write a small macro to refresh query for you at a specific time and include a task in the windows task scheduler so that the file can open, refresh and save at a specific time of the day.

Kind regards,
A!
 
You must log in or register to reply here.
Back
Top