Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
automation from access into excel
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
automation from access into excel
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" 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
automation from access into excel
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 "Jim Cone" wrote: 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" 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
automation from access into excel
thanks, most have been the problem. i didn't close it correctly. now it works
fine, your script did it. sam "SAm" wrote: 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 "Jim Cone" wrote: 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" 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
automation from access into excel
sam,
You are welcome. Appreciate getting the feedback. Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "SAm" wrote in message thanks, most have been the problem. i didn't close it correctly. now it works fine, your script did it. sam "SAm" wrote: 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 "Jim Cone" wrote: 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" 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
automation from access into excel
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" wrote: sam, You are welcome. Appreciate getting the feedback. Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "SAm" wrote in message thanks, most have been the problem. i didn't close it correctly. now it works fine, your script did it. sam "SAm" wrote: 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 "Jim Cone" wrote: 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" 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
automation from access into excel
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" 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 "Jim Cone" wrote: sam, You are welcome. Appreciate getting the feedback. Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "SAm" wrote in message thanks, most have been the problem. i didn't close it correctly. now it works fine, your script did it. sam "SAm" wrote: 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 "Jim Cone" wrote: 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" 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
automation from access into excel
thanks again. it worked. i will try to brush up on my skills on vba in excel.
sam "Jim Cone" wrote: 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" 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 "Jim Cone" wrote: sam, You are welcome. Appreciate getting the feedback. Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "SAm" wrote in message thanks, most have been the problem. i didn't close it correctly. now it works fine, your script did it. sam "SAm" wrote: 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 "Jim Cone" wrote: 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Financial modelling from Excel to Access | Excel Discussion (Misc queries) | |||
excel to access problem | Excel Discussion (Misc queries) | |||
Access a excel file through FTP without write protection | Excel Discussion (Misc queries) | |||
Excel won't open - pasting from Access | Excel Discussion (Misc queries) | |||
Changing the format of an Excel output file made by Microsoft Access | Excel Discussion (Misc queries) |