Visual Basic Apps

  Jarvo 19:23 22 Nov 2003

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?

Many thanks in advance


  VoG II 22:55 22 Nov 2003


  Jarvo 08:46 23 Nov 2003

Hi VoG

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?

Many Thanks


  VoG II 10:21 23 Nov 2003

Sorry, I wasn't thinking straight. Here's an example that opens another file in the same folder as the calling macro.

Sub Auto_Open()

Dim path As String

path = ThisWorkbook.path ' WaQCoMvb.XLA should be in this folder

Workbooks.Open (path & "\WaQCoMvb.XLA")

Workbooks("WaQCoMvb.XLA").RunAutoMacros xlAutoOpen

End Sub

Hope this helps.

  Jarvo 17:23 23 Nov 2003

Many thanks VoG

Cracked it today with

Workbooks.Open Filename:=Application.ActiveWorkbook.Path & "\Sales Doc\sales701.Xls"

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.



  plasticinepam 12:29 28 Nov 2003

how could i open one vb batch file from another batch file?

  Jarvo 19:09 28 Nov 2003


Not sure about batch files havent played around with them yet this may be one for VoG.

I know that you can use App.Path & "\Yourfilename" for textfiles but not sure this will work with batch files


  VoG II 19:13 28 Nov 2003

You need to post a new subject for this - vbs is not the same as vba.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

HP Envy x2 review: Hands-on

Iconic New York graphic designer Milton Glaser on his uplifting new subway posters

New iMac Pro release date, UK price & specs rumours

Comment suivre le parcours du père Noël ?