Categories

See More
Popular Forum

MBA (4887) B.Tech (1769) Engineering (1486) Class 12 (1030) Study Abroad (1004) Computer Science and Engineering (988) Business Management Studies (865) BBA (846) Diploma (746) CAT (651) B.Com (648) B.Sc (643) JEE Mains (618) Mechanical Engineering (574) Exam (525) India (462) Career (452) All Time Q&A (439) Mass Communication (427) BCA (417) Science (384) Computers & IT (Non-Engg) (383) Medicine & Health Sciences (381) Hotel Management (373) Civil Engineering (353) MCA (349) Tuteehub Top Questions (348) Distance (340) Colleges in India (334)
See More
( 7 months ago )

VBA saveas newly created Excel sheet using old Word Document's title

General Tech Learning Aids/Tools
Max. 2000 characters
Replies

usr_profile.png
Mike Franklin

User

( 7 months ago )

 

 

I can create a new workbook by using the method

workbooks.add 

However, I need to save it with the same file name as the previous Word document which I manually copied all the useful data into it.

I have done some research into this and have found out about this method in VBA called

saveas

but whenever I try, it fails to save the new file with the same name as the Word document.

This was my code...

Dim wdFileName As Variant
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date  

Workbooks.Add


MyPath = "D:\Analysis\"
If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

'this line below selects the word file to be chosen
MyFile = Dir(MyPath & "*.docx", vbNormal)

'If no files were found, exit the sub
If Len(MyFile) = 0 Then
    MsgBox "No files were found...", vbExclamation
    Exit Sub
End If

'Loop through each word file in the folder
Do While Len(MyFile) > 0

    'Assign the date/time of the current file to a variable
    LMD = FileDateTime(MyPath & MyFile)

    'If the date/time of the current file is greater than the latest
    'recorded date, assign its filename and date/time to variables
    If LMD > LatestDate Then
        LatestFile = MyFile
        LatestDate = LMD
    End If

    'Get the next word file from the folder
    MyFile = Dir
    Loop

 wdFileName = MyPath & LatestFile

(extra code here)
(extra code here)

ActiveWorkbook.SaveAs Filename:=wdFileName, _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

(the last 2 lines of code do not seem to have any effect at all and it does not save the Excel with the same title name as the Word document I get my data from)

As I'm still new in VBA, help would be much appreciated with thanks!

Please do provide clear code to aid in my learning.

what's your interest


forum_ban8_5d8c5fd7cf6f7.gif