Is it possible to activate a dormant or closed file via VBA

[Jo4x4]

Hi everybody,

Is it possible to activate a worksheet (on the same computer) via VBA.

What I am trying to do is to save data to an open (monthly) worksheet, and then copy that to a closed (yearly) worksheet.

Thanks
Jo

Win XP, Office 2007

 

[wigi]

You'd better open the file first (via code) and then do your copy/paste action.

 

[kpark91]

Hi, Jo4x4.
You must open the workbook first in order to write to it then close it.
There is a code in this forum somewhere that "locks" the screen completely so that the user does not see the workbook opened.

 

[Jo4x4]

Hi guys,

Thanks for the quick reply.

How do I open the file via VBA?

And I am not really concerned with anybody seeing the content, it is just that they might forget to open the "yearly" file, and then get a VBA error when trying to save.

Thanks again
Jo

Win XP, Office 2007

 

[Ruddles]

At it's simplest, you can do it something like this:-

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Public Sub OpenWorkbookAndDoStuffToIt()[/FONT]
  
[FONT=Fixedsys]  Dim wkbk As Workbook[/FONT]
 
[FONT=Fixedsys][COLOR=green]  ' open the workbook with a known path & name[/COLOR][/FONT]
[FONT=Fixedsys]  Application.EnableEvents = False[/FONT]
[FONT=Fixedsys]  Set wkbk = Workbooks.Open("c:\folder\filename.xls")[/FONT]
[FONT=Fixedsys]  Application.EnableEvents = True[/FONT]
 
[FONT=Fixedsys][COLOR=green]  ' now you can do stuff to the workbook[/COLOR][/FONT]
[FONT=Fixedsys][COLOR=blue]  wkbk.Sheets(1).Range("A1") = ThisWorkbook.Sheets(1).Range("A1")[/COLOR][/FONT]
[FONT=Fixedsys][COLOR=green][COLOR=#0000ff]  ThisWorkbook.Sheets(1).Range("X99").Copy Destination:=wkbk.Sheets(1).Range("X99")[/COLOR][/COLOR][/FONT]
 
[COLOR=green][FONT=Fixedsys]  ' save and close the workbook[/FONT][/COLOR]
[FONT=Fixedsys]  Application.EnableEvents = False[/FONT]
[FONT=Fixedsys]  wkbk.Close SaveChanges:=True[/FONT]
[FONT=Fixedsys]  Application.EnableEvents = True[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]

The blue bit is where you have the workbook open and you can 'do stuff' with it. I've coded a couple of very simple statements to copy a couple of values from the current workbook to the newly-opened one, in two different ways. Your code goes here.

That all assumes that you know the path & name of the workbook at the time of writing the code. If you want to open a dialog box so that the user can specify the workbook he wants, you'd do it something like this:-

[FONT=Fixedsys]Dim sFilename As String[/FONT]
 
[FONT=Fixedsys]sFilename = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")[/FONT]
[FONT=Fixedsys]If sFilename = "False" Then Exit Sub[/FONT]

Then you'd change the code for opening the workbook to:-

[FONT=Fixedsys]Set wkbk = Workbooks.Open(sFilename)[/FONT]

Is that what you're looking for?

 

[Jo4x4]

Thanks a stack!

Jo

 

[Ruddles]

I guess you couldn't wish an old Assembler programmer anything better than that! ←clicky