Listbox active workbooks and copy data

[jmaocubo]

Hi!!!

I'm trying to create a userform with a listbox contain all workbooks open. Then i select a workbook from the listbox and from that selected workbook i need to copy the range A1:L3 from sheet1 to my workbook same range but in sheet 4.

Problems:

1) to list all workbooks i'm using :

[vba]Private Sub UserForm_Activate()

Dim wbk As Workbook

ListBox1.Clear
For Each wbk In Workbooks
If wbk.Name <> ThisWorkbook.Name Then _
ListBox1.AddItem wbk.Name

Next wbk

End Sub[/vba]
But it only shows those workbooks that have been previous save and not those that have no name.


2) After resolving problem 1 i don't know how to make the part: copy the range A1:L3 from sheet1 to my workbook same range but in sheet 4




I hope you can help me.... Thanks in advance and sorry for my bad english

[mancubus]

try:

[VBA]
If Windows(wbk.Name).Visible Then _
[/VBA]

[jmaocubo]

try:

[vba]
If Windows(wbk.Name).Visible Then _
[/vba]

Hi mancubus

It does not work....

Only those who are previous saved appear.... if i open a new workbook and try to execute the macro it does not appear.

[mancubus]

oooopppssss.

the reason for error is "_" after "then" in the if block.



give this a try:

[vba]
Private Sub UserForm_Activate()

Dim wbk As Workbook

ListBox1.Clear
For Each wbk In Workbooks
If wbk.Name <> ThisWorkbook.Name Then
ListBox1.AddItem wbk.Name
End If
Next wbk

End Sub

[/vba]

[mancubus]

2) After resolving problem 1 i don't know how to make the part: copy the range A1:L3 from sheet1 to my workbook same range but in sheet 4

try.
you mas assign this code to a button or use as is.
[VBA]
Sub kopy()
Sheets("Sheet1").Range("A1:L3").Copy Destination:=Sheets("Sheet4").Range("A1")
End Sub
[/VBA]

[Kenneth Hobs]

You may need to so some other checks to be sure that the worksheets actually exist.

[vba]Private Sub CommandButton1_Click()
Workbooks(ListBox1.Value).Worksheets("Sheet4").Range("A1:L3").Value = _
ThisWorkbook.Worksheets("Sheet1").Range("A1:L3").Value
End Sub

Private Sub UserForm_Initialize()
Dim wbk As Workbook

ListBox1.Clear
For Each wbk In Workbooks
If wbk.Name = ThisWorkbook.Name Then GoTo NextWbk
If wbk.Name = "PERSONAL.XLSB" Then GoTo NextWbk
ListBox1.AddItem wbk.Name
NextWbk:
Next wbk
End Sub[/vba]

[jmaocubo]

I use a program that exports to excel. it opens a new workbook and export there. The listbox can not detect those workbooks that will open from the export

[Kenneth Hobs]

I am confused now.

The code that I posted shows all of the open workbooks except for the current one and the personal workbook in the current instance of Excel.

It looks like your Export program used CreateObject() rather than GetObject() to put the workbook in an existing instance if one existed. Had it done this, all workbook names would have been shown whether saved or not.

When multiple instances of Excel are created, collection object methods such as WorkBooks, will only work for that current instance. I have one idea to iterate through instances. Even so, you would be better off by closing those instances and then do something from that point on.

If you open Windows Task Manager, you can verify that multiple instances of Excel exists. Another way is to check the View > Switch Windows in your Excel file and one or more of the Export Excel workbooks.

[jmaocubo]

I am confused now.

The code that I posted shows all of the open workbooks except for the current one and the personal workbook in the current instance of Excel.

It looks like your Export program used CreateObject() rather than GetObject() to put the workbook in an existing instance if one existed. Had it done this, all workbook names would have been shown whether saved or not.

When multiple instances of Excel are created, collection object methods such as WorkBooks, will only work for that current instance. I have one idea to iterate through instances. Even so, you would be better off by closing those instances and then do something from that point on.

If you open Windows Task Manager, you can verify that multiple instances of Excel exists. Another way is to check the View > Switch Windows in your Excel file and one or more of the Export Excel workbooks.

Hi...

I tried to understand what you say, but could not follow. Could you maybe explain that part of the GetObject and CreateObject?
I'm looking in books for a VBA way to overcome this problem, but still to no avail. Is there another way to do?

Again many thanks for your help


Miguel

[Kenneth Hobs]

If you don't have any control over the Exported excel file methods I am sure that this would help. The concept is shown by this MSWord from Excel example. Notice how the GetObject method is used first. If an error occurs, that means that no instance is open so CreateObject is used.

[VBA]'http://www.mrexcel.com/forum/showthread.php?t=333200
Sub FillForm()
Dim wdApp As Object, WD As Object, rn As Long
rn = ActiveCell.Row
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set WD = wdApp.Documents.Open(ThisWorkbook.Path & "\Car Information Page.doc")

wdApp.Visible = True
With WD
.FormFields("Brand").Result = Cells(rn, "B")
.FormFields("Model").Result = Cells(rn, "C")
.FormFields("Chasis").Result = Cells(rn, "D")
.FormFields("Engine").Result = Cells(rn, "E")
.FormFields("Color").Result = Cells(rn, "F")
.FormFields("YearMonth").Result = Cells(rn, "G").Value & "/" & Cells(rn, "H").Value
End With

Set WD = Nothing
Set wdApp = Nothing
End Sub[/VBA]