Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting
Hello,
I have a spreadsheet that I need to export selected data to a file. It can be a spreadsheet. My file is over 1mb and I just need to export the actual values of a particular range to a backup file. Any ideas? Ernst. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting
Dim sh as Worksheet
Dim rng as Range Dim varr as Variant On Error Resume Next Kill "C:\Backup\MyData.xls" On Error goto 0 ActiveWorkbook.SaveCopyAs Filename:="C:\Backup\Mydata.xls" workbooks.Open "C:\Backup\MyData.xls" for each sh in ActiveWorkbook.Worksheets if sh.Name < "Data" then application.DisplayAlerts = False sh.Delete Application.DisplayAlerts = True end if Next with worksheets("Data") set rng = .Range("A1").CurrentRegion vArr = rng.value .Usedrange.Clearcontents rng.Value = varr End With Activeworkbook.Close Savechanges:=True -- Regards, Tom Ogilvy "Ernst Guckel" wrote in message ... Hello, I have a spreadsheet that I need to export selected data to a file. It can be a spreadsheet. My file is over 1mb and I just need to export the actual values of a particular range to a backup file. Any ideas? Ernst. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting
Dim sh as Worksheet
Dim rng as Range Dim varr as Variant On Error Resume Next Kill "C:\Backup\MyData.xls" On Error goto 0 ActiveWorkbook.SaveCopyAs Filename:="C:\Backup\Mydata.xls" workbooks.Open "C:\Backup\MyData.xls" for each sh in ActiveWorkbook.Worksheets if sh.Name < "Data" then application.DisplayAlerts = False sh.Delete Application.DisplayAlerts = True end if Next This works great. with worksheets("Data") set rng = .Range("A1").CurrentRegion vArr = rng.value .Usedrange.Clearcontents rng.Value = varr End With Activeworkbook.Close Savechanges:=True but I don't understand what the last bit of code is to accomplish... Ernst. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting
How about saving the values of a 'Selected Range' within a worksheet?
Ernst. You said you needed to: "export the actual values of a particular range to a backup file. " So I assumed the particular range is on a sheet that contains more data than you want to save. I gave this as an example - the code assumes the particular range starts in A1 and is a contiguous rectangle offset from the other data at least by one completely blank row and column. If that isn't the case, then you don't need that part. If you want to save a single sheet On Error Resume Next Kill "C:\Backup\MyData.xls" On Error goto 0 ActiveSheet.Copy Activeworkbook.Save Filename:="C:\Backup\Mydata.xls" Acitveworkbook.Close SaveChanges:=False would be all you need. -- Regards, Tom Ogilvy "Ernst Guckel" wrote in message ... Dim sh as Worksheet Dim rng as Range Dim varr as Variant On Error Resume Next Kill "C:\Backup\MyData.xls" On Error goto 0 ActiveWorkbook.SaveCopyAs Filename:="C:\Backup\Mydata.xls" workbooks.Open "C:\Backup\MyData.xls" for each sh in ActiveWorkbook.Worksheets if sh.Name < "Data" then application.DisplayAlerts = False sh.Delete Application.DisplayAlerts = True end if Next This works great. with worksheets("Data") set rng = .Range("A1").CurrentRegion vArr = rng.value .Usedrange.Clearcontents rng.Value = varr End With Activeworkbook.Close Savechanges:=True but I don't understand what the last bit of code is to accomplish... Ernst. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
exporting | Excel Discussion (Misc queries) | |||
Exporting | Excel Discussion (Misc queries) | |||
Exporting | Excel Discussion (Misc queries) | |||
exporting | Excel Discussion (Misc queries) | |||
exporting to CSV | Excel Worksheet Functions |