Hi I wonder if anyone can help with a bit of VB, I am trying to get an update macro to copy data from a closed workbook within the same folder on the C:\ drive and paste to the current sheet. I can get this macro to work using this code to open the closed workbook-
Workbooks.Open Filename:= _ "C:\Documents and Settings\Ian\My Documents\work files\excel test\sales.xls"
But I would like the file address to be non geographical so the workbooks can be distrabuted on number of PC's. I know in VB6 you can use
App.Path & "\sales.xls"
Is there a command for VBA to open Workbook files in a shared folder?
I have trided Applcation.Path but it looks for the file in the Office 10 folder as Excel is the aplication. I would like the files to be placed in a folder in the my documents folder as there is less chance of users delving there way into the windows folder and messing about. I dont know if this would be posssible?
But your code looks very intesting so I am going to have a play around with it. current code opens and closes a number of sheets one at a time causing screen flashing even with Application.ScreenUpdating = False set. I may be going a long way around it but still very new to VB, but its true what they say you learn more from your mistakes and correcting them than anything else.