automation from access into excel

[Guest]

Hi

i have never been to the excel boards. i am not sure if it is apprpriate to
post both on access and excel groups. so i will try now, and if it isn't
cool, please let me know.

i generate a report on access then i export it to word. then i copy it from
word and i am trying to paste it into excel. i am the lease familiar with vba
in excel. i am having problems with the excel end of things. the following is
my code:

Code:
Set MyWordInstance = New Word.Application
MyWordInstance.Documents.Open "C:\tempRpt.rtf"
With MyWordInstance
.Selection.WholeStory
.Selection.Find.ClearFormatting
.Selection.Find.Replacement.ClearFormatting
With .Selection.Find
.Text = "^m"
.Replacement.Text = ""
.Forward = True
End With
.Selection.Find.Execute Replace:=wdReplaceAll
.Selection.WholeStory
.Selection.Copy
End With
MyWordInstance.ActiveDocument.Close
MyWordInstance.Quit
Kill "C:\tempPayperiodRpt.rtf"

' all of the above works fine, the following is giving me some problems

Set MyExcelInstance = New Excel.Application
MyExcelInstance.Workbooks.Add
MyExcelInstance.ActiveWorkbook.SaveAs "C:\PPP"
MyExcelInstance.ActiveWorkbook.ActiveSheet.Range("B5").Select
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("B5")
MyExcelInstance.ActiveWorkbook.Save
MyExcelInstance.ActiveWorkbook.Close
MyExcelInstance.Quit

 

[Jim Cone]

S,

You don't not say what problems you are having so I am having to guess.
("my car doesn't work, what's wrong with it?")

When automating Excel, you should set a reference to every object you refer to in Excel.
Then use the object references.
When quitting Excel, you must set each object reference to Nothing.
Not doing so can leave orphan references to Excel which prevent Excel from quitting.
You may also want to defer copying of the Word selection to just before you
paste it into Excel....
'------------------------------
Dim MyExcelInstance As Excel.Application
Dim MyWorkbook As Excel.Workbook
Dim MySheet As Excel.Worksheet

Set MyExcelInstance = New Excel.Application
Set MyWorkbook = MyExcelInstance.Workbooks.Add
Set MySheet = MyWorkbook.Worksheets(1)
MyWorkbook.SaveAs "C:\PPP"
MySheet.Paste Destination:=MySheet.Range("B5") 'note MySheet used twice
MyWorkbook.Close SaveChanges:=True
Set MySheet = Nothing
Set MyWorkbook = Nothing
MyExcelInstance.Quit
Set MyExcelInstance = Nothing
'------------------------------------------

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"SAm" <[email protected]>
wrote in message
Hi
i have never been to the excel boards. i am not sure if it is apprpriate to
post both on access and excel groups. so i will try now, and if it isn't
cool, please let me know.
i generate a report on access then i export it to word. then i copy it from
word and i am trying to paste it into excel. i am the lease familiar with vba
in excel. i am having problems with the excel end of things. the following is
my code:

Code:
Set MyWordInstance = New Word.Application
MyWordInstance.Documents.Open "C:\tempRpt.rtf"
With MyWordInstance
.Selection.WholeStory
.Selection.Find.ClearFormatting
.Selection.Find.Replacement.ClearFormatting
With .Selection.Find
.Text = "^m"
.Replacement.Text = ""
.Forward = True
End With
.Selection.Find.Execute Replace:=wdReplaceAll
.Selection.WholeStory
.Selection.Copy
End With
MyWordInstance.ActiveDocument.Close
MyWordInstance.Quit
Kill "C:\tempPayperiodRpt.rtf"

' all of the above works fine, the following is giving me some problems

Set MyExcelInstance = New Excel.Application
MyExcelInstance.Workbooks.Add
MyExcelInstance.ActiveWorkbook.SaveAs "C:\PPP"
MyExcelInstance.ActiveWorkbook.ActiveSheet.Range("B5").Select
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("B5")
MyExcelInstance.ActiveWorkbook.Save
MyExcelInstance.ActiveWorkbook.Close
MyExcelInstance.Quit

 

[Guest]

Hi Jim,

thanks for replying. i will try to use your code tomorow when i get to work.

i would like to explain, however. I am new to Excel VBA. the word part
didn't give errors. the excel part, was sometimes giving me errors. it may
very well be that it had to do with open workspaces (or whatever their
called). so perhaps it may be solved with the portion of code that you gave
me.

i would like to add to anybody reading this post, that for amatures, this
code me a solution. as you may know, and i saw some posts on this, that
reports maybe difficult to export. by exporting first to word, and then
either modifying in word or in excel, one can achieve the correct output.

thanks and good night,

sam

 

[Guest]

thanks, most have been the problem. i didn't close it correctly. now it works
fine, your script did it.

sam

 

[Jim Cone]

sam,
You are welcome. Appreciate getting the feedback.
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"SAm" <[email protected]>
wrote in message
thanks, most have been the problem. i didn't close it correctly. now it works
fine, your script did it.
sam

 

[Guest]

one more short one. i tried the following in excel, and it works. when i run
from access, i am having some problems (i will paste only the part that you
didn't give me).

MyWorkbook.Save
MySheet.Range("A2").EntireRow.Select

Do While ActiveCell.Value <> ""
ActiveCell.EntireRow.Insert
ActiveCell.Offset(2, 0).EntireRow.Select
Loop
MyWorkbook.Save
MyWorkbook.Close SaveChanges:=True
Set MySheet = Nothing

 

[Jim Cone]

sam,

Don't use ActiveCell, Selection, UsedRange etc.
Use object references

Set MyCell = MySheet.Range("A2")

Do While MyCell.Value <> ""
MyCell.EntireRow.Insert
Set MyCell = MyCell.Offset(2, 0)
Loop

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"SAm" <[email protected]>
wrote in message
one more short one. i tried the following in excel, and it works. when i run
from access, i am having some problems (i will paste only the part that you
didn't give me).

MyWorkbook.Save
MySheet.Range("A2").EntireRow.Select

Do While ActiveCell.Value <> ""
ActiveCell.EntireRow.Insert
ActiveCell.Offset(2, 0).EntireRow.Select
Loop
MyWorkbook.Save
MyWorkbook.Close SaveChanges:=True
Set MySheet = Nothing

 

[Guest]

thanks again. it worked. i will try to brush up on my skills on vba in excel.

sam