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 quot;C:\tempRpt.rtfquot;
With MyWordInstance
.Selection.WholeStory
.Selection.Find.ClearFormatting
.Selection.Find.Replacement.ClearFormatting
With .Selection.Find
.Text = quot;^mquot;
.Replacement.Text = quot;quot;
.Forward = True
End With
.Selection.Find.Execute Replace:=wdReplaceAll
.Selection.WholeStory
.Selection.Copy
End With
MyWordInstance.ActiveDocument.Close
MyWordInstance.Quit
Kill quot;C:\tempPayperiodRpt.rtfquot;
' all of the above works fine, the following is giving me some problems
Set MyExcelInstance = New Excel.Application
MyExcelInstance.Workbooks.Add
MyExcelInstance.ActiveWorkbook.SaveAs quot;C:\PPPquot;
MyExcelInstance.ActiveWorkbook.ActiveSheet.Range(quot; B5quot;).Select
ActiveSheet.Paste Destination:=Worksheets(quot;Sheet1quot;).Range(quot;B5quot;)
MyExcelInstance.ActiveWorkbook.Save
MyExcelInstance.ActiveWorkbook.Close
MyExcelInstance.Quit
S,
You don't not say what problems you are having so I am having to guess.
(quot;my car doesn't work, what's wrong with it?quot;)
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 quot;C:\PPPquot;
MySheet.Paste Destination:=MySheet.Range(quot;B5quot;) 'note MySheet used twice
MyWorkbook.Close SaveChanges:=True
Set MySheet = Nothing
Set MyWorkbook = Nothing
MyExcelInstance.Quit
Set MyExcelInstance = Nothing
'------------------------------------------
Jim Cone
San Francisco, USA
www.realezsites.com/bus/primitivesoftwarequot;SAmquot; gt;
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 quot;C:\tempRpt.rtfquot;
With MyWordInstance
.Selection.WholeStory
.Selection.Find.ClearFormatting
.Selection.Find.Replacement.ClearFormatting
With .Selection.Find
.Text = quot;^mquot;
.Replacement.Text = quot;quot;
.Forward = True
End With
.Selection.Find.Execute Replace:=wdReplaceAll
.Selection.WholeStory
.Selection.Copy
End With
MyWordInstance.ActiveDocument.Close
MyWordInstance.Quit
Kill quot;C:\tempPayperiodRpt.rtfquot;
' all of the above works fine, the following is giving me some problems
Set MyExcelInstance = New Excel.Application
MyExcelInstance.Workbooks.Add
MyExcelInstance.ActiveWorkbook.SaveAs quot;C:\PPPquot;
MyExcelInstance.ActiveWorkbook.ActiveSheet.Range(quot; B5quot;).Select
ActiveSheet.Paste Destination:=Worksheets(quot;Sheet1quot;).Range(quot;B5quot;)
MyExcelInstance.ActiveWorkbook.Save
MyExcelInstance.ActiveWorkbook.Close
MyExcelInstance.Quit
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
quot;Jim Conequot; wrote:
gt; S,
gt;
gt; You don't not say what problems you are having so I am having to guess.
gt; (quot;my car doesn't work, what's wrong with it?quot;)
gt;
gt; When automating Excel, you should set a reference to every object you refer to in Excel.
gt; Then use the object references.
gt; When quitting Excel, you must set each object reference to Nothing.
gt; Not doing so can leave orphan references to Excel which prevent Excel from quitting.
gt; You may also want to defer copying of the Word selection to just before you
gt; paste it into Excel....
gt; '------------------------------
gt; Dim MyExcelInstance As Excel.Application
gt; Dim MyWorkbook As Excel.Workbook
gt; Dim MySheet As Excel.Worksheet
gt;
gt; Set MyExcelInstance = New Excel.Application
gt; Set MyWorkbook = MyExcelInstance.Workbooks.Add
gt; Set MySheet = MyWorkbook.Worksheets(1)
gt; MyWorkbook.SaveAs quot;C:\PPPquot;
gt; MySheet.Paste Destination:=MySheet.Range(quot;B5quot;) 'note MySheet used twice
gt; MyWorkbook.Close SaveChanges:=True
gt; Set MySheet = Nothing
gt; Set MyWorkbook = Nothing
gt; MyExcelInstance.Quit
gt; Set MyExcelInstance = Nothing
gt; '------------------------------------------
gt;
gt; Jim Cone
gt; San Francisco, USA
gt; www.realezsites.com/bus/primitivesoftware
gt;
gt;
gt; quot;SAmquot; gt;
gt; wrote in message
gt;
gt; Hi
gt; i have never been to the excel boards. i am not sure if it is apprpriate to
gt; post both on access and excel groups. so i will try now, and if it isn't
gt; cool, please let me know.
gt; i generate a report on access then i export it to word. then i copy it from
gt; word and i am trying to paste it into excel. i am the lease familiar with vba
gt; in excel. i am having problems with the excel end of things. the following is
gt; my code:
gt;
gt; Code:
gt; Set MyWordInstance = New Word.Application
gt; MyWordInstance.Documents.Open quot;C:\tempRpt.rtfquot;
gt; With MyWordInstance
gt; .Selection.WholeStory
gt; .Selection.Find.ClearFormatting
gt; .Selection.Find.Replacement.ClearFormatting
gt; With .Selection.Find
gt; .Text = quot;^mquot;
gt; .Replacement.Text = quot;quot;
gt; .Forward = True
gt; End With
gt; .Selection.Find.Execute Replace:=wdReplaceAll
gt; .Selection.WholeStory
gt; .Selection.Copy
gt; End With
gt; MyWordInstance.ActiveDocument.Close
gt; MyWordInstance.Quit
gt; Kill quot;C:\tempPayperiodRpt.rtfquot;
gt;
gt; ' all of the above works fine, the following is giving me some problems
gt;
gt; Set MyExcelInstance = New Excel.Application
gt; MyExcelInstance.Workbooks.Add
gt; MyExcelInstance.ActiveWorkbook.SaveAs quot;C:\PPPquot;
gt; MyExcelInstance.ActiveWorkbook.ActiveSheet.Range(quot; B5quot;).Select
gt; ActiveSheet.Paste Destination:=Worksheets(quot;Sheet1quot;).Range(quot;B5quot;)
gt; MyExcelInstance.ActiveWorkbook.Save
gt; MyExcelInstance.ActiveWorkbook.Close
gt; MyExcelInstance.Quit
gt;
thanks, most have been the problem. i didn't close it correctly. now it works
fine, your script did it.
sam
quot;SAmquot; wrote:
gt; Hi Jim,
gt;
gt; thanks for replying. i will try to use your code tomorow when i get to work.
gt;
gt; i would like to explain, however. I am new to Excel VBA. the word part
gt; didn't give errors. the excel part, was sometimes giving me errors. it may
gt; very well be that it had to do with open workspaces (or whatever their
gt; called). so perhaps it may be solved with the portion of code that you gave
gt; me.
gt;
gt; i would like to add to anybody reading this post, that for amatures, this
gt; code me a solution. as you may know, and i saw some posts on this, that
gt; reports maybe difficult to export. by exporting first to word, and then
gt; either modifying in word or in excel, one can achieve the correct output.
gt;
gt; thanks and good night,
gt;
gt; sam
gt;
gt; quot;Jim Conequot; wrote:
gt;
gt; gt; S,
gt; gt;
gt; gt; You don't not say what problems you are having so I am having to guess.
gt; gt; (quot;my car doesn't work, what's wrong with it?quot;)
gt; gt;
gt; gt; When automating Excel, you should set a reference to every object you refer to in Excel.
gt; gt; Then use the object references.
gt; gt; When quitting Excel, you must set each object reference to Nothing.
gt; gt; Not doing so can leave orphan references to Excel which prevent Excel from quitting.
gt; gt; You may also want to defer copying of the Word selection to just before you
gt; gt; paste it into Excel....
gt; gt; '------------------------------
gt; gt; Dim MyExcelInstance As Excel.Application
gt; gt; Dim MyWorkbook As Excel.Workbook
gt; gt; Dim MySheet As Excel.Worksheet
gt; gt;
gt; gt; Set MyExcelInstance = New Excel.Application
gt; gt; Set MyWorkbook = MyExcelInstance.Workbooks.Add
gt; gt; Set MySheet = MyWorkbook.Worksheets(1)
gt; gt; MyWorkbook.SaveAs quot;C:\PPPquot;
gt; gt; MySheet.Paste Destination:=MySheet.Range(quot;B5quot;) 'note MySheet used twice
gt; gt; MyWorkbook.Close SaveChanges:=True
gt; gt; Set MySheet = Nothing
gt; gt; Set MyWorkbook = Nothing
gt; gt; MyExcelInstance.Quit
gt; gt; Set MyExcelInstance = Nothing
gt; gt; '------------------------------------------
gt; gt;
gt; gt; Jim Cone
gt; gt; San Francisco, USA
gt; gt; www.realezsites.com/bus/primitivesoftware
gt; gt;
gt; gt;
gt; gt; quot;SAmquot; gt;
gt; gt; wrote in message
gt; gt;
gt; gt; Hi
gt; gt; i have never been to the excel boards. i am not sure if it is apprpriate to
gt; gt; post both on access and excel groups. so i will try now, and if it isn't
gt; gt; cool, please let me know.
gt; gt; i generate a report on access then i export it to word. then i copy it from
gt; gt; word and i am trying to paste it into excel. i am the lease familiar with vba
gt; gt; in excel. i am having problems with the excel end of things. the following is
gt; gt; my code:
gt; gt;
gt; gt; Code:
gt; gt; Set MyWordInstance = New Word.Application
gt; gt; MyWordInstance.Documents.Open quot;C:\tempRpt.rtfquot;
gt; gt; With MyWordInstance
gt; gt; .Selection.WholeStory
gt; gt; .Selection.Find.ClearFormatting
gt; gt; .Selection.Find.Replacement.ClearFormatting
gt; gt; With .Selection.Find
gt; gt; .Text = quot;^mquot;
gt; gt; .Replacement.Text = quot;quot;
gt; gt; .Forward = True
gt; gt; End With
gt; gt; .Selection.Find.Execute Replace:=wdReplaceAll
gt; gt; .Selection.WholeStory
gt; gt; .Selection.Copy
gt; gt; End With
gt; gt; MyWordInstance.ActiveDocument.Close
gt; gt; MyWordInstance.Quit
gt; gt; Kill quot;C:\tempPayperiodRpt.rtfquot;
gt; gt;
gt; gt; ' all of the above works fine, the following is giving me some problems
gt; gt;
gt; gt; Set MyExcelInstance = New Excel.Application
gt; gt; MyExcelInstance.Workbooks.Add
gt; gt; MyExcelInstance.ActiveWorkbook.SaveAs quot;C:\PPPquot;
gt; gt; MyExcelInstance.ActiveWorkbook.ActiveSheet.Range(quot; B5quot;).Select
gt; gt; ActiveSheet.Paste Destination:=Worksheets(quot;Sheet1quot;).Range(quot;B5quot;)
gt; gt; MyExcelInstance.ActiveWorkbook.Save
gt; gt; MyExcelInstance.ActiveWorkbook.Close
gt; gt; MyExcelInstance.Quit
gt; gt;
sam,
You are welcome. Appreciate getting the feedback.
Jim Cone
San Francisco, USA
www.realezsites.com/bus/primitivesoftwarequot;SAmquot; gt;
wrote in message
thanks, most have been the problem. i didn't close it correctly. now it works
fine, your script did it.
sam
quot;SAmquot; wrote:
gt; Hi Jim,
gt;
gt; thanks for replying. i will try to use your code tomorow when i get to work.
gt;
gt; i would like to explain, however. I am new to Excel VBA. the word part
gt; didn't give errors. the excel part, was sometimes giving me errors. it may
gt; very well be that it had to do with open workspaces (or whatever their
gt; called). so perhaps it may be solved with the portion of code that you gave
gt; me.
gt;
gt; i would like to add to anybody reading this post, that for amatures, this
gt; code me a solution. as you may know, and i saw some posts on this, that
gt; reports maybe difficult to export. by exporting first to word, and then
gt; either modifying in word or in excel, one can achieve the correct output.
gt;
gt; thanks and good night,
gt;
gt; sam
gt;
gt; quot;Jim Conequot; wrote:
gt;
gt; gt; S,
gt; gt;
gt; gt; You don't not say what problems you are having so I am having to guess.
gt; gt; (quot;my car doesn't work, what's wrong with it?quot;)
gt; gt;
gt; gt; When automating Excel, you should set a reference to every object you refer to in Excel.
gt; gt; Then use the object references.
gt; gt; When quitting Excel, you must set each object reference to Nothing.
gt; gt; Not doing so can leave orphan references to Excel which prevent Excel from quitting.
gt; gt; You may also want to defer copying of the Word selection to just before you
gt; gt; paste it into Excel....
gt; gt; '------------------------------
gt; gt; Dim MyExcelInstance As Excel.Application
gt; gt; Dim MyWorkbook As Excel.Workbook
gt; gt; Dim MySheet As Excel.Worksheet
gt; gt;
gt; gt; Set MyExcelInstance = New Excel.Application
gt; gt; Set MyWorkbook = MyExcelInstance.Workbooks.Add
gt; gt; Set MySheet = MyWorkbook.Worksheets(1)
gt; gt; MyWorkbook.SaveAs quot;C:\PPPquot;
gt; gt; MySheet.Paste Destination:=MySheet.Range(quot;B5quot;) 'note MySheet used twice
gt; gt; MyWorkbook.Close SaveChanges:=True
gt; gt; Set MySheet = Nothing
gt; gt; Set MyWorkbook = Nothing
gt; gt; MyExcelInstance.Quit
gt; gt; Set MyExcelInstance = Nothing
gt; gt; '------------------------------------------
gt; gt;
gt; gt; Jim Cone
gt; gt; San Francisco, USA
gt; gt; www.realezsites.com/bus/primitivesoftware
gt; gt;
gt; gt;
gt; gt; quot;SAmquot; gt;
gt; gt; wrote in message
gt; gt;
gt; gt; Hi
gt; gt; i have never been to the excel boards. i am not sure if it is apprpriate to
gt; gt; post both on access and excel groups. so i will try now, and if it isn't
gt; gt; cool, please let me know.
gt; gt; i generate a report on access then i export it to word. then i copy it from
gt; gt; word and i am trying to paste it into excel. i am the lease familiar with vba
gt; gt; in excel. i am having problems with the excel end of things. the following is
gt; gt; my code:
gt; gt;
gt; gt; Code:
gt; gt; Set MyWordInstance = New Word.Application
gt; gt; MyWordInstance.Documents.Open quot;C:\tempRpt.rtfquot;
gt; gt; With MyWordInstance
gt; gt; .Selection.WholeStory
gt; gt; .Selection.Find.ClearFormatting
gt; gt; .Selection.Find.Replacement.ClearFormatting
gt; gt; With .Selection.Find
gt; gt; .Text = quot;^mquot;
gt; gt; .Replacement.Text = quot;quot;
gt; gt; .Forward = True
gt; gt; End With
gt; gt; .Selection.Find.Execute Replace:=wdReplaceAll
gt; gt; .Selection.WholeStory
gt; gt; .Selection.Copy
gt; gt; End With
gt; gt; MyWordInstance.ActiveDocument.Close
gt; gt; MyWordInstance.Quit
gt; gt; Kill quot;C:\tempPayperiodRpt.rtfquot;
gt; gt;
gt; gt; ' all of the above works fine, the following is giving me some problems
gt; gt;
gt; gt; Set MyExcelInstance = New Excel.Application
gt; gt; MyExcelInstance.Workbooks.Add
gt; gt; MyExcelInstance.ActiveWorkbook.SaveAs quot;C:\PPPquot;
gt; gt; MyExcelInstance.ActiveWorkbook.ActiveSheet.Range(quot; B5quot;).Select
gt; gt; ActiveSheet.Paste Destination:=Worksheets(quot;Sheet1quot;).Range(quot;B5quot;)
gt; gt; MyExcelInstance.ActiveWorkbook.Save
gt; gt; MyExcelInstance.ActiveWorkbook.Close
gt; gt; MyExcelInstance.Quit
gt; gt;
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(quot;A2quot;).EntireRow.Select
Do While ActiveCell.Value lt;gt; quot;quot;
ActiveCell.EntireRow.Insert
ActiveCell.Offset(2, 0).EntireRow.Select
Loop
MyWorkbook.Save
MyWorkbook.Close SaveChanges:=True
Set MySheet = Nothing
quot;Jim Conequot; wrote:
gt; sam,
gt; You are welcome. Appreciate getting the feedback.
gt; Jim Cone
gt; San Francisco, USA
gt; www.realezsites.com/bus/primitivesoftware
gt;
gt;
gt; quot;SAmquot; gt;
gt; wrote in message
gt; thanks, most have been the problem. i didn't close it correctly. now it works
gt; fine, your script did it.
gt; sam
gt;
gt;
gt;
gt; quot;SAmquot; wrote:
gt; gt; Hi Jim,
gt; gt;
gt; gt; thanks for replying. i will try to use your code tomorow when i get to work.
gt; gt;
gt; gt; i would like to explain, however. I am new to Excel VBA. the word part
gt; gt; didn't give errors. the excel part, was sometimes giving me errors. it may
gt; gt; very well be that it had to do with open workspaces (or whatever their
gt; gt; called). so perhaps it may be solved with the portion of code that you gave
gt; gt; me.
gt; gt;
gt; gt; i would like to add to anybody reading this post, that for amatures, this
gt; gt; code me a solution. as you may know, and i saw some posts on this, that
gt; gt; reports maybe difficult to export. by exporting first to word, and then
gt; gt; either modifying in word or in excel, one can achieve the correct output.
gt; gt;
gt; gt; thanks and good night,
gt; gt;
gt; gt; sam
gt; gt;
gt; gt; quot;Jim Conequot; wrote:
gt; gt;
gt; gt; gt; S,
gt; gt; gt;
gt; gt; gt; You don't not say what problems you are having so I am having to guess.
gt; gt; gt; (quot;my car doesn't work, what's wrong with it?quot;)
gt; gt; gt;
gt; gt; gt; When automating Excel, you should set a reference to every object you refer to in Excel.
gt; gt; gt; Then use the object references.
gt; gt; gt; When quitting Excel, you must set each object reference to Nothing.
gt; gt; gt; Not doing so can leave orphan references to Excel which prevent Excel from quitting.
gt; gt; gt; You may also want to defer copying of the Word selection to just before you
gt; gt; gt; paste it into Excel....
gt; gt; gt; '------------------------------
gt; gt; gt; Dim MyExcelInstance As Excel.Application
gt; gt; gt; Dim MyWorkbook As Excel.Workbook
gt; gt; gt; Dim MySheet As Excel.Worksheet
gt; gt; gt;
gt; gt; gt; Set MyExcelInstance = New Excel.Application
gt; gt; gt; Set MyWorkbook = MyExcelInstance.Workbooks.Add
gt; gt; gt; Set MySheet = MyWorkbook.Worksheets(1)
gt; gt; gt; MyWorkbook.SaveAs quot;C:\PPPquot;
gt; gt; gt; MySheet.Paste Destination:=MySheet.Range(quot;B5quot;) 'note MySheet used twice
gt; gt; gt; MyWorkbook.Close SaveChanges:=True
gt; gt; gt; Set MySheet = Nothing
gt; gt; gt; Set MyWorkbook = Nothing
gt; gt; gt; MyExcelInstance.Quit
gt; gt; gt; Set MyExcelInstance = Nothing
gt; gt; gt; '------------------------------------------
gt; gt; gt;
gt; gt; gt; Jim Cone
gt; gt; gt; San Francisco, USA
gt; gt; gt; www.realezsites.com/bus/primitivesoftware
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;SAmquot; gt;
gt; gt; gt; wrote in message
gt; gt; gt;
gt; gt; gt; Hi
gt; gt; gt; i have never been to the excel boards. i am not sure if it is apprpriate to
gt; gt; gt; post both on access and excel groups. so i will try now, and if it isn't
gt; gt; gt; cool, please let me know.
gt; gt; gt; i generate a report on access then i export it to word. then i copy it from
gt; gt; gt; word and i am trying to paste it into excel. i am the lease familiar with vba
gt; gt; gt; in excel. i am having problems with the excel end of things. the following is
gt; gt; gt; my code:
gt; gt; gt;
gt; gt; gt; Code:
gt; gt; gt; Set MyWordInstance = New Word.Application
gt; gt; gt; MyWordInstance.Documents.Open quot;C:\tempRpt.rtfquot;
gt; gt; gt; With MyWordInstance
gt; gt; gt; .Selection.WholeStory
gt; gt; gt; .Selection.Find.ClearFormatting
gt; gt; gt; .Selection.Find.Replacement.ClearFormatting
gt; gt; gt; With .Selection.Find
gt; gt; gt; .Text = quot;^mquot;
gt; gt; gt; .Replacement.Text = quot;quot;
gt; gt; gt; .Forward = True
gt; gt; gt; End With
gt; gt; gt; .Selection.Find.Execute Replace:=wdReplaceAll
gt; gt; gt; .Selection.WholeStory
gt; gt; gt; .Selection.Copy
gt; gt; gt; End With
gt; gt; gt; MyWordInstance.ActiveDocument.Close
gt; gt; gt; MyWordInstance.Quit
gt; gt; gt; Kill quot;C:\tempPayperiodRpt.rtfquot;
gt; gt; gt;
gt; gt; gt; ' all of the above works fine, the following is giving me some problems
gt; gt; gt;
gt; gt; gt; Set MyExcelInstance = New Excel.Application
gt; gt; gt; MyExcelInstance.Workbooks.Add
gt; gt; gt; MyExcelInstance.ActiveWorkbook.SaveAs quot;C:\PPPquot;
gt; gt; gt; MyExcelInstance.ActiveWorkbook.ActiveSheet.Range(quot; B5quot;).Select
gt; gt; gt; ActiveSheet.Paste Destination:=Worksheets(quot;Sheet1quot;).Range(quot;B5quot;)
gt; gt; gt; MyExcelInstance.ActiveWorkbook.Save
gt; gt; gt; MyExcelInstance.ActiveWorkbook.Close
gt; gt; gt; MyExcelInstance.Quit
gt; gt; gt;
gt;
sam,
Don't use ActiveCell, Selection, UsedRange etc.
Use object references
Set MyCell = MySheet.Range(quot;A2quot;)
Do While MyCell.Value lt;gt; quot;quot;
MyCell.EntireRow.Insert
Set MyCell = MyCell.Offset(2, 0)
Loop
Jim Cone
San Francisco, USA
www.realezsites.com/bus/primitivesoftware
quot;SAmquot; gt;
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(quot;A2quot;).EntireRow.Select
Do While ActiveCell.Value lt;gt; quot;quot;
ActiveCell.EntireRow.Insert
ActiveCell.Offset(2, 0).EntireRow.Select
Loop
MyWorkbook.Save
MyWorkbook.Close SaveChanges:=True
Set MySheet = Nothing
quot;Jim Conequot; wrote:
gt; sam,
gt; You are welcome. Appreciate getting the feedback.
gt; Jim Cone
gt; San Francisco, USA
gt; www.realezsites.com/bus/primitivesoftware
gt;
gt;
gt; quot;SAmquot; gt;
gt; wrote in message
gt; thanks, most have been the problem. i didn't close it correctly. now it works
gt; fine, your script did it.
gt; sam
gt;
gt;
gt;
gt; quot;SAmquot; wrote:
gt; gt; Hi Jim,
gt; gt;
gt; gt; thanks for replying. i will try to use your code tomorow when i get to work.
gt; gt;
gt; gt; i would like to explain, however. I am new to Excel VBA. the word part
gt; gt; didn't give errors. the excel part, was sometimes giving me errors. it may
gt; gt; very well be that it had to do with open workspaces (or whatever their
gt; gt; called). so perhaps it may be solved with the portion of code that you gave
gt; gt; me.
gt; gt;
gt; gt; i would like to add to anybody reading this post, that for amatures, this
gt; gt; code me a solution. as you may know, and i saw some posts on this, that
gt; gt; reports maybe difficult to export. by exporting first to word, and then
gt; gt; either modifying in word or in excel, one can achieve the correct output.
gt; gt;
gt; gt; thanks and good night,
gt; gt;
gt; gt; sam
gt; gt;
gt; gt; quot;Jim Conequot; wrote:
gt; gt;
gt; gt; gt; S,
gt; gt; gt;
gt; gt; gt; You don't not say what problems you are having so I am having to guess.
gt; gt; gt; (quot;my car doesn't work, what's wrong with it?quot;)
gt; gt; gt;
gt; gt; gt; When automating Excel, you should set a reference to every object you refer to in Excel.
gt; gt; gt; Then use the object references.
gt; gt; gt; When quitting Excel, you must set each object reference to Nothing.
gt; gt; gt; Not doing so can leave orphan references to Excel which prevent Excel from quitting.
gt; gt; gt; You may also want to defer copying of the Word selection to just before you
gt; gt; gt; paste it into Excel....
gt; gt; gt; '------------------------------
gt; gt; gt; Dim MyExcelInstance As Excel.Application
gt; gt; gt; Dim MyWorkbook As Excel.Workbook
gt; gt; gt; Dim MySheet As Excel.Worksheet
gt; gt; gt;
gt; gt; gt; Set MyExcelInstance = New Excel.Application
gt; gt; gt; Set MyWorkbook = MyExcelInstance.Workbooks.Add
gt; gt; gt; Set MySheet = MyWorkbook.Worksheets(1)
gt; gt; gt; MyWorkbook.SaveAs quot;C:\PPPquot;
gt; gt; gt; MySheet.Paste Destination:=MySheet.Range(quot;B5quot;) 'note MySheet used twice
gt; gt; gt; MyWorkbook.Close SaveChanges:=True
gt; gt; gt; Set MySheet = Nothing
gt; gt; gt; Set MyWorkbook = Nothing
gt; gt; gt; MyExcelInstance.Quit
gt; gt; gt; Set MyExcelInstance = Nothing
gt; gt; gt; '------------------------------------------
gt; gt; gt;
gt; gt; gt; Jim Cone
gt; gt; gt; San Francisco, USA
gt; gt; gt; www.realezsites.com/bus/primitivesoftware
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;SAmquot; gt;
gt; gt; gt; wrote in message
gt; gt; gt;
gt; gt; gt; Hi
gt; gt; gt; i have never been to the excel boards. i am not sure if it is apprpriate to
gt; gt; gt; post both on access and excel groups. so i will try now, and if it isn't
gt; gt; gt; cool, please let me know.
gt; gt; gt; i generate a report on access then i export it to word. then i copy it from
gt; gt; gt; word and i am trying to paste it into excel. i am the lease familiar with vba
gt; gt; gt; in excel. i am having problems with the excel end of things. the following is
gt; gt; gt; my code:
gt; gt; gt;
gt; gt; gt; Code:
gt; gt; gt; Set MyWordInstance = New Word.Application
gt; gt; gt; MyWordInstance.Documents.Open quot;C:\tempRpt.rtfquot;
gt; gt; gt; With MyWordInstance
gt; gt; gt; .Selection.WholeStory
gt; gt; gt; .Selection.Find.ClearFormatting
gt; gt; gt; .Selection.Find.Replacement.ClearFormatting
gt; gt; gt; With .Selection.Find
gt; gt; gt; .Text = quot;^mquot;
gt; gt; gt; .Replacement.Text = quot;quot;
gt; gt; gt; .Forward = True
gt; gt; gt; End With
gt; gt; gt; .Selection.Find.Execute Replace:=wdReplaceAll
gt; gt; gt; .Selection.WholeStory
gt; gt; gt; .Selection.Copy
gt; gt; gt; End With
gt; gt; gt; MyWordInstance.ActiveDocument.Close
gt; gt; gt; MyWordInstance.Quit
gt; gt; gt; Kill quot;C:\tempPayperiodRpt.rtfquot;
gt; gt; gt;
gt; gt; gt; ' all of the above works fine, the following is giving me some problems
gt; gt; gt;
gt; gt; gt; Set MyExcelInstance = New Excel.Application
gt; gt; gt; MyExcelInstance.Workbooks.Add
gt; gt; gt; MyExcelInstance.ActiveWorkbook.SaveAs quot;C:\PPPquot;
gt; gt; gt; MyExcelInstance.ActiveWorkbook.ActiveSheet.Range(quot; B5quot;).Select
gt; gt; gt; ActiveSheet.Paste Destination:=Worksheets(quot;Sheet1quot;).Range(quot;B5quot;)
gt; gt; gt; MyExcelInstance.ActiveWorkbook.Save
gt; gt; gt; MyExcelInstance.ActiveWorkbook.Close
gt; gt; gt; MyExcelInstance.Quit
gt; gt; gt;
gt;
thanks again. it worked. i will try to brush up on my skills on vba in excel.
sam
quot;Jim Conequot; wrote:
gt; sam,
gt;
gt; Don't use ActiveCell, Selection, UsedRange etc.
gt; Use object references
gt;
gt; Set MyCell = MySheet.Range(quot;A2quot;)
gt;
gt; Do While MyCell.Value lt;gt; quot;quot;
gt; MyCell.EntireRow.Insert
gt; Set MyCell = MyCell.Offset(2, 0)
gt; Loop
gt;
gt; Jim Cone
gt; San Francisco, USA
gt; www.realezsites.com/bus/primitivesoftware
gt;
gt;
gt;
gt; quot;SAmquot; gt;
gt; wrote in message
gt; one more short one. i tried the following in excel, and it works. when i run
gt; from access, i am having some problems (i will paste only the part that you
gt; didn't give me).
gt;
gt; MyWorkbook.Save
gt; MySheet.Range(quot;A2quot;).EntireRow.Select
gt;
gt; Do While ActiveCell.Value lt;gt; quot;quot;
gt; ActiveCell.EntireRow.Insert
gt; ActiveCell.Offset(2, 0).EntireRow.Select
gt; Loop
gt; MyWorkbook.Save
gt; MyWorkbook.Close SaveChanges:=True
gt; Set MySheet = Nothing
gt;
gt;
gt;
gt;
gt; quot;Jim Conequot; wrote:
gt;
gt; gt; sam,
gt; gt; You are welcome. Appreciate getting the feedback.
gt; gt; Jim Cone
gt; gt; San Francisco, USA
gt; gt; www.realezsites.com/bus/primitivesoftware
gt; gt;
gt; gt;
gt; gt; quot;SAmquot; gt;
gt; gt; wrote in message
gt; gt; thanks, most have been the problem. i didn't close it correctly. now it works
gt; gt; fine, your script did it.
gt; gt; sam
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;SAmquot; wrote:
gt; gt; gt; Hi Jim,
gt; gt; gt;
gt; gt; gt; thanks for replying. i will try to use your code tomorow when i get to work.
gt; gt; gt;
gt; gt; gt; i would like to explain, however. I am new to Excel VBA. the word part
gt; gt; gt; didn't give errors. the excel part, was sometimes giving me errors. it may
gt; gt; gt; very well be that it had to do with open workspaces (or whatever their
gt; gt; gt; called). so perhaps it may be solved with the portion of code that you gave
gt; gt; gt; me.
gt; gt; gt;
gt; gt; gt; i would like to add to anybody reading this post, that for amatures, this
gt; gt; gt; code me a solution. as you may know, and i saw some posts on this, that
gt; gt; gt; reports maybe difficult to export. by exporting first to word, and then
gt; gt; gt; either modifying in word or in excel, one can achieve the correct output.
gt; gt; gt;
gt; gt; gt; thanks and good night,
gt; gt; gt;
gt; gt; gt; sam
gt; gt; gt;
gt; gt; gt; quot;Jim Conequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; S,
gt; gt; gt; gt;
gt; gt; gt; gt; You don't not say what problems you are having so I am having to guess.
gt; gt; gt; gt; (quot;my car doesn't work, what's wrong with it?quot;)
gt; gt; gt; gt;
gt; gt; gt; gt; When automating Excel, you should set a reference to every object you refer to in Excel.
gt; gt; gt; gt; Then use the object references.
gt; gt; gt; gt; When quitting Excel, you must set each object reference to Nothing.
gt; gt; gt; gt; Not doing so can leave orphan references to Excel which prevent Excel from quitting.
gt; gt; gt; gt; You may also want to defer copying of the Word selection to just before you
gt; gt; gt; gt; paste it into Excel....
gt; gt; gt; gt; '------------------------------
gt; gt; gt; gt; Dim MyExcelInstance As Excel.Application
gt; gt; gt; gt; Dim MyWorkbook As Excel.Workbook
gt; gt; gt; gt; Dim MySheet As Excel.Worksheet
gt; gt; gt; gt;
gt; gt; gt; gt; Set MyExcelInstance = New Excel.Application
gt; gt; gt; gt; Set MyWorkbook = MyExcelInstance.Workbooks.Add
gt; gt; gt; gt; Set MySheet = MyWorkbook.Worksheets(1)
gt; gt; gt; gt; MyWorkbook.SaveAs quot;C:\PPPquot;
gt; gt; gt; gt; MySheet.Paste Destination:=MySheet.Range(quot;B5quot;) 'note MySheet used twice
gt; gt; gt; gt; MyWorkbook.Close SaveChanges:=True
gt; gt; gt; gt; Set MySheet = Nothing
gt; gt; gt; gt; Set MyWorkbook = Nothing
gt; gt; gt; gt; MyExcelInstance.Quit
gt; gt; gt; gt; Set MyExcelInstance = Nothing
gt; gt; gt; gt; '------------------------------------------
gt; gt; gt; gt;
gt; gt; gt; gt; Jim Cone
gt; gt; gt; gt; San Francisco, USA
gt; gt; gt; gt; www.realezsites.com/bus/primitivesoftware
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;SAmquot; gt;
gt; gt; gt; gt; wrote in message
gt; gt; gt; gt;
gt; gt; gt; gt; Hi
gt; gt; gt; gt; i have never been to the excel boards. i am not sure if it is apprpriate to
gt; gt; gt; gt; post both on access and excel groups. so i will try now, and if it isn't
gt; gt; gt; gt; cool, please let me know.
gt; gt; gt; gt; i generate a report on access then i export it to word. then i copy it from
gt; gt; gt; gt; word and i am trying to paste it into excel. i am the lease familiar with vba
gt; gt; gt; gt; in excel. i am having problems with the excel end of things. the following is
gt; gt; gt; gt; my code:
gt; gt; gt; gt;
gt; gt; gt; gt; Code:
gt; gt; gt; gt; Set MyWordInstance = New Word.Application
gt; gt; gt; gt; MyWordInstance.Documents.Open quot;C:\tempRpt.rtfquot;
gt; gt; gt; gt; With MyWordInstance
gt; gt; gt; gt; .Selection.WholeStory
gt; gt; gt; gt; .Selection.Find.ClearFormatting
gt; gt; gt; gt; .Selection.Find.Replacement.ClearFormatting
gt; gt; gt; gt; With .Selection.Find
gt; gt; gt; gt; .Text = quot;^mquot;
gt; gt; gt; gt; .Replacement.Text = quot;quot;
gt; gt; gt; gt; .Forward = True
gt; gt; gt; gt; End With
gt; gt; gt; gt; .Selection.Find.Execute Replace:=wdReplaceAll
gt; gt; gt; gt; .Selection.WholeStory
gt; gt; gt; gt; .Selection.Copy
gt; gt; gt; gt; End With
gt; gt; gt; gt; MyWordInstance.ActiveDocument.Close
gt; gt; gt; gt; MyWordInstance.Quit
gt; gt; gt; gt; Kill quot;C:\tempPayperiodRpt.rtfquot;
gt; gt; gt; gt;
gt; gt; gt; gt; ' all of the above works fine, the following is giving me some problems
gt; gt; gt; gt;
gt; gt; gt; gt; Set MyExcelInstance = New Excel.Application
gt; gt; gt; gt; MyExcelInstance.Workbooks.Add
gt; gt; gt; gt; MyExcelInstance.ActiveWorkbook.SaveAs quot;C:\PPPquot;
gt; gt; gt; gt; MyExcelInstance.ActiveWorkbook.ActiveSheet.Range(quot; B5quot;).Select
gt; gt; gt; gt; ActiveSheet.Paste Destination:=Worksheets(quot;Sheet1quot;).Range(quot;B5quot;)
gt; gt; gt; gt; MyExcelInstance.ActiveWorkbook.Save
gt; gt; gt; gt; MyExcelInstance.ActiveWorkbook.Close
gt; gt; gt; gt; MyExcelInstance.Quit
gt; gt; gt; gt;
gt; gt;
gt;
留言列表
