![]() |
|
|||||||
|
|
|
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
|
|
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
Loop Through all documents in a folder
|
ballpoint | Word VBA | 10 | 08-15-2022 05:24 PM |
loop through all documents in a folder, search and replace text, save document ONLY IF CHANGE MADE
|
shpkmom | Word VBA | 1 | 08-15-2022 05:16 PM |
Combine 2 word documents in subfolder and save on same location
|
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 |