Sleeper: How to activate an open workbook

[martha555]

I am creating a data entry form in excel. I have created a worksheet(file1.xls), where I have written a macro to open another excel file(file2.xls). I enter/ select data in this file (file2.xls) and then click a button to save the data. The macro written on CommandButton1_Click() will transfer some data from this file (file2.xls) to the first file (file1.xls) and then close the second file (file2.xls). How do I go about it.

Please help

[Jacob Hilderbrand]

You can refer to the workbook you want to work with in your code.

Workbooks("file1.xls").Sheets("Sheet1").Range("A1").Value = 
Workbooks("file1.xls").Activate
Workbooks("file1.xls").Save
Workbooks("file1.xls").Close

For example.

[martha555]

thanx a ton, will try and get back

[Jacob Hilderbrand]

Ok, let me know if you have any followup questions.

[martha555]

hi once again,
what if I want to toggle between two open workbooks? The scenario is as such:
I am creating a template i.e., a format worksheet in excel. Every time I want to create a document using that template, a new file is created with the same name as the template plus a new document number. For e.g., the name of my excel worksheet is mrn.xls, which is the template. Now when I create a new document using this template, a new file is opened opened with the name "doc number 1 mrn.xls"(this file will have the same format as "mrn.xls". From this file I open another excel file "mrntemp.xls". After entering some values, I have written a macro in this file "mrntemp.xls", wherien I am passing some values into the new file "doc number 1 mrn.xls" into some cells. After this the "mrntemp.xls" file closes and hence only "doc number 1 mrn.xls" file is open. My problem is how to pass the values from the active workbook to the other workbook which is open(this workbook number will always change, hence I can't hardcode the filename) Please help.

[Jacob Hilderbrand]

Well you can store the workbook name(s) in a cell on the main workbook, then retrieve them later. Or you can store the workbook to a variable when you open it.

Dim Wkb As Workbook 
Set Wkb = Workbooks.Open(FileName:=...)

[martha555]

Hi,

My requirement is a little different. The name of my main workbook always changes like "doc number 1 main.xls", "doc number 2 main.xls" and so on. From the main workbook I open another workbook file1.xls, do some entries and then pass some values to the main workbook after which it closes. I have written a macro for this in file1.xls. Using the following command

Workbooks("doc number 1 main.xls").Activate

will require me to know the file name in advance. But I cannot hardcode the file name. Is there a solution to this. Please help.

[jetted]

Hi

I would paste this code in the workbook section. This will allow to save the workbook in file that you can access later.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim LogFile As String
Close #1
LogFile = "c:\test\activite.log"
ChDir "c:\test"
Open LogFile For Append Shared As #1
Print #1, ActiveWorkbook.Name
Close #1
End Sub

and then I would you put this in a module

Sub auto_open()
Close #2 ' Close file.
LogFile = "c:\test\activite.log"
ChDir "c:\test"
Open LogFile For Input As #2 
Do While Not EOF(2) ' Loop until end of file.
Input #2, MyWorkbookName
Loop
Close #2 ' Close file.
MsgBox MyWorkbookName
End Sub

[sheeeng]

Hi

I would paste this code in the workbook section. This will allow to save the workbook in file that you can access later.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim LogFile As String
Close #1
LogFile = "c:\test\activite.log"
ChDir "c:\test"
Open LogFile For Append Shared As #1
Print #1, ActiveWorkbook.Name
Close #1
End Sub

and then I would you put this in a module

Sub auto_open()
Close #2 ' Close file.
LogFile = "c:\test\activite.log"
ChDir "c:\test"
Open LogFile For Input As #2 
Do While Not EOF(2) ' Loop until end of file.
Input #2, MyWorkbookName
Loop
Close #2 ' Close file.
MsgBox MyWorkbookName
End Sub

Wonderful!

[martha555]

hi!,
thanx for the help. But I do not have this path "c:\test\activite.log" . Should I create it? Secondly, do I paste the code in the main workbook(i.e., from where I open the second workbook) or the second workbook.

[Zack Barresse]

You can create that path if you want. It can actually be anything you want, as long as it matches in the code. You can put it in the first workbook if you want.