Code for SAVE AS

[jms2553]

I get several reports (in .csv format) that I need to 'save as' each week - all in the same location, converted to .xls. What code can I use to open File / Save As and direct it to the same folder each week?

Thanks!

 

[xenou]

Using the macro recorder will give you a good start: just open one of the .csv files with excel with the recorder on.

Here are some examples of some methods you could use:
1) GetOpenFileName (to let you select the file to open)
2) Open (to open a text file...a rather complicated set of arguments so record this piece)
3) SaveAs

I have put together the routines so that the save as filepath is a constant - this way you can plug the code into other routines and just change the constant (I use this code in about 10 different projects). However, you could remove the constant and just hard code the file path in the routine. Alternatively, if you want to create a public variable rather than a constant, then you could create a changing filepath or filename (for instance, add the date at the end).

Regards.

Public Const MY_FILE_PATH As String = "C:\myWorkbook.xls"
Public varFileName As Variant
'----------------------------------------
Sub OpenTextFileAndSave()
    Call MyGetOpenFileName
    Call MyOpenTextFile
    Call MySaveWorkbook
End Sub
'----------------------------------------
Private Sub MyGetOpenFileName()

    varFileName = Application.GetOpenFilename(Title:="Select a File to Import")
    
    'All code ends if user clicks cancel or close
    If varFileName = False Or varFileName = "" Then End

End Sub
'----------------------------------------
Private Sub MySaveWorkbook()
    ActiveWorkbook.SaveAs Filename:=MY_FILE_PATH, FileFormat:= _
        xlWorkbookNormal
End Sub
'----------------------------------------
Private Sub MyOpenTextFile()
'I recorded this code with the macro recorder by opening a text file with excel.
'you may want to do this and use your code in case any parameters are different --
'If you do, the filename may be replaced with the variable from the routine above
    
    Workbooks.OpenText Filename:= _
    varFileName, Origin:= _
    437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
    , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
    Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
    Array(10, 1)), TrailingMinusNumbers:=True

End Sub