hi im trying to us excel to log whick folders i access within my email and
am
struggling with the automation between outloook and excel

i appear to be able to do this by creating a new excel object and then
opening the excel file writing the info and then closing it again see code
below.

what i would like to do is leave the escel file open as i assume this
would
be quicker but if i do this creatobject opens the file as read only in the
background

i think i need to use getobject to pick up the open file but cant get it
to
work any pointers

code:

Sub log()

Set MyApp = CreateObject("excel.application")



Set objNS = Application.GetNamespace("MAPI")
Set thisfolder = Application.ActiveExplorer.CurrentFolder

MyApp.workbooks.Open "C:\Documents and Settings\Peter\My
Documents\test.xls", ReadOnly:="false"
MyApp.workbooks("test.xls").worksheets(1).Range("A 1").Select
MyApp.Selection.entirerow.Insert
MyApp.workbooks("test.xls").worksheets(1).Range("A 1").Value =
thisfolder.Name



MyApp.workbooks("test.xls").Close (True)



End Sub

Before calling CreateObject check if GetObject returns an object. If it
does, see whether the file is already in the Workbooks collection before
calling Workbooks.Open.

--
Best regards
Michael Bauer - MVP Outlook
Manage and share your categories:
http://www.vboffice.net/product.html?pub=6&lang=en


Am Sun, 3 Jan 2010 08:03:01 -0800 schrieb pete the greek:

hi im trying to us excel to log whick folders i access within my email
and
am
struggling with the automation between outloook and excel

i appear to be able to do this by creating a new excel object and then
opening the excel file writing the info and then closing it again see
code
below.

what i would like to do is leave the escel file open as i assume this
would
be quicker but if i do this creatobject opens the file as read only in
the
background

i think i need to use getobject to pick up the open file but cant get it
to
work any pointers

code:

Sub log()

Set MyApp = CreateObject("excel.application")



Set objNS = Application.GetNamespace("MAPI")
Set thisfolder = Application.ActiveExplorer.CurrentFolder

MyApp.workbooks.Open "C:\Documents and Settings\Peter\My
Documents\test.xls", ReadOnly:="false"
MyApp.workbooks("test.xls").worksheets(1).Range("A 1").Select
MyApp.Selection.entirerow.Insert
MyApp.workbooks("test.xls").worksheets(1).Range("A 1").Value =
thisfolder.Name



MyApp.workbooks("test.xls").Close (True)



End Sub

thanks Michael

getting the info into the spreadsheet now

the code runs great from outlook and is much quicker with the spreadsheet open

one thing i would like but cant get is to bring the excel spreadsheet to the
front after inseting the data as there is a field i need to manual update.

i have tried all the variations i can think of on MyApp and MyApp.workbook
ie select,show,visible.activate but cant get the spreadsheet to the front

regards

pete





"Michael Bauer [MVP - Outlook]" wrote:



Before calling CreateObject check if GetObject returns an object. If it
does, see whether the file is already in the Workbooks collection before
calling Workbooks.Open.

--
Best regards
Michael Bauer - MVP Outlook
Manage and share your categories:
http://www.vboffice.net/product.html?pub=6&lang=en


Am Sun, 3 Jan 2010 08:03:01 -0800 schrieb pete the greek:

hi im trying to us excel to log whick folders i access within my email
and
am
struggling with the automation between outloook and excel

i appear to be able to do this by creating a new excel object and then
opening the excel file writing the info and then closing it again see
code
below.

what i would like to do is leave the escel file open as i assume this
would
be quicker but if i do this creatobject opens the file as read only in
the
background

i think i need to use getobject to pick up the open file but cant get it
to
work any pointers

code:

Sub log()

Set MyApp = CreateObject("excel.application")



Set objNS = Application.GetNamespace("MAPI")
Set thisfolder = Application.ActiveExplorer.CurrentFolder

MyApp.workbooks.Open "C:\Documents and Settings\Peter\My
Documents\test.xls", ReadOnly:="false"
MyApp.workbooks("test.xls").worksheets(1).Range("A 1").Select
MyApp.Selection.entirerow.Insert
MyApp.workbooks("test.xls").worksheets(1).Range("A 1").Value =
thisfolder.Name



MyApp.workbooks("test.xls").Close (True)



End Sub
.

That's how it works as Excel is another process, and the last line of code
executed is always in Outlook, which brings Outlook up to the front. You
could minimize the current Outlook window, or use some Win32 API functions
in order to find the Excel window.

--
Best regards
Michael Bauer - MVP Outlook
Manage and share your categories:
http://www.vboffice.net/product.html?pub=6&lang=en


Am Mon, 4 Jan 2010 07:51:01 -0800 schrieb pete the greek:

thanks Michael

getting the info into the spreadsheet now

the code runs great from outlook and is much quicker with the spreadsheet
open

one thing i would like but cant get is to bring the excel spreadsheet to
the
front after inseting the data as there is a field i need to manual update.

i have tried all the variations i can think of on MyApp and MyApp.workbook
ie select,show,visible.activate but cant get the spreadsheet to the front

regards

pete





"Michael Bauer [MVP - Outlook]" wrote:



Before calling CreateObject check if GetObject returns an object. If it
does, see whether the file is already in the Workbooks collection before
calling Workbooks.Open.

--
Best regards
Michael Bauer - MVP Outlook
Manage and share your categories:
http://www.vboffice.net/product.html?pub=6&lang=en


Am Sun, 3 Jan 2010 08:03:01 -0800 schrieb pete the greek:

hi im trying to us excel to log whick folders i access within my email
and
am
struggling with the automation between outloook and excel

i appear to be able to do this by creating a new excel object and then
opening the excel file writing the info and then closing it again see
code
below.

what i would like to do is leave the escel file open as i assume this
would
be quicker but if i do this creatobject opens the file as read only in
the
background

i think i need to use getobject to pick up the open file but cant get
it
to
work any pointers

code:

Sub log()

Set MyApp = CreateObject("excel.application")



Set objNS = Application.GetNamespace("MAPI")
Set thisfolder = Application.ActiveExplorer.CurrentFolder

MyApp.workbooks.Open "C:\Documents and Settings\Peter\My
Documents\test.xls", ReadOnly:="false"
MyApp.workbooks("test.xls").worksheets(1).Range("A 1").Select
MyApp.Selection.entirerow.Insert
MyApp.workbooks("test.xls").worksheets(1).Range("A 1").Value =
thisfolder.Name



MyApp.workbooks("test.xls").Close (True)



End Sub
.