| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Zipping the current Excel Spreadsheet with PkZip
Is there anyway to zip the current spreadsheet using the DOS version of
PkZip? I don't have WinZip, but I do have PkZip. I just want to save the worksheet with it's current name. Thanks! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
Hi David
If you use Win XP you can try http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Is there anyway to zip the current spreadsheet using the DOS version of PkZip? I don't have WinZip, but I do have PkZip. I just want to save the worksheet with it's current name. Thanks! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
I got a compile error at the NewZip (FileNameZip) line. Unexpected Sub,
Function, Property. What did I do wrong? Dim strDate As String, DefPath As String, sFName As String Dim oApp As Object, iCtr As Long Dim FName, vArr, FileNameZip DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip" 'Browse to the file(s), use the Ctrl key to select more files FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _ MultiSelect:=True, Title:="Select the files you want to zip") If IsArray(FName) = False Then 'do nothing Else 'Create empty Zip File NewZip (FileNameZip) 'Here is where the error pccurs. Set oApp = CreateObject("Shell.Application") "Ron de Bruin" wrote: Hi David If you use Win XP you can try http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Is there anyway to zip the current spreadsheet using the DOS version of PkZip? I don't have WinZip, but I do have PkZip. I just want to save the worksheet with it's current name. Thanks! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
Hi David
Copy the sub NewZip also in the module -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I got a compile error at the NewZip (FileNameZip) line. Unexpected Sub, Function, Property. What did I do wrong? Dim strDate As String, DefPath As String, sFName As String Dim oApp As Object, iCtr As Long Dim FName, vArr, FileNameZip DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip" 'Browse to the file(s), use the Ctrl key to select more files FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _ MultiSelect:=True, Title:="Select the files you want to zip") If IsArray(FName) = False Then 'do nothing Else 'Create empty Zip File NewZip (FileNameZip) 'Here is where the error pccurs. Set oApp = CreateObject("Shell.Application") "Ron de Bruin" wrote: Hi David If you use Win XP you can try http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Is there anyway to zip the current spreadsheet using the DOS version of PkZip? I don't have WinZip, but I do have PkZip. I just want to save the worksheet with it's current name. Thanks! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
I copied the sub and both functions. Works great!
Thanks much!! "Ron de Bruin" wrote: Hi David Copy the sub NewZip also in the module -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I got a compile error at the NewZip (FileNameZip) line. Unexpected Sub, Function, Property. What did I do wrong? Dim strDate As String, DefPath As String, sFName As String Dim oApp As Object, iCtr As Long Dim FName, vArr, FileNameZip DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip" 'Browse to the file(s), use the Ctrl key to select more files FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _ MultiSelect:=True, Title:="Select the files you want to zip") If IsArray(FName) = False Then 'do nothing Else 'Create empty Zip File NewZip (FileNameZip) 'Here is where the error pccurs. Set oApp = CreateObject("Shell.Application") "Ron de Bruin" wrote: Hi David If you use Win XP you can try http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Is there anyway to zip the current spreadsheet using the DOS version of PkZip? I don't have WinZip, but I do have PkZip. I just want to save the worksheet with it's current name. Thanks! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
Thanks for the feedback
I will add a example to zip the activeworkbook and mail it also this weekend -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I copied the sub and both functions. Works great! Thanks much!! "Ron de Bruin" wrote: Hi David Copy the sub NewZip also in the module -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I got a compile error at the NewZip (FileNameZip) line. Unexpected Sub, Function, Property. What did I do wrong? Dim strDate As String, DefPath As String, sFName As String Dim oApp As Object, iCtr As Long Dim FName, vArr, FileNameZip DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip" 'Browse to the file(s), use the Ctrl key to select more files FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _ MultiSelect:=True, Title:="Select the files you want to zip") If IsArray(FName) = False Then 'do nothing Else 'Create empty Zip File NewZip (FileNameZip) 'Here is where the error pccurs. Set oApp = CreateObject("Shell.Application") "Ron de Bruin" wrote: Hi David If you use Win XP you can try http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Is there anyway to zip the current spreadsheet using the DOS version of PkZip? I don't have WinZip, but I do have PkZip. I just want to save the worksheet with it's current name. Thanks! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
That would complete my project! Thanks again!
"Ron de Bruin" wrote: Thanks for the feedback I will add a example to zip the activeworkbook and mail it also this weekend -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I copied the sub and both functions. Works great! Thanks much!! "Ron de Bruin" wrote: Hi David Copy the sub NewZip also in the module -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I got a compile error at the NewZip (FileNameZip) line. Unexpected Sub, Function, Property. What did I do wrong? Dim strDate As String, DefPath As String, sFName As String Dim oApp As Object, iCtr As Long Dim FName, vArr, FileNameZip DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip" 'Browse to the file(s), use the Ctrl key to select more files FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _ MultiSelect:=True, Title:="Select the files you want to zip") If IsArray(FName) = False Then 'do nothing Else 'Create empty Zip File NewZip (FileNameZip) 'Here is where the error pccurs. Set oApp = CreateObject("Shell.Application") "Ron de Bruin" wrote: Hi David If you use Win XP you can try http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Is there anyway to zip the current spreadsheet using the DOS version of PkZip? I don't have WinZip, but I do have PkZip. I just want to save the worksheet with it's current name. Thanks! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
Well...I'm trying one more thing to completely automate the process. I have
created a 2nd workbook that is called from the 1st workbook that I ultimately want to zip. I write the variables I need to sheet1 and then reload those variables when the zip macro is run. I've modified your code to use the path and zip file name I want, but the one thing I can't make it do is automatically use the filename (SName) when it gets to the part where you open the file to zip. It looks like the code won't support inserting the filename like the SaveAsFile will. Could you look at this and let me know? Here is the code in the zip.xls worksheet: Dim strDate As String, DefPath As String, sFName As String Dim oApp As Object, iCtr As Long Dim FName, vArr, FileNameZip Dim ZName As String Dim SName As String Dim File_path As String Worksheets(1).Select ZName = Range("A1").Value 'contains the zip file name I want SName = Range("A2").Value 'contains the file name I want to zip File_path = Range("A3").Value 'contains the path of the both the file I want to zip and the target of the zip file I want to create. 'Original Code Goes to My Documents 'DefPath = Application.DefaultFilePath 'Sets path to where my files are located DefPath = File_path If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If 'Original Code to Set the Zip File Name 'strDate = Format(Now, " dd-mmm-yy h-mm-ss") 'FileNameZip = File_path & "MyFilesZip " & strDate & ".zip" 'I want to use my own zip name FileNameZip = DefPath & ZName 'Browse to the file(s), use the Ctrl key to select more files 'Original Code to set which file to zip FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _ MultiSelect:=True, Title:="Select the files you want to zip") (THIS IS WHERE I'M TRYING TO USE THE SNAME) 'I want automatically use my own filename FName = Application.GetOpenFilename(SName) If IsArray(FName) = False Then 'do nothing Else 'Create empty Zip File NewZip (FileNameZip) Set oApp = CreateObject("Shell.Application") For iCtr = LBound(FName) To UBound(FName) vArr = Split97(FName(iCtr), "\") sFName = vArr(UBound(vArr)) If bIsBookOpen(sFName) Then MsgBox "You can't zip a file that is open!" & vbLf & _ "Please close: " & FName(iCtr) Else 'Copy the file to the compressed folder oApp.Namespace(FileNameZip).CopyHere (FName(iCtr)) End If Next iCtr MsgBox "You will find your zipfile he " & FileNameZip Set oApp = Nothing End If End Sub Sub NewZip(sPath) 'Create empty Zip File 'keepITcool Dec-12-2005 If Len(Dir(sPath)) 0 Then Kill sPath Open sPath For Output As #1 Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0) Close #1 End Sub Function bIsBookOpen(ByRef szBookName As String) As Boolean ' Rob Bovey On Error Resume Next bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing) End Function Function Split97(sStr As Variant, sdelim As String) As Variant 'Tom Ogilvy Split97 = Evaluate("{""" & _ Application.Substitute(sStr, sdelim, """,""") & """}") End Function "Ron de Bruin" wrote: Thanks for the feedback I will add a example to zip the activeworkbook and mail it also this weekend -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I copied the sub and both functions. Works great! Thanks much!! "Ron de Bruin" wrote: Hi David Copy the sub NewZip also in the module -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I got a compile error at the NewZip (FileNameZip) line. Unexpected Sub, Function, Property. What did I do wrong? Dim strDate As String, DefPath As String, sFName As String Dim oApp As Object, iCtr As Long Dim FName, vArr, FileNameZip DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip" 'Browse to the file(s), use the Ctrl key to select more files FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _ MultiSelect:=True, Title:="Select the files you want to zip") If IsArray(FName) = False Then 'do nothing Else 'Create empty Zip File NewZip (FileNameZip) 'Here is where the error pccurs. Set oApp = CreateObject("Shell.Application") "Ron de Bruin" wrote: Hi David If you use Win XP you can try http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Is there anyway to zip the current spreadsheet using the DOS version of PkZip? I don't have WinZip, but I do have PkZip. I just want to save the worksheet with it's current name. Thanks! |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
Hi David
I must go to a party this evening. I update the site tomorrow and look at your problem -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... That would complete my project! Thanks again! "Ron de Bruin" wrote: Thanks for the feedback I will add a example to zip the activeworkbook and mail it also this weekend -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I copied the sub and both functions. Works great! Thanks much!! "Ron de Bruin" wrote: Hi David Copy the sub NewZip also in the module -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I got a compile error at the NewZip (FileNameZip) line. Unexpected Sub, Function, Property. What did I do wrong? Dim strDate As String, DefPath As String, sFName As String Dim oApp As Object, iCtr As Long Dim FName, vArr, FileNameZip DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip" 'Browse to the file(s), use the Ctrl key to select more files FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _ MultiSelect:=True, Title:="Select the files you want to zip") If IsArray(FName) = False Then 'do nothing Else 'Create empty Zip File NewZip (FileNameZip) 'Here is where the error pccurs. Set oApp = CreateObject("Shell.Application") "Ron de Bruin" wrote: Hi David If you use Win XP you can try http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Is there anyway to zip the current spreadsheet using the DOS version of PkZip? I don't have WinZip, but I do have PkZip. I just want to save the worksheet with it's current name. Thanks! |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
Try the mail example on
http://www.rondebruin.nl/windowsxpzip.htm#mail I go to bed now -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi David I must go to a party this evening. I update the site tomorrow and look at your problem -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... That would complete my project! Thanks again! "Ron de Bruin" wrote: Thanks for the feedback I will add a example to zip the activeworkbook and mail it also this weekend -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I copied the sub and both functions. Works great! Thanks much!! "Ron de Bruin" wrote: Hi David Copy the sub NewZip also in the module -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I got a compile error at the NewZip (FileNameZip) line. Unexpected Sub, Function, Property. What did I do wrong? Dim strDate As String, DefPath As String, sFName As String Dim oApp As Object, iCtr As Long Dim FName, vArr, FileNameZip DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip" 'Browse to the file(s), use the Ctrl key to select more files FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _ MultiSelect:=True, Title:="Select the files you want to zip") If IsArray(FName) = False Then 'do nothing Else 'Create empty Zip File NewZip (FileNameZip) 'Here is where the error pccurs. Set oApp = CreateObject("Shell.Application") "Ron de Bruin" wrote: Hi David If you use Win XP you can try http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Is there anyway to zip the current spreadsheet using the DOS version of PkZip? I don't have WinZip, but I do have PkZip. I just want to save the worksheet with it's current name. Thanks! |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
I have newly installed Microsoft Office. Now, launching a file, sa Personal.xls (containing macros) from Excel startup folder, trigger off the error message: MODULE NOT FOUND. The file consequently neve gets open. Can someone familiar with this problem help me with solution? Thanks Myle -- Myle ----------------------------------------------------------------------- Myles's Profile: http://www.excelforum.com/member.php...fo&userid=2874 View this thread: http://www.excelforum.com/showthread.php?threadid=49431 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
I used the first part of the file you suggested and it's working fine. Was
hoping to delete the existing file...only keeping the zip file, but just can't be done from within the workbook. Thanks! "Ron de Bruin" wrote: Try the mail example on http://www.rondebruin.nl/windowsxpzip.htm#mail I go to bed now -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi David I must go to a party this evening. I update the site tomorrow and look at your problem -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... That would complete my project! Thanks again! "Ron de Bruin" wrote: Thanks for the feedback I will add a example to zip the activeworkbook and mail it also this weekend -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I copied the sub and both functions. Works great! Thanks much!! "Ron de Bruin" wrote: Hi David Copy the sub NewZip also in the module -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I got a compile error at the NewZip (FileNameZip) line. Unexpected Sub, Function, Property. What did I do wrong? Dim strDate As String, DefPath As String, sFName As String Dim oApp As Object, iCtr As Long Dim FName, vArr, FileNameZip DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip" 'Browse to the file(s), use the Ctrl key to select more files FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _ MultiSelect:=True, Title:="Select the files you want to zip") If IsArray(FName) = False Then 'do nothing Else 'Create empty Zip File NewZip (FileNameZip) 'Here is where the error pccurs. Set oApp = CreateObject("Shell.Application") "Ron de Bruin" wrote: Hi David If you use Win XP you can try http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Is there anyway to zip the current spreadsheet using the DOS version of PkZip? I don't have WinZip, but I do have PkZip. I just want to save the worksheet with it's current name. Thanks! |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
Do you want to zip the activeworkbook and delete the activeworkbook then ?
-- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I used the first part of the file you suggested and it's working fine. Was hoping to delete the existing file...only keeping the zip file, but just can't be done from within the workbook. Thanks! "Ron de Bruin" wrote: Try the mail example on http://www.rondebruin.nl/windowsxpzip.htm#mail I go to bed now -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi David I must go to a party this evening. I update the site tomorrow and look at your problem -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... That would complete my project! Thanks again! "Ron de Bruin" wrote: Thanks for the feedback I will add a example to zip the activeworkbook and mail it also this weekend -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I copied the sub and both functions. Works great! Thanks much!! "Ron de Bruin" wrote: Hi David Copy the sub NewZip also in the module -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I got a compile error at the NewZip (FileNameZip) line. Unexpected Sub, Function, Property. What did I do wrong? Dim strDate As String, DefPath As String, sFName As String Dim oApp As Object, iCtr As Long Dim FName, vArr, FileNameZip DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip" 'Browse to the file(s), use the Ctrl key to select more files FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _ MultiSelect:=True, Title:="Select the files you want to zip") If IsArray(FName) = False Then 'do nothing Else 'Create empty Zip File NewZip (FileNameZip) 'Here is where the error pccurs. Set oApp = CreateObject("Shell.Application") "Ron de Bruin" wrote: Hi David If you use Win XP you can try http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Is there anyway to zip the current spreadsheet using the DOS version of PkZip? I don't have WinZip, but I do have PkZip. I just want to save the worksheet with it's current name. Thanks! |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
Yes....No need to have both
"Ron de Bruin" wrote: Do you want to zip the activeworkbook and delete the activeworkbook then ? -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I used the first part of the file you suggested and it's working fine. Was hoping to delete the existing file...only keeping the zip file, but just can't be done from within the workbook. Thanks! "Ron de Bruin" wrote: Try the mail example on http://www.rondebruin.nl/windowsxpzip.htm#mail I go to bed now -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi David I must go to a party this evening. I update the site tomorrow and look at your problem -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... That would complete my project! Thanks again! "Ron de Bruin" wrote: Thanks for the feedback I will add a example to zip the activeworkbook and mail it also this weekend -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I copied the sub and both functions. Works great! Thanks much!! "Ron de Bruin" wrote: Hi David Copy the sub NewZip also in the module -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I got a compile error at the NewZip (FileNameZip) line. Unexpected Sub, Function, Property. What did I do wrong? Dim strDate As String, DefPath As String, sFName As String Dim oApp As Object, iCtr As Long Dim FName, vArr, FileNameZip DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip" 'Browse to the file(s), use the Ctrl key to select more files FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _ MultiSelect:=True, Title:="Select the files you want to zip") If IsArray(FName) = False Then 'do nothing Else 'Create empty Zip File NewZip (FileNameZip) 'Here is where the error pccurs. Set oApp = CreateObject("Shell.Application") "Ron de Bruin" wrote: Hi David If you use Win XP you can try http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Is there anyway to zip the current spreadsheet using the DOS version of PkZip? I don't have WinZip, but I do have PkZip. I just want to save the worksheet with it's current name. Thanks! |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
Hi David
Ok, Try this Sub Zip_ActiveWorkbook_And_Delete_ActiveWorkbook() Dim strDate As String, DefPath As String, strbody As String Dim oApp As Object Dim FileNameZip, FileNameXls DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If 'Create date/time string and the temporary xls file and zip file name strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & strDate & ".zip" FileNameXls = DefPath & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & strDate & ".xls" If Dir(FileNameZip) = "" And Dir(FileNameXls) = "" Then 'Make copy of the activeworkbook ActiveWorkbook.SaveCopyAs FileNameXls 'Create empty Zip File NewZip (FileNameZip) 'Copy the file in the compressed folder Set oApp = CreateObject("Shell.Application") oApp.NameSpace(FileNameZip).CopyHere FileNameXls 'Keep script waiting until Compressing is done On Error Resume Next Do Until oApp.NameSpace(FileNameZip).items.Count = 1 Application.Wait (Now + TimeValue("0:00:01")) Loop On Error GoTo 0 Set oApp = Nothing 'Delete the temporary xls file Kill FileNameXls MsgBox "You find the zipfile he " & FileNameZip 'Delete the activeworkbook With ActiveWorkbook .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Else MsgBox "FileNameZip or/and FileNameXls exist" End If End Sub Sub NewZip(sPath) 'Create empty Zip File 'keepITcool Dec-12-2005 If Len(Dir(sPath)) 0 Then Kill sPath Open sPath For Output As #1 Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0) Close #1 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Yes....No need to have both "Ron de Bruin" wrote: Do you want to zip the activeworkbook and delete the activeworkbook then ? -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I used the first part of the file you suggested and it's working fine. Was hoping to delete the existing file...only keeping the zip file, but just can't be done from within the workbook. Thanks! "Ron de Bruin" wrote: Try the mail example on http://www.rondebruin.nl/windowsxpzip.htm#mail I go to bed now -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi David I must go to a party this evening. I update the site tomorrow and look at your problem -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... That would complete my project! Thanks again! "Ron de Bruin" wrote: Thanks for the feedback I will add a example to zip the activeworkbook and mail it also this weekend -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I copied the sub and both functions. Works great! Thanks much!! "Ron de Bruin" wrote: Hi David Copy the sub NewZip also in the module -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I got a compile error at the NewZip (FileNameZip) line. Unexpected Sub, Function, Property. What did I do wrong? Dim strDate As String, DefPath As String, sFName As String Dim oApp As Object, iCtr As Long Dim FName, vArr, FileNameZip DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip" 'Browse to the file(s), use the Ctrl key to select more files FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _ MultiSelect:=True, Title:="Select the files you want to zip") If IsArray(FName) = False Then 'do nothing Else 'Create empty Zip File NewZip (FileNameZip) 'Here is where the error pccurs. Set oApp = CreateObject("Shell.Application") "Ron de Bruin" wrote: Hi David If you use Win XP you can try http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Is there anyway to zip the current spreadsheet using the DOS version of PkZip? I don't have WinZip, but I do have PkZip. I just want to save the worksheet with it's current name. Thanks! |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
That's exactly what I needed. You should post this up on your site!
Thanks again! "Ron de Bruin" wrote: Hi David Ok, Try this Sub Zip_ActiveWorkbook_And_Delete_ActiveWorkbook() Dim strDate As String, DefPath As String, strbody As String Dim oApp As Object Dim FileNameZip, FileNameXls DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If 'Create date/time string and the temporary xls file and zip file name strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & strDate & ".zip" FileNameXls = DefPath & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & strDate & ".xls" If Dir(FileNameZip) = "" And Dir(FileNameXls) = "" Then 'Make copy of the activeworkbook ActiveWorkbook.SaveCopyAs FileNameXls 'Create empty Zip File NewZip (FileNameZip) 'Copy the file in the compressed folder Set oApp = CreateObject("Shell.Application") oApp.NameSpace(FileNameZip).CopyHere FileNameXls 'Keep script waiting until Compressing is done On Error Resume Next Do Until oApp.NameSpace(FileNameZip).items.Count = 1 Application.Wait (Now + TimeValue("0:00:01")) Loop On Error GoTo 0 Set oApp = Nothing 'Delete the temporary xls file Kill FileNameXls MsgBox "You find the zipfile he " & FileNameZip 'Delete the activeworkbook With ActiveWorkbook .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Else MsgBox "FileNameZip or/and FileNameXls exist" End If End Sub Sub NewZip(sPath) 'Create empty Zip File 'keepITcool Dec-12-2005 If Len(Dir(sPath)) 0 Then Kill sPath Open sPath For Output As #1 Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0) Close #1 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Yes....No need to have both "Ron de Bruin" wrote: Do you want to zip the activeworkbook and delete the activeworkbook then ? -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I used the first part of the file you suggested and it's working fine. Was hoping to delete the existing file...only keeping the zip file, but just can't be done from within the workbook. Thanks! "Ron de Bruin" wrote: Try the mail example on http://www.rondebruin.nl/windowsxpzip.htm#mail I go to bed now -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi David I must go to a party this evening. I update the site tomorrow and look at your problem -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... That would complete my project! Thanks again! "Ron de Bruin" wrote: Thanks for the feedback I will add a example to zip the activeworkbook and mail it also this weekend -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I copied the sub and both functions. Works great! Thanks much!! "Ron de Bruin" wrote: Hi David Copy the sub NewZip also in the module -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I got a compile error at the NewZip (FileNameZip) line. Unexpected Sub, Function, Property. What did I do wrong? Dim strDate As String, DefPath As String, sFName As String Dim oApp As Object, iCtr As Long Dim FName, vArr, FileNameZip DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip" 'Browse to the file(s), use the Ctrl key to select more files FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _ MultiSelect:=True, Title:="Select the files you want to zip") If IsArray(FName) = False Then 'do nothing Else 'Create empty Zip File NewZip (FileNameZip) 'Here is where the error pccurs. Set oApp = CreateObject("Shell.Application") "Ron de Bruin" wrote: Hi David If you use Win XP you can try http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Is there anyway to zip the current spreadsheet using the DOS version of PkZip? I don't have WinZip, but I do have PkZip. I just want to save the worksheet with it's current name. Thanks! |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
Hi David
Thanks for the feedback -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... That's exactly what I needed. You should post this up on your site! Thanks again! "Ron de Bruin" wrote: Hi David Ok, Try this Sub Zip_ActiveWorkbook_And_Delete_ActiveWorkbook() Dim strDate As String, DefPath As String, strbody As String Dim oApp As Object Dim FileNameZip, FileNameXls DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If 'Create date/time string and the temporary xls file and zip file name strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & strDate & ".zip" FileNameXls = DefPath & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & strDate & ".xls" If Dir(FileNameZip) = "" And Dir(FileNameXls) = "" Then 'Make copy of the activeworkbook ActiveWorkbook.SaveCopyAs FileNameXls 'Create empty Zip File NewZip (FileNameZip) 'Copy the file in the compressed folder Set oApp = CreateObject("Shell.Application") oApp.NameSpace(FileNameZip).CopyHere FileNameXls 'Keep script waiting until Compressing is done On Error Resume Next Do Until oApp.NameSpace(FileNameZip).items.Count = 1 Application.Wait (Now + TimeValue("0:00:01")) Loop On Error GoTo 0 Set oApp = Nothing 'Delete the temporary xls file Kill FileNameXls MsgBox "You find the zipfile he " & FileNameZip 'Delete the activeworkbook With ActiveWorkbook .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Else MsgBox "FileNameZip or/and FileNameXls exist" End If End Sub Sub NewZip(sPath) 'Create empty Zip File 'keepITcool Dec-12-2005 If Len(Dir(sPath)) 0 Then Kill sPath Open sPath For Output As #1 Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0) Close #1 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Yes....No need to have both "Ron de Bruin" wrote: Do you want to zip the activeworkbook and delete the activeworkbook then ? -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I used the first part of the file you suggested and it's working fine. Was hoping to delete the existing file...only keeping the zip file, but just can't be done from within the workbook. Thanks! "Ron de Bruin" wrote: Try the mail example on http://www.rondebruin.nl/windowsxpzip.htm#mail I go to bed now -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi David I must go to a party this evening. I update the site tomorrow and look at your problem -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... That would complete my project! Thanks again! "Ron de Bruin" wrote: Thanks for the feedback I will add a example to zip the activeworkbook and mail it also this weekend -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I copied the sub and both functions. Works great! Thanks much!! "Ron de Bruin" wrote: Hi David Copy the sub NewZip also in the module -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I got a compile error at the NewZip (FileNameZip) line. Unexpected Sub, Function, Property. What did I do wrong? Dim strDate As String, DefPath As String, sFName As String Dim oApp As Object, iCtr As Long Dim FName, vArr, FileNameZip DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip" 'Browse to the file(s), use the Ctrl key to select more files FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _ MultiSelect:=True, Title:="Select the files you want to zip") If IsArray(FName) = False Then 'do nothing Else 'Create empty Zip File NewZip (FileNameZip) 'Here is where the error pccurs. Set oApp = CreateObject("Shell.Application") "Ron de Bruin" wrote: Hi David If you use Win XP you can try http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Is there anyway to zip the current spreadsheet using the DOS version of PkZip? I don't have WinZip, but I do have PkZip. I just want to save the worksheet with it's current name. Thanks! |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
I'm no expert, but instead of putting it in the personal.xls, but it in the
ThisWorkbook module. Alt 11 to start the marco editor, CTRL+R to see the Project Viewer and look for the last module under your workbook. You didn't include any code, not did you post your question in the right place. Try again if this doesn't help. "Myles" wrote: I have newly installed Microsoft Office. Now, launching a file, say Personal.xls (containing macros) from Excel startup folder, triggers off the error message: MODULE NOT FOUND. The file consequently never gets open. Can someone familiar with this problem help me with a solution? Thanks Myles -- Myles ------------------------------------------------------------------------ Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746 View this thread: http://www.excelforum.com/showthread...hreadid=494316 |
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
Hi Dante
Is the code from my site working correct for you ? http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "dantee" wrote in message ... Hi Ron. I was trying to modify the code below (to compress a file & email it -- the zipped file -- yet keep it and the original file in the same folder) and it would stop and give me the following error: Run-time error €˜91: Object variable or With block variable not set. The line Set oApp = CreateObject("Shell.Application") 'SETS oApp to nothing goes from oApp = Nothing to not even showing any value at all after it is executed (hovering over oApp), and oApp.Namespace(FileNameZip).CopyHere FileNameXls give me the error message. Can you, or anyone, help me understand why it would do that? Thx, Dante Encinas "Ron de Bruin" wrote: Hi David Ok, Try this Sub Zip_ActiveWorkbook_And_Delete_ActiveWorkbook() Dim strDate As String, DefPath As String, strbody As String Dim oApp As Object Dim FileNameZip, FileNameXls DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If 'Create date/time string and the temporary xls file and zip file name strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & strDate & ".zip" FileNameXls = DefPath & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & strDate & ".xls" If Dir(FileNameZip) = "" And Dir(FileNameXls) = "" Then 'Make copy of the activeworkbook ActiveWorkbook.SaveCopyAs FileNameXls 'Create empty Zip File NewZip (FileNameZip) 'Copy the file in the compressed folder Set oApp = CreateObject("Shell.Application") oApp.NameSpace(FileNameZip).CopyHere FileNameXls 'Keep script waiting until Compressing is done On Error Resume Next Do Until oApp.NameSpace(FileNameZip).items.Count = 1 Application.Wait (Now + TimeValue("0:00:01")) Loop On Error GoTo 0 Set oApp = Nothing 'Delete the temporary xls file Kill FileNameXls MsgBox "You find the zipfile he " & FileNameZip 'Delete the activeworkbook With ActiveWorkbook .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Else MsgBox "FileNameZip or/and FileNameXls exist" End If End Sub Sub NewZip(sPath) 'Create empty Zip File 'keepITcool Dec-12-2005 If Len(Dir(sPath)) 0 Then Kill sPath Open sPath For Output As #1 Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0) Close #1 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Yes....No need to have both "Ron de Bruin" wrote: Do you want to zip the activeworkbook and delete the activeworkbook then ? -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I used the first part of the file you suggested and it's working fine. Was hoping to delete the existing file...only keeping the zip file, but just can't be done from within the workbook. Thanks! "Ron de Bruin" wrote: Try the mail example on http://www.rondebruin.nl/windowsxpzip.htm#mail I go to bed now -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi David I must go to a party this evening. I update the site tomorrow and look at your problem -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... That would complete my project! Thanks again! "Ron de Bruin" wrote: Thanks for the feedback I will add a example to zip the activeworkbook and mail it also this weekend -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I copied the sub and both functions. Works great! Thanks much!! "Ron de Bruin" wrote: Hi David Copy the sub NewZip also in the module -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I got a compile error at the NewZip (FileNameZip) line. Unexpected Sub, Function, Property. What did I do wrong? Dim strDate As String, DefPath As String, sFName As String Dim oApp As Object, iCtr As Long Dim FName, vArr, FileNameZip DefPath = Application.DefaultFilePath If Right(DefPath, 1) < "\" Then DefPath = DefPath & "\" End If strDate = Format(Now, " dd-mmm-yy h-mm-ss") FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip" 'Browse to the file(s), use the Ctrl key to select more files FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _ MultiSelect:=True, Title:="Select the files you want to zip") If IsArray(FName) = False Then 'do nothing Else 'Create empty Zip File NewZip (FileNameZip) 'Here is where the error pccurs. Set oApp = CreateObject("Shell.Application") "Ron de Bruin" wrote: Hi David If you use Win XP you can try http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Is there anyway to zip the current spreadsheet using the DOS version of PkZip? I don't have WinZip, but I do have PkZip. I just want to save the worksheet with it's current name. Thanks! |
|
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
Hi Ron.
No, it is not. I'm trying something simpler... just emailing the contents on a spreadsheet worksheet, and your code makes the email the but is failing to put the contents of the sheet on the email body... ..HTMLBody = RangetoHTML(rng) doesn't seem to be working (I did copy the RangetoHTML function into the same module as the macro). Perhaps the problem may be my settings in Outlook? Any help would be great. Here is your code, which I am using: Sub Mail_Sheet_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object With Application .EnableEvents = False .ScreenUpdating = False End With Set rng = Nothing Set rng = ActiveSheet.UsedRange 'You can also use a sheet name 'Set rng = Sheets("YourSheet").UsedRange Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = " .CC = "" .BCC = "" .Subject = "This is the Subject line" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Sincerely, Dante "Ron de Bruin" wrote: Hi Dante Is the code from my site working correct for you ? http://www.rondebruin.nl/windowsxpzip.htm |
|
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
Download the workbook (body) example from my mail page
Let me know if this is working or not http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "dantee" wrote in message ... Hi Ron. No, it is not. I'm trying something simpler... just emailing the contents on a spreadsheet worksheet, and your code makes the email the but is failing to put the contents of the sheet on the email body... .HTMLBody = RangetoHTML(rng) doesn't seem to be working (I did copy the RangetoHTML function into the same module as the macro). Perhaps the problem may be my settings in Outlook? Any help would be great. Here is your code, which I am using: Sub Mail_Sheet_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object With Application .EnableEvents = False .ScreenUpdating = False End With Set rng = Nothing Set rng = ActiveSheet.UsedRange 'You can also use a sheet name 'Set rng = Sheets("YourSheet").UsedRange Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = " .CC = "" .BCC = "" .Subject = "This is the Subject line" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Sincerely, Dante "Ron de Bruin" wrote: Hi Dante Is the code from my site working correct for you ? http://www.rondebruin.nl/windowsxpzip.htm |
|
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
Hi again.
I downloaded the Outlook Object Model (Body) example workbook. Got stuck/Debug at Set OutMail = OutApp.CreateItem(0) error message was new to me... "Run-time error '-2147287035 (80030005)': You don't have appropriate permission to perform this operation." Dante "Ron de Bruin" wrote: Download the workbook (body) example from my mail page Let me know if this is working or not http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "dantee" wrote in message ... Hi Ron. No, it is not. I'm trying something simpler... just emailing the contents on a spreadsheet worksheet, and your code makes the email the but is failing to put the contents of the sheet on the email body... .HTMLBody = RangetoHTML(rng) doesn't seem to be working (I did copy the RangetoHTML function into the same module as the macro). Perhaps the problem may be my settings in Outlook? Any help would be great. Here is your code, which I am using: Sub Mail_Sheet_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object With Application .EnableEvents = False .ScreenUpdating = False End With Set rng = Nothing Set rng = ActiveSheet.UsedRange 'You can also use a sheet name 'Set rng = Sheets("YourSheet").UsedRange Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = " .CC = "" .BCC = "" .Subject = "This is the Subject line" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Sincerely, Dante "Ron de Bruin" wrote: Hi Dante Is the code from my site working correct for you ? http://www.rondebruin.nl/windowsxpzip.htm |
|
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
Ron...
One of the programmers here helped me... told me a little trick that makes a big difference. He showed me that I actually needed to extract the Excel file from the zip file to allows the code to work properly. Just opening the Excel file was not yielding the proper results. Now it is. I'm going to go ahead and modify it for my purposes and see if I don't get stuck somewhere again. Thanks for posting all this great time-saving code! Dante "Ron de Bruin" wrote: Download the workbook (body) example from my mail page Let me know if this is working or not http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "dantee" wrote in message ... Hi Ron. No, it is not. I'm trying something simpler... just emailing the contents on a spreadsheet worksheet, and your code makes the email the but is failing to put the contents of the sheet on the email body... .HTMLBody = RangetoHTML(rng) doesn't seem to be working (I did copy the RangetoHTML function into the same module as the macro). Perhaps the problem may be my settings in Outlook? Any help would be great. Here is your code, which I am using: Sub Mail_Sheet_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object With Application .EnableEvents = False .ScreenUpdating = False End With Set rng = Nothing Set rng = ActiveSheet.UsedRange 'You can also use a sheet name 'Set rng = Sheets("YourSheet").UsedRange Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = " .CC = "" .BCC = "" .Subject = "This is the Subject line" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Sincerely, Dante "Ron de Bruin" wrote: Hi Dante Is the code from my site working correct for you ? http://www.rondebruin.nl/windowsxpzip.htm |
|
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Zipping the current Excel Spreadsheet with PkZip
Ahaa, you are not the only one that make that mistake <g
Good luck and if you need help post back -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "dantee" wrote in message ... Ron... One of the programmers here helped me... told me a little trick that makes a big difference. He showed me that I actually needed to extract the Excel file from the zip file to allows the code to work properly. Just opening the Excel file was not yielding the proper results. Now it is. I'm going to go ahead and modify it for my purposes and see if I don't get stuck somewhere again. Thanks for posting all this great time-saving code! Dante "Ron de Bruin" wrote: Download the workbook (body) example from my mail page Let me know if this is working or not http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "dantee" wrote in message ... Hi Ron. No, it is not. I'm trying something simpler... just emailing the contents on a spreadsheet worksheet, and your code makes the email the but is failing to put the contents of the sheet on the email body... .HTMLBody = RangetoHTML(rng) doesn't seem to be working (I did copy the RangetoHTML function into the same module as the macro). Perhaps the problem may be my settings in Outlook? Any help would be great. Here is your code, which I am using: Sub Mail_Sheet_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object With Application .EnableEvents = False .ScreenUpdating = False End With Set rng = Nothing Set rng = ActiveSheet.UsedRange 'You can also use a sheet name 'Set rng = Sheets("YourSheet").UsedRange Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = " .CC = "" .BCC = "" .Subject = "This is the Subject line" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Sincerely, Dante "Ron de Bruin" wrote: Hi Dante Is the code from my site working correct for you ? http://www.rondebruin.nl/windowsxpzip.htm |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Zipping my Excel Files and maintain the folder structure | Excel Discussion (Misc queries) | |||
| Can Excel automatically enter the current date in a spreadsheet? | Excel Discussion (Misc queries) | |||
| How can I reduce the size of my Excel file without zipping it? | Excel Discussion (Misc queries) | |||
| Zipping Excel files | Excel Programming | |||
| How to open another Excel spreadsheet to copy data into current spreadsheet ? | Excel Programming | |||
Linear Mode
