VBA code required to pull data from closed workbook

[hrayani]

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

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 MAX]

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]

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]

For the sake of a few seconds, I would live with what you've got.

 

[hrayani]

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]

I certainly wouldn't call it simple.
You can see an example here Use a closed workbook as a database (ADO) using VBA in Microsoft Excel

 

[hrayani]

I certainly wouldn't call it simple.
You can see an example here Use a closed workbook as a database (ADO) using VBA in Microsoft Excel

I will surely look into it

Thanks & Regards

 

[Fluff]

You're welcome