Obtain data from a closed spreadsheet on a network drive

0

I need to be able to copy data using VBA from another spreadsheet on a network drive into an open spreadsheet. I can use normal Excel commands to do this if the spreadsheet with the data I need is on the drive on my PC. I can obtain this even if the spreadsheet that contains the data I need is not open. However, this does not work if the spreadsheet is on a network drive unless the spreadsheet is open. Assitance would be gratefully received.

Kind Regards

Peter

Answer
Discuss

Discussion

Can you open the workbook on the network drive from your PC using VBA code? I suspect that your problem is in accessing the drive and would like to take a look at the code you use for opening and/or acessing.
Variatus (rep: 1108) Sep 26, '17 at 4:57 am
Hi Variatus
As yet I have not built ant VBA code I was trying to do it just use standard Excel commands '=(workbook!A14), etc. This works if the workbook on the network drive is opened on my PC, but I want to get the data without opening the workbook. If the workbook is on my C drive no problem. So I would dearly like to be able to find sme VBA code that would either open the workbook in the background copy the data required and then close the workbook in the background, or extract the data from the workbook while it is closed?
Regards Peter
PRAFiddlers (rep: 2) Sep 26, '17 at 9:15 am
Add to Discussion

Answers

0
Selected Answer

Take a look at this thread. Adjust the code below to your requirements and try it.

Sub OpenWorkbook()
    ' replace "L" with the letter  assigned to the net work drive on your PC
    ' replace "\Path\" with the path on the network drive
    ' replace "Filename.xls" with the name of the workbook you wish to open
    Workbooks.Open Filename:="L:\Path\Filename.xls"
End Sub

Run the code by placing the cursor anywhere in the procedure and press F5 (or Run -> Sub/UserForm). The workbook should open.

Once you have found a way to open it you can use that same way to access it without opening.

Discuss

Discussion

Hi Variatus
That was extremely helpful, it worked OK
Kind Regards

Peter
PRAFiddlers (rep: 2) Sep 27, '17 at 9:30 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login