hockeyfan21
04-21-2010, 09:38 AM
I am so close to getting this to work but hoping someone here can help me figure out a couple of issues. This VBA is in a .xlsm macro enabled Excel 2007 file. I am trying to do a SaveAs and save it as a .xls, removing the data connections, names and module in the .xls file only.
Attempt 1 - This will save it with the .xls extension but the file type has not changed from .xlsm, causing error/warning messages when opening the new file.
Attempt 2 - I think this is what I want but, for some reason after executing the line after this one "set wkb = ..." The new .xls file opens but my code completely stops running. No errors, just quits.
Sub SaveAndBreak()
Dim strSaveAs As String
Dim strFile As String
Dim strFolder As String
Dim wkb As Workbook
Dim nmName As Name
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
strFolder = ThisWorkbook.Path & "\"
strFile = "RGAC_OGAC" & "_" & Format(Date, "mm_dd_yyyy") & ".xls"
strSaveAs = strFolder & strFile
Attempt1) ThisWorkbook.SaveCopyAs strSaveAs
Attempt2) 'ThisWorkbook.SaveAs Filename:=strSaveAs, FileFormat:=xlExcel8
Set wkb = Workbooks.Open(strSaveAs, False)
For Each nmName In wkb.Names
wkb.Names(nmName.Name).Delete
Next
wkb.Connections("RGAC_OGAC").Delete
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Module1")
VBProj.VBComponents.Remove VBComp
Application.DisplayAlerts = False
wkb.Close True
Set wkb = Nothing
MsgBox strFile & " now saved"
End Sub
I would appreciate any help you could give me with this. I have tried many different examples of this fileformat code I've found in searches but can't get one to work.
Attempt 1 - This will save it with the .xls extension but the file type has not changed from .xlsm, causing error/warning messages when opening the new file.
Attempt 2 - I think this is what I want but, for some reason after executing the line after this one "set wkb = ..." The new .xls file opens but my code completely stops running. No errors, just quits.
Sub SaveAndBreak()
Dim strSaveAs As String
Dim strFile As String
Dim strFolder As String
Dim wkb As Workbook
Dim nmName As Name
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
strFolder = ThisWorkbook.Path & "\"
strFile = "RGAC_OGAC" & "_" & Format(Date, "mm_dd_yyyy") & ".xls"
strSaveAs = strFolder & strFile
Attempt1) ThisWorkbook.SaveCopyAs strSaveAs
Attempt2) 'ThisWorkbook.SaveAs Filename:=strSaveAs, FileFormat:=xlExcel8
Set wkb = Workbooks.Open(strSaveAs, False)
For Each nmName In wkb.Names
wkb.Names(nmName.Name).Delete
Next
wkb.Connections("RGAC_OGAC").Delete
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Module1")
VBProj.VBComponents.Remove VBComp
Application.DisplayAlerts = False
wkb.Close True
Set wkb = Nothing
MsgBox strFile & " now saved"
End Sub
I would appreciate any help you could give me with this. I have tried many different examples of this fileformat code I've found in searches but can't get one to work.