Slomaro2000
Board Regular
- Joined
- Jun 4, 2008
- Messages
- 107
Hi all,
Im using the code below to open the .XLS files in a folder and save them as .XLSX files with the same name as they are in the same folder.
This is the macro I am using. Not sure why this is not working. If I try to put them on the C: in a folder I can and it works fine but when I try to save to the server its not working.
A little bit on why Im doing this. Business Objects sends a file (XLS) to a specified folder. I need these files to be (XLSX). So all I am trying to do is convert the XLS files in this folder to XLSX. If there is a easier way please let me know.
The problem must be here
Im using the code below to open the .XLS files in a folder and save them as .XLSX files with the same name as they are in the same folder.
This is the macro I am using. Not sure why this is not working. If I try to put them on the C: in a folder I can and it works fine but when I try to save to the server its not working.
A little bit on why Im doing this. Business Objects sends a file (XLS) to a specified folder. I need these files to be (XLSX). So all I am trying to do is convert the XLS files in this folder to XLSX. If there is a easier way please let me know.
Code:
Public Function IsFileOpen(strFileName As String) As Boolean
On Error Resume Next 'Ignore any errors (i.e. if workbook is not open)
Set wrkFileName = Workbooks(strFileName)
If wrkFileName Is Nothing Then
IsFileOpen = False
Else
IsFileOpen = True
End If
On Error GoTo 0 'Nullify above error handler
End Function
Sub Macro1()
Dim strDir As String, _
strFileType As String
Dim objFSO As Object, _
objFolder As Object, _
objFile As Object
Dim intCounter As Integer
strDir = "[URL="file://\\Mdnt15\maint_odms\Data\Reliability"]\\Mdnt15\maint_odms\Data\Reliability[/URL] Tool\WO Detail Spending Report_Access\BO Reports that feed Reliability Final" 'Change to suit
'strDir = "C:\Users\U369875\Desktop\jaysonTest" 'Change to suit
strFileType = "XLS" 'Shouldn't need to, but change to suit if required
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strDir)
Application.ScreenUpdating = False
For Each objFile In objFolder.Files
'If the file in the 'strDir' directory is not this workbook, then...
If objFile.Name <> ThisWorkbook.Name Then
If objFile.Name Like "*." & strFileType Then
'...check to see if it's open. If it is...
If IsFileOpen(objFile.Name) = True Then
'...run the 'MyMacro' passing the active workbook variable with it and _
increment the counter.
Call MyMacro(objFile.Name)
intCounter = intCounter + 1
'Else, _
1. Open the file, _
2. Run the 'MyMacro' passing the active workbook variable with it, _
3. Save the changes and close the file, and _
4. Increment the counter.
Else
Workbooks.Open (strDir & "\" & objFile.Name), UpdateLinks:=False
Call MyMacro(objFile.Name)
'Workbooks(objFile.Name).Close SaveChanges:=True
intCounter = intCounter + 1
End If
End If
End If
'Release memory
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
Next objFile
Application.ScreenUpdating = True
Select Case intCounter
Case Is = 0
MsgBox "There were no """ & strFileType & """ file types in the """ & strDir & """ directory for the desired macro to be run on.", vbExclamation, "Data Execution Editor"
Case Is = 1
MsgBox "The desired macro has been run on the only """ & strFileType & """ file in the """ & strDir & """ directory.", vbInformation, "Data Execution Editor"
Case Is > 1
MsgBox "The desired macro has now been run on the " & intCounter & " files in the """ & strDir & """ directory.", vbInformation, "Data Execution Editor"
End Select
End Sub
Sub MyMacro(strDesiredWkb As String)
'ActiveWorkbook.SaveAs , strDir, FileFormat:=51
Application.DisplayAlerts = False
ChDir "[URL="file://\\Mdnt15\maint_odms\Data\Reliability"]\\Mdnt15\maint_odms\Data\Reliability[/URL] Tool\WO Detail Spending Report_Access\BO Reports that feed Reliability Final\testing_Jayson"
ActiveWorkbook.SaveAs , FileFormat:=51
ActiveWorkbook.Close
Application.DisplayAlerts = True
End SubThe problem must be here
Code:
Sub MyMacro(strDesiredWkb As String)
'ActiveWorkbook.SaveAs , strDir, FileFormat:=51
Application.DisplayAlerts = False
ChDir "[URL="file://mdnt15/maint_odms/Data/Reliability"]\\Mdnt15\maint_odms\Data\Reliability[/URL] Tool\WO Detail Spending Report_Access\BO Reports that feed Reliability Final\testing_Jayson"
ActiveWorkbook.SaveAs , FileFormat:=51
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub
