Activating previously used workbook

[Guest]

Hi there,
I have a workbook which will have a different name with every changing day.
I am trying to import some data via VB and have maneged the import part but
since the name will change I can't activate the original file with
windows("XXX").activate... I have tried to save the name but I did not
succeed...
Any help would be great.

Thank you.

Ozgur

 

[Ivan Raiminius]

Hi Ozgur,

can you work with the workbooks as with objects?

I mean:

dim wb1 as workbook
set wb1=workbooks.open filename:=(your original filename)


then you can activate your wb1 with wb1.activate.

Regards,
Ivan

 

[Guest]

Hi Ivan,
thanks for the reply.
I have a workbbok which has the following procedure:

Sub Import()
Workbooks.Open Filename:="R:\DAILY\May06\daily.xls"
Range("A2:L2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Application.CutCopyMode = False
Windows("daily.xls").Close
Sheets(1).Select
Range("A2").Select
ActiveSheet.Paste
End Sub


My problem is that I can't use Windows("X").activate because the workbook
with the import macro will have a different name every time. Rather than
opening I need to find a way to activate the workbook from with the macro.

Ozgur

 

[Ivan Raiminius]

Hi Ozgur,

if the procedure is in the workbook you need to activate, you can use:

thisworkbook.activate

if the workbook is different from that in which is the macro, you have
to create or open it, so you should be able to set it to object
variable.

But maybe I didn't understand you correctly.

Regards,
Ivan

 

[Guest]

Hi Ivan,
I am probably not very clear about the problem I face(sorry).
I tried thisworkbook.activate before but it did not work...

Last line activesheet.paste gives and error.

Sub Import()
Workbooks.Open Filename:="R:\treasury\DAILY\May06\daily.xls"
Range("A2:L2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Application.CutCopyMode = False
Windows("daily.xls").Close
ThisWorkbook.Activate
Sheets(1).Select
Range("A2").Select
ActiveSheet.Paste
End Sub

Thanks for your time.
Ozgur

 

[Ivan Raiminius]

Hi Ozgur,

activesheet.paste gives you error probably because of
Application.CutCopyMode = False in your code. Try to comment it out.
Or:

Sub Import()
Workbooks.Open Filename:="R:\treasury\DAILY\May06\daily.xls"
Range("A2:L2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ThisWorkbook.Activate
Sheets(1).Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("daily.xls").Close
End Sub

Regards,
Ivan

 

[Guest]

Ivan,
its official you are a genious...)

It worked.

Thanks a lot,
Ozgur