![]() |
|
![]() |
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
![]() Hi there, I'm not even sure if this is possible, but I am asked to loop through all folders and subfolders for every instance of a folder called "Cannot Upload". In that folder there will be old format ".doc" files that need to be converted to ".docx". They then want these converted docx files to be saved to a new folder within the current Cannot Upload folder called "Converted Files". I tried everything I know (although admittedly, that isn't a lot) and can get the code to work for one file, but it won't on the rest AND I don't know how to loop through subfolders. Any help would be greatly appreciated! |
#2
|
|||
|
|||
![]()
This is what I have so far. It's not great, but it kind of works. The user has to select the folder where the documents are stored, the macro will loop through the folder and convert any documents that are in .doc format. I'm not sure if there are any that are in .docx format in these folders, so I thought I would build an exit if it encountered any and errored on the convert code.
Ultimately, I would like to: 1. Be able to loop through all folders and subfolders if possible. The folders specifically that contains the documents to be converted is called "Cannot Upload", but that same folder name could be a subfolder in multiple folders. 2. It would be awesome if I could save the converted documents to another folder called "Converted Files". Code:
Sub Loop_AllWordFiles_inFolder() 'Optimize Macro Speed Application.ScreenUpdating = False Dim strDocNm As String, strFolder As String, strFile As String, wdDoc As Document Dim sFolderPath As String 'Get form user where the path to the folder is strFolder = GetFolder If strFolder = "" Then Exit Sub 'strFile = Dir(strFolder & "\Cannot Upload" & "*.doc", vbNormal) strFile = Dir(strFolder & "\*.doc", vbNormal) strDocNm = ThisDocument.FullName aFile = strFolder While strFile <> "" If strFolder & "" & strFile <> strDocNm Then Set wdDoc = Documents.Open(FileName:=strFolder & "" & strFile, AddToRecentFiles:=False, Visible:=True) With wdDoc Call ConvertDoc(wdDoc) .Close End With End If strFile = Dir() Wend MsgBox "Finished scanning all files in Folder " & Path Set wdDoc = Nothing Application.ScreenUpdating = True End Sub Function GetFolder() As String Dim oFolder As Object GetFolder = "" Set oFolder = CreateObject("Shell.Application").browseforfolder(0, "Choose a folder", 0) If (Not oFolder Is Nothing) Then GetFolder = oFolder.items.Item.Path Set oFolder = Nothing End Function Sub ConvertDoc(wdDoc As Document) 'macro created to open an old .doc file and convert to new word file With wdDoc On Error GoTo lbl_exit .Convert .SaveAs2 ActiveDocument.Path & "" & wdDoc & "x", fileformat:=wdFormatDocumentDefault End With lbl_exit: Exit Sub End Sub |
#3
|
||||
|
||||
![]()
Hi MurphyMom,
You say the code you have is working to some extent, but what you've posted is code for PCs whereas your user profile says you're using a Mac with OS X. Please clarify.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
![]()
Hi Paul,
I usually program and test on my PC. I find it easier than on the Mac because I have been working on a PC for so long. Usually, I found that the code works on the Mac as well, but if not, we do have access to PC's if needed. I just started with a company that uses Macs instead of PC's so my learning curve has been high ![]() |
#5
|
||||
|
||||
![]()
Try the PC code in https://www.msofficeforums.com/word-...-doc-docx.html. All you need do with that code is change:
FileName:=.FullName to: FileName:=strFolder & "\Cannot Upload\Converted Files\" & .Name assuming, of course, your own code's reference to 'strFolder & "\Cannot Upload' is correct and that the 'Converted Files' folder exists there. You may want to delete/comment-out: 'Delete the old file Kill strFldr & "" & strFil
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
|||
|
|||
![]()
Thank you so much!
Would it be possible to have the macro check to see if the converted files folder exists and if not create it to store the converted documents? |
#7
|
||||
|
||||
![]()
Try:
Code:
Option Explicit Dim FSO As Object, oFolder As Object, StrFolds As String Sub Main() Application.ScreenUpdating = False Dim TopLevelFolder As String, TheFolders As Variant, aFolder As Variant, i As Long TopLevelFolder = GetFolder StrFolds = vbCr & TopLevelFolder If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject") End If 'Get the sub-folder structure Set TheFolders = FSO.GetFolder(TopLevelFolder).SubFolders For Each aFolder In TheFolders RecurseWriteFolderName (aFolder) Next 'Process the documents in each "Cannot Upload" folder For i = 1 To UBound(Split(StrFolds, vbCr)) If Split(StrFolds, "\")(UBound(Split(StrFolds, "\"))) = "Cannot Upload" Then 'create the output folder, if necessary On Error Resume Next MkDir (CStr(Split(StrFolds, vbCr)(i)) & "\Converted Files") On Error GoTo 0 'Convert the documents Call ConvertDocuments(CStr(Split(StrFolds, vbCr)(i))) End If Next Application.ScreenUpdating = True End Sub Sub RecurseWriteFolderName(aFolder) Dim SubFolders As Variant, SubFolder As Variant Set SubFolders = FSO.GetFolder(aFolder).SubFolders StrFolds = StrFolds & vbCr & CStr(aFolder) On Error Resume Next For Each SubFolder In SubFolders RecurseWriteFolderName (SubFolder) Next End Sub Function GetFolder() As String Dim oFolder As Object GetFolder = "" Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0) If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path Set oFolder = Nothing End Function Sub ConvertDocuments(oFolder As String) Dim strFldr As String, strFile As String, wdDoc As Document strFldr = oFolder: If strFldr = "" Then Exit Sub strFile = Dir(strFldr & "\*.doc", vbNormal) While strFile <> "" If Right(strFile, 4) = ".doc" Then Set wdDoc = Documents.Open(FileName:=strFldr & "\" & strFile, AddToRecentFiles:=False, ReadOnly:=False, Visible:=False) With wdDoc 'Save as docx or docm, depending on whether the file contains macros. If .HasVBProject = False Then .SaveAs2 FileName:=strFldr & "\Converted Files\" & .Name & "x", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False Else .SaveAs2 FileName:=strFldr & "\Converted Files\" & .Name & "m", FileFormat:=wdFormatXMLDocumentMacroEnabled, AddToRecentFiles:=False End If 'close the document .Close False End With 'Delete the old file Kill strFldr & "\" & strFile End If strFile = Dir() Wend Set wdDoc = Nothing End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#8
|
|||
|
|||
![]()
Amazing!
Yah!!! It's working. Thank you so, so much!! You are the best ![]() |
![]() |
Thread Tools | |
Display Modes | |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
ballpoint | Word VBA | 10 | 08-15-2022 05:24 PM |
![]() |
shpkmom | Word VBA | 1 | 08-15-2022 05:16 PM |
![]() |
D1985 | Word VBA | 5 | 03-29-2022 01:39 PM |
Office 2010 Can't Open Or Save Documents in My Documents Folder | trippb | Office | 1 | 07-12-2013 07:29 AM |
Add folder and subfolder to Favorite | ying06 | Outlook | 0 | 03-30-2012 10:41 AM |