VBA code required to pull data from closed workbook

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,035
Office Version
  1. 2016
Platform
  1. Windows
Hello Friends,

I am using the below code to pull data... Is it possible to tweak the code to pull the data without opening the source workbook.. like mentioning the path or something

VBA Code:
Private Sub update_database()
   
    Application.ScreenUpdating = False
 
    Dim ws As Worksheet
                    
Set WSArray = Workbooks("REPORTS.xlsm").Worksheets(Array("INDEX", "DATABASE", "R VALIDATION", "R DAILY PRODUCTION", "R FRI DPI LABTEST", "R PO DESIGN COLOUR"))

For Each ws In WSArray
ws.Unprotect Password:="merchant"
If ws.AutoFilterMode Then ws.AutoFilterMode = False
Next
  
Workbooks.Open Filename:="C:/OneDrive/Documents/DATA ENTRY.xlsm"
Set WSArray1 = Workbooks("DATA ENTRY.xlsm").Worksheets(Array("VALIDATION", "ORDERS", "DAILY PRODUCTION", "FRI DPI LABTEST", "PO DESIGN COLOUR"))
For Each ws In WSArray1
If ws.AutoFilterMode Then ws.AutoFilterMode = False
Next
   
Workbooks("REPORTS.xlsm").Activate
Worksheets("R VALIDATION").Cells.ClearContents
Worksheets("DATABASE").Range("A2:AO2").ClearContents
Worksheets("DATABASE").Range("orders").ClearContents
Worksheets("R DAILY PRODUCTION").Cells.ClearContents
Worksheets("R FRI DPI LABTEST").Cells.ClearContents
Worksheets("R PO DESIGN COLOUR").Cells.ClearContents

   
    '''''''''''''''''''''''''''''''''''''''''''
    Workbooks("DATA ENTRY.xlsm").Worksheets("VALIDATION").Cells.Copy
    Workbooks("REPORTS.xlsm").Worksheets("R VALIDATION").Range("A1").PasteSpecial Paste:=xlPasteValues
   
    '''''''''''''''''''''''''''''''''''''''''''
    Workbooks("DATA ENTRY.XLSM").Worksheets("ORDERS").Range("A2:AO10000").Copy
    Workbooks("REPORTS.xlsm").Worksheets("DATABASE").Range("A4").PasteSpecial Paste:=xlPasteValues
   
    '''''''''''''''''''''''''''''''''''''''''''
    Workbooks("DATA ENTRY.xlsm").Worksheets("DAILY PRODUCTION").Cells.Copy
    Workbooks("REPORTS.xlsm").Worksheets("R DAILY PRODUCTION").Range("A1").PasteSpecial Paste:=xlPasteValues
   
    '''''''''''''''''''''''''''''''''''''''''''
    Workbooks("DATA ENTRY.xlsm").Worksheets("FRI DPI LABTEST").Cells.Copy
    Workbooks("REPORTS.xlsm").Worksheets("R FRI DPI LABTEST").Range("A1").PasteSpecial Paste:=xlPasteValues
   
    '''''''''''''''''''''''''''''''''''''''''''
    Workbooks("DATA ENTRY.xlsm").Worksheets("PO DESIGN COLOUR").Cells.Copy
    Workbooks("REPORTS.xlsm").Worksheets("R PO DESIGN COLOUR").Range("A1").PasteSpecial Paste:=xlPasteValues
   
    '''''''''''''''''''''''''''''''''''''''''''
   
Workbooks("DATA ENTRY.xlsm").Close savechanges:=False  
       
   For Each ws In WSArray
   ws.Protect Password:="merchant", DrawingObjects:=True, Contents:=True, Scenarios:=True _
       , AllowFormattingColumns:=True, AllowFormattingRows:=True
     
Next
Application.ScreenUpdating = False

End Sub
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
Hello Hrayani,
I don't know too much about this, but I think workbook need to be opened to be readed.
If can be helpfull and you still not tryed this ...
Create new excel instance, set instance in invisible mode,
open workbook in this new Excel instance, manipulate with sheets,
in the end close opened workbook and delete new Excel instance.
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,035
Office Version
  1. 2016
Platform
  1. Windows
Hi, thanks for the reply
The code in post 1 is working fine. But takes few seconds to complete the task
I was just wondering if someone could help me to pull data without opening the workbook which would reduce the time.
I believe it is possible but not sure how to do it
May be some expert out here could up.with a piece of code or some clue.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
For the sake of a few seconds, I would live with what you've got.
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,035
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

For the sake of a few seconds, I would live with what you've got.
Thanks Fluff
Sounds like it needs some massive coding
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,129,296
Messages
5,635,364
Members
416,856
Latest member
silentir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top