I have written code to automatically open, rename, and save embedded workbooks in an excel spreadsheet to a file. I will post the code below. For some reason, all of the code works, except when I hit the ".saveas" command. If the files are saved in the same folder as the workbook with the embedded files, then it will save all the files. However, if I save the files at in a subfolder, the saveas command will run without error, but the document will never show up in the folder. Am I missing something? Code is posted below.
Code:Sub SaveExistingExcelDoc() Dim MyDir As String Dim strPath As String Dim xlApp As Excel.Application Dim xlDoc As Excel.Workbook Dim windowCt As Integer Dim objct As OLEObject Dim DocName As String Dim FinalDest2 As String MyDir = ActiveWorkbook.Path CheckDir = MyDir On Error Resume Next a = 1 Worksheets(a).Activate Do While a <= Worksheets.Count For Each objct In ActiveSheet.OLEObjects ActiveSheet.Shapes(objct).Select objtype = objct.progID If objct.TopLeftCell.Column = "3" Then docTitle1 = Cells(objct.TopLeftCell.Row, 1).Value docTitle2 = "Touchpoint" 'MsgBox (docTitle1 & " " & docTitle2) DestFldr = "\Sample Touchpoint" ElseIf objct.TopLeftCell.Column = "19" Then docTitle1 = Cells(objct.TopLeftCell.Row, 1).Value docTitle2 = "Internal Performance" 'MsgBox (docTitle1 & " " & docTitle2) DestFldr = "\Internal Perf Reports" ElseIf objct.TopLeftCell.Column = "21" Then docTitle1 = Cells(objct.TopLeftCell.Row, 1).Value docTitle2 = "Competitive Intelligence" 'MsgBox (docTitle1 & " " & docTitle2) DestFldr = "\Comp Intel Reports" ElseIf objct.TopLeftCell.Column = "23" Then docTitle1 = Cells(objct.TopLeftCell.Row, 1).Value docTitle2 = "Consituent Opinions" 'MsgBox (docTitle1 & " " & docTitle2) DestFldr = "\Constituent Opinions" End If strPath = MyDir & DestFldr DocName = docTitle1 & " " & docTitle2 Select Case objct.progID Case "Excel.Sheet.8" Selection.Verb Verb:=xlOpen Set xlDoc = ActiveWorkbook GoTo Line29: If xlApp Is Nothing Then Set xlApp = GetObject("Excel.Application") xlApp.Visible = True End If On Error GoTo 0 On Error Resume Next If xlApp.Windows.Count > 0 Then Set xlDoc = xlApp.ActiveWorkbook Else Set xlDoc = xlApp.Documents.Add End If On Error GoTo 0 Line29: With xlDoc MsgBox xlDoc.Name 'strTitle = InputBox("what should the file be named?", FileSaveAs, "Test") 'strTitle = "THIS IS A TEST2" '.SaveAs (MyDir & "\" & DocName & ".xls") MsgBox strPath MsgBox strPath & "- " & DocName .SaveAs Filename:=(strPath & "\" & DocName & ".xls"), FileFormat:=xlNormal, Password:="", WriteResPassword:="password", ReadOnlyRecommended:=True_, CreateBackup:=False .Close End With 'xlApp.Quit ' Clean up Set xlDoc = Nothing 'Set WrdApp = Nothing End Select Next a = a + 1 TheBottom: If a > Worksheets.Count Then MsgBox "Done Processing" Exit Sub End If Worksheets(a).Activate Loop End Sub
Last edited by mcledavid; 09-29-2009 at 01:17 PM. Reason: solved
I have solved the issue. I didn't realize that there is a limit of 218 characters for the folder path structure and document title when you are saving a document. So I have now put in an input box which alerts the user that they have exceeded the maximum allowable characters, and they must shorten the length of the file before saving.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks