Conversion Macro

gnt21 · July 2nd, 2012, 03:45 PM

Hi. I'm somewhat new to the world of VBA. I need to create a macro that will open all .wk1 files in a directory and save them as .xls files. I would appreciate any help I can get. Thanks!

JP · July 3rd, 2012, 09:32 AM

Quote:

Originally Posted by gnt21

Hi. I'm somewhat new to the world of VBA. I need to create a macro that will open all .wk1 files in a directory and save them as .xls files. I would appreciate any help I can get. Thanks!

Welcome to the forum. Where are you stuck?

gnt21 · July 3rd, 2012, 09:40 AM

From the beginning actually. I have picked up stuff that others have developed, but have no experience developing macros for myself. I've been able to manipulate others for calculations, but haven't seen one like this before. Thanks!

JP · July 4th, 2012, 08:44 PM

Use Workbooks.Open(filename) to open a file, and Workbook.SaveAs to save the file, specifying the format. The Visual Basic help has sample code for each method.

To open all files in a folder, try something like this:

(From http://stackoverflow.com/q/11152870/190829)

Code:

Sub OpenFiles()
Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\Myfiles"
MyFile = Dir(MyFolder & "\*.wk1")
Do While MyFile <> ""
  Workbooks.Open Filename:=MyFolder & "\" & MyFile
Loop
End Sub

gnt21 · July 5th, 2012, 05:21 PM

Thanks to your help I have the opening and closing of files taken care of. I am partially there for the saving aspect. It will only save the first file as an .xls file and then closes everything else. Am I missing something in the way this is being looped? The code is below. You have been a life saver. I really appreciate it!!

Sub OpenAndSaveFiles()
Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\Documents and Settings\trichter\My Documents\Macro Testing\Wk1 to XLS\wk1-first level"
MyFile = Dir(MyFolder & "\*.wk1")
Do While MyFile <> ""
Workbooks.Open Filename:=MyFolder & "\" & MyFile
ActiveWorkbook.SaveAs MyFile, FileFormat:=xlNormal
MyFile = Dir
Loop
Workbooks.Close
End Sub