| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting to several xls files
Hi ng,
I have a problem, i have 15 different xls files and one master, i want to create a macro that when you push it, it exports range A1:A5 to the master xls (example file1.xls exports range A1:A5 to master.xls range A1:A5, file2.xls exports range A1:A5 to master.xls range B1:B5 and so on), also the macro should save the file at the same time. It could be when you leave the file, but the user should not have a possibility to say no. I hope you understand my problem and can help me, regards, Ole |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Exporting to several xls files
You can use the Beforeclose event.
Chip Pearson has general information on Events: http://www:cpearson.com/excel/vbe.htm in the event you would have something like Dim bClose as Boolean Dim bk as workbooks On Error Resume Next set bk = workbooks("Master.xls") On Error goto 0 if bk is nothing then bClose = True set bk = Workbooks.Open("C:\Myfolder\Master.xls") end if bk.worksheets(1).Range("C1:C5").Value = worksheets(1).Range("A1:A5").Value bk.Save if bClose then bk.Close Savechanges:=False End if Application.EnableEvents = False thisworkbook.Save Application.EnableEvents = True -- Regards, Tom Ogilvy "ole_" wrote in message ... Hi ng, I have a problem, i have 15 different xls files and one master, i want to create a macro that when you push it, it exports range A1:A5 to the master xls (example file1.xls exports range A1:A5 to master.xls range A1:A5, file2.xls exports range A1:A5 to master.xls range B1:B5 and so on), also the macro should save the file at the same time. It could be when you leave the file, but the user should not have a possibility to say no. I hope you understand my problem and can help me, regards, Ole |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Exporting to several xls files
"Tom Ogilvy" skrev i en meddelelse ... You can use the Beforeclose event. Chip Pearson has general information on Events: http://www:cpearson.com/excel/vbe.htm in the event you would have something like Dim bClose as Boolean Dim bk as workbooks On Error Resume Next set bk = workbooks("Master.xls") On Error goto 0 if bk is nothing then bClose = True set bk = Workbooks.Open("C:\Myfolder\Master.xls") end if bk.worksheets(1).Range("C1:C5").Value = worksheets(1).Range("A1:A5").Value bk.Save if bClose then bk.Close Savechanges:=False End if Application.EnableEvents = False thisworkbook.Save Application.EnableEvents = True Hi Tom, I have tried to put it in "this workbook", but it failed at: bk.worksheets(1).Range("C1:C5").Value = Is it me there is doing anything wrong? Regards, Ole |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Exporting to several xls files
Ole,
The problem is likely cause by line breaks in Tom's otherwise correct reply. The code bk.worksheets(1).Range("C1:C5").Value = worksheets(1).Range("A1:A5").Value should be on a single line of code in the editor. Or, you can use the line continuation character to split it in to two lines: bk.worksheets(1).Range("C1:C5").Value = _ worksheets(1).Range("A1:A5").Value -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "ole_" wrote in message ... "Tom Ogilvy" skrev i en meddelelse ... You can use the Beforeclose event. Chip Pearson has general information on Events: http://www:cpearson.com/excel/vbe.htm in the event you would have something like Dim bClose as Boolean Dim bk as workbooks On Error Resume Next set bk = workbooks("Master.xls") On Error goto 0 if bk is nothing then bClose = True set bk = Workbooks.Open("C:\Myfolder\Master.xls") end if bk.worksheets(1).Range("C1:C5").Value = worksheets(1).Range("A1:A5").Value bk.Save if bClose then bk.Close Savechanges:=False End if Application.EnableEvents = False thisworkbook.Save Application.EnableEvents = True Hi Tom, I have tried to put it in "this workbook", but it failed at: bk.worksheets(1).Range("C1:C5").Value = Is it me there is doing anything wrong? Regards, Ole |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Exporting to several xls files
"Chip Pearson" skrev i en meddelelse ... Ole, The problem is likely cause by line breaks in Tom's otherwise correct reply. The code bk.worksheets(1).Range("C1:C5").Value = worksheets(1).Range("A1:A5").Value should be on a single line of code in the editor. Or, you can use the line continuation character to split it in to two lines: bk.worksheets(1).Range("C1:C5").Value = _ worksheets(1).Range("A1:A5").Value -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com Hi Chip, Now i get another error: "Compile error: ethod or data member not found" and then ".worksheets" in bk.worksheets(1).Range("C1:C5").Value = _ is highlighted, here is what i have so far: Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim bClose As Boolean Dim bk As Workbooks On Error Resume Next Set bk = Workbooks("Master.xls") On Error GoTo 0 If bk Is Nothing Then bClose = True Set bk = Workbooks.Open("C:\Master.xls") End If bk.Worksheets(1).Range("C1:C5").Value = _ Worksheets(1).Range("A1:A5").Value bk.Save If bClose Then bk.Close Savechanges:=False End If Application.EnableEvents = False ThisWorkbook.Save Application.EnableEvents = True End Sub And something else i have been wondering, shouldent the master.xls open?? Regards, Ole |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Exporting to several xls files
Ole,
bk should be declared as Workbook (singular) not Workbooks (plural). Dim bk As Workbook -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "ole_" wrote in message ... "Chip Pearson" skrev i en meddelelse ... Ole, The problem is likely cause by line breaks in Tom's otherwise correct reply. The code bk.worksheets(1).Range("C1:C5").Value = worksheets(1).Range("A1:A5").Value should be on a single line of code in the editor. Or, you can use the line continuation character to split it in to two lines: bk.worksheets(1).Range("C1:C5").Value = _ worksheets(1).Range("A1:A5").Value -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com Hi Chip, Now i get another error: "Compile error: ethod or data member not found" and then ".worksheets" in bk.worksheets(1).Range("C1:C5").Value = _ is highlighted, here is what i have so far: Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim bClose As Boolean Dim bk As Workbooks On Error Resume Next Set bk = Workbooks("Master.xls") On Error GoTo 0 If bk Is Nothing Then bClose = True Set bk = Workbooks.Open("C:\Master.xls") End If bk.Worksheets(1).Range("C1:C5").Value = _ Worksheets(1).Range("A1:A5").Value bk.Save If bClose Then bk.Close Savechanges:=False End If Application.EnableEvents = False ThisWorkbook.Save Application.EnableEvents = True End Sub And something else i have been wondering, shouldent the master.xls open?? Regards, Ole |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Exporting to several xls files
Chip and Tom
Thanks a lot, its working just like i hoped. Many thanks, Ole "Chip Pearson" skrev i en meddelelse ... Ole, bk should be declared as Workbook (singular) not Workbooks (plural). Dim bk As Workbook -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "ole_" wrote in message ... "Chip Pearson" skrev i en meddelelse ... Ole, The problem is likely cause by line breaks in Tom's otherwise correct reply. The code bk.worksheets(1).Range("C1:C5").Value = worksheets(1).Range("A1:A5").Value should be on a single line of code in the editor. Or, you can use the line continuation character to split it in to two lines: bk.worksheets(1).Range("C1:C5").Value = _ worksheets(1).Range("A1:A5").Value -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com Hi Chip, Now i get another error: "Compile error: ethod or data member not found" and then ".worksheets" in bk.worksheets(1).Range("C1:C5").Value = _ is highlighted, here is what i have so far: Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim bClose As Boolean Dim bk As Workbooks On Error Resume Next Set bk = Workbooks("Master.xls") On Error GoTo 0 If bk Is Nothing Then bClose = True Set bk = Workbooks.Open("C:\Master.xls") End If bk.Worksheets(1).Range("C1:C5").Value = _ Worksheets(1).Range("A1:A5").Value bk.Save If bClose Then bk.Close Savechanges:=False End If Application.EnableEvents = False ThisWorkbook.Save Application.EnableEvents = True End Sub And something else i have been wondering, shouldent the master.xls open?? Regards, Ole |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Exporting to seperate .ini files | Excel Discussion (Misc queries) | |||
| EXPORTING EXCEL FILES | New Users to Excel | |||
| exporting pdf files to excel | New Users to Excel | |||
| Exporting delimited files | Excel Worksheet Functions | |||
| Exporting CSV files | Excel Programming | |||
Linear Mode
