Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlsm SaveAs xls
Excel 2007 Win 7 64-bit
With VBA I'm trying to save an xlsm file as an xls file. It didn't work. That is, after saving, the xls file does not exist and the active file is the starting xlsm file. I recorded a macro as I did the SaveAs and got the code (after modifying the recorded code): ActiveWorkbook.SaveAs Filename:= _ ThePath & "\" & WbName & ".xls", FileFormat:=xlExcel8 where WbName is "Anything". Again, the active file is the xlsm file and the Anything.xls file does not exist. How do I code this? Thanks for your time. Otto |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlsm SaveAs xls
Otto
Using....................... Sub saver() Dim Wbname As String Wbname = "Anything" ActiveWorkbook.SaveAs Filename:= _ ThePath & "\" & Wbname & ".xls", FileFormat:=xlExcel8 End Sub Anything.xls winds up in C:\ Maybe change ThePath to ActiveWorkbook.Path Gord Dibben MS Excel MVP On Fri, 11 Dec 2009 13:42:24 -0500, "Otto Moehrbach" wrote: Excel 2007 Win 7 64-bit With VBA I'm trying to save an xlsm file as an xls file. It didn't work. That is, after saving, the xls file does not exist and the active file is the starting xlsm file. I recorded a macro as I did the SaveAs and got the code (after modifying the recorded code): ActiveWorkbook.SaveAs Filename:= _ ThePath & "\" & WbName & ".xls", FileFormat:=xlExcel8 where WbName is "Anything". Again, the active file is the xlsm file and the Anything.xls file does not exist. How do I code this? Thanks for your time. Otto |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlsm SaveAs xls
Otto Moehrbach wrote:
Excel 2007 Win 7 64-bit With VBA I'm trying to save an xlsm file as an xls file. It didn't work. That is, after saving, the xls file does not exist and the active file is the starting xlsm file. I recorded a macro as I did the SaveAs and got the code (after modifying the recorded code): ActiveWorkbook.SaveAs Filename:= _ ThePath & "\" & WbName & ".xls", FileFormat:=xlExcel8 where WbName is "Anything". Again, the active file is the xlsm file and the Anything.xls file does not exist. How do I code this? Thanks for your time. Otto It works for me check if ThePath & "\" & WbName & ".xls" gives you valid path and you have access to write there. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlsm SaveAs xls
Thanks Gord. ThePath is ActiveWorkbook.Path.
Your suggested code is identical to mine. I used yours and got the same result. The new file, gord.xls, doesn't exist and the active file is the original xlsm file. Gord, I started remarking out lines and remarked out the DisplayAlerts=False line. I ran the below code and up popped a Msgbox telling me that the original xlsm file was already open and did I want to open it again. I clicked NO and the code stopped and never reached the SaveAs line. Apparently NO is the default on that MsgBox and the SaveAs line was never reached. Notice the MsgBox after the Loop. The code never reaches that Msgbox. Why is the loop trying to open the original xlsm file? Thanks for your time. Otto Sub xlsmToxls() Call GetWbName If CancelA = True Then Exit Sub ThePath = ThisWorkbook.Path Set Dest = Range("A8") ChDir ThePath TheFile = Dir("*.xls") Application.ScreenUpdating = False Do Set wb = Workbooks.Open(ThePath & "\" & TheFile) Sheets("Report For Supv").Range("A8").EntireRow.Copy Dest.EntireRow.PasteSpecial xlPasteValues Application.CutCopyMode = False Set Dest = Dest.Offset(1) wb.Close TheFile = Dir Loop While TheFile < "" MsgBox ActiveWorkbook.Name ActiveWorkbook.SaveAs Filename:= _ ThePath & "\" & WbName & ".xls", FileFormat:=xlExcel8 Application.ScreenUpdating = True End Sub "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Otto Using....................... Sub saver() Dim Wbname As String Wbname = "Anything" ActiveWorkbook.SaveAs Filename:= _ ThePath & "\" & Wbname & ".xls", FileFormat:=xlExcel8 End Sub Anything.xls winds up in C:\ Maybe change ThePath to ActiveWorkbook.Path Gord Dibben MS Excel MVP On Fri, 11 Dec 2009 13:42:24 -0500, "Otto Moehrbach" wrote: Excel 2007 Win 7 64-bit With VBA I'm trying to save an xlsm file as an xls file. It didn't work. That is, after saving, the xls file does not exist and the active file is the starting xlsm file. I recorded a macro as I did the SaveAs and got the code (after modifying the recorded code): ActiveWorkbook.SaveAs Filename:= _ ThePath & "\" & WbName & ".xls", FileFormat:=xlExcel8 where WbName is "Anything". Again, the active file is the xlsm file and the Anything.xls file does not exist. How do I code this? Thanks for your time. Otto |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlsm SaveAs xls
Gord
I figured it out. ThisWorkbook is an .xlsm wb and is in the same folder as the 5 .xls files that the code opens and closes. What was happening was that the code was trying to open that .xlsm file, which was already open. The "DisplayAlerts=False" line prevented the display advising the above, so I never saw it. The code stopped cold at that point. Apparently the line TheFile = Dir("*.xls") picks up any file that has "xls" after the period and .xlsm has that. Of course that was never a problem until 2007 came out. I trapped it with an IF statement that TheFile=ActiveWorkbook.Name and all works good now. I thought you might want to know. Otto "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Otto Using....................... Sub saver() Dim Wbname As String Wbname = "Anything" ActiveWorkbook.SaveAs Filename:= _ ThePath & "\" & Wbname & ".xls", FileFormat:=xlExcel8 End Sub Anything.xls winds up in C:\ Maybe change ThePath to ActiveWorkbook.Path Gord Dibben MS Excel MVP On Fri, 11 Dec 2009 13:42:24 -0500, "Otto Moehrbach" wrote: Excel 2007 Win 7 64-bit With VBA I'm trying to save an xlsm file as an xls file. It didn't work. That is, after saving, the xls file does not exist and the active file is the starting xlsm file. I recorded a macro as I did the SaveAs and got the code (after modifying the recorded code): ActiveWorkbook.SaveAs Filename:= _ ThePath & "\" & WbName & ".xls", FileFormat:=xlExcel8 where WbName is "Anything". Again, the active file is the xlsm file and the Anything.xls file does not exist. How do I code this? Thanks for your time. Otto |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlsm SaveAs xls
Otto
I never got back to you earlier but I ran the macro(after remming Call GetWbname)............I missed you posting that macro? I got message about file already being opened. There is no "displayalerts" lines in the macro you posted. Assume it is set in GetWbname code. Thanks for the update. Gord On Sat, 12 Dec 2009 10:00:58 -0500, "Otto Moehrbach" wrote: Gord I figured it out. ThisWorkbook is an .xlsm wb and is in the same folder as the 5 .xls files that the code opens and closes. What was happening was that the code was trying to open that .xlsm file, which was already open. The "DisplayAlerts=False" line prevented the display advising the above, so I never saw it. The code stopped cold at that point. Apparently the line TheFile = Dir("*.xls") picks up any file that has "xls" after the period and .xlsm has that. Of course that was never a problem until 2007 came out. I trapped it with an IF statement that TheFile=ActiveWorkbook.Name and all works good now. I thought you might want to know. Otto "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Otto Using....................... Sub saver() Dim Wbname As String Wbname = "Anything" ActiveWorkbook.SaveAs Filename:= _ ThePath & "\" & Wbname & ".xls", FileFormat:=xlExcel8 End Sub Anything.xls winds up in C:\ Maybe change ThePath to ActiveWorkbook.Path Gord Dibben MS Excel MVP On Fri, 11 Dec 2009 13:42:24 -0500, "Otto Moehrbach" wrote: Excel 2007 Win 7 64-bit With VBA I'm trying to save an xlsm file as an xls file. It didn't work. That is, after saving, the xls file does not exist and the active file is the starting xlsm file. I recorded a macro as I did the SaveAs and got the code (after modifying the recorded code): ActiveWorkbook.SaveAs Filename:= _ ThePath & "\" & WbName & ".xls", FileFormat:=xlExcel8 where WbName is "Anything". Again, the active file is the xlsm file and the Anything.xls file does not exist. How do I code this? Thanks for your time. Otto |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
linking XLSM to XLS | Setting up and Configuration of Excel | |||
SaveAs - - provide option to Save As .xlsm or .xls | Excel Programming | |||
saveas ActiveWorkbook.SaveAs Filename:=Range("A1").Value | Excel Programming | |||
SaveAs XLSM question | Excel Programming | |||
xlsm | Excel Programming |