Opening and saving Excel from Access

[dark11984]

I'm after an access code to open an excel spreadsheet and save as another name than close the spreadsheet. My spreadsheet is located in I:\Databases\StockDB\SarkoezyTemp\NswStoresOM2.xls

Can anyone help please?

 

[georgedwilkinson]

Use FileCopy() and Kill().

 

[dark11984]

Will this work if I have to change the file type as well when I save as?

 

[georgedwilkinson]

Hmmmm. The way you say that scares me a little.

Neither FileCopy() nor Kill() care what the file name is. You'll need to supply the logic to use them, including any logic for giving the file names to copy and delete.

You could potentially use Dir() in conjunction with them to get closer to using a file type, but you need to be careful.

 

[boblarson]

I'm guessing (just guessing) that you might be wanting to change it to a CSV file, perhaps. If so, this would do it for you:

Function RenameExcelFile(strFileName As String, strNewName As String, strNewFileType As String)
    ' xlCSV for CSV
    'xlNormal for normal Excel for the current version
    Const xlCSV As Integer = 6
    Const xlNormal As Integer = -4143
    Dim objXL As Object
    Dim xlWB As Object
    
    Set objXL = CreateObject("Excel.Application")
    
    Set xlWB = objXL.Workbooks.Open(strFileName)
    
    xlWB.SaveAs strNewName, strNewFileType

    
    xlWB.Close False
    objXL.Application.Quit
    
    Set xlWB = Nothing
    Set objXL = Nothing
    
    
End Function

And the usage would be like this:

RenameExcelFile "C:\Test\Text.xls","C:\Test\Text.csv", xlCSV

 

[Skip Bisconer]

Bob,

This answers my problem as well only I want to save a backup of the file with a mm-yy added to the end of the file name and save as an .xls. Can I just & the date some way?

 

[DCrake]

To insert the date into the file name use

Dim strDate as String
strDate = Format(Date(),"yyyymmdd")

OldFileName = "C:\Test\Test.xls"
NewFileName = Replace(OldFileName,".xls",strDate & ".xls")

FileCopy OldFileName,NewFileName

 

[Rabbie]

To open an Excel work book

Dim DataDate As String, wksBook As Excel.Workbook, srcBook As Excel.Workbook, wksSheet As Excel.Worksheet,
    objExcel As Excel.Application
       srcFile = "H:\directory\filename.xls"
       Workbooks.Open (srcFile)

To close with a new name held in srcFile

ActiveWorkbook.SaveAs FileName:=srcFile, FileFormat:= _
        xlNormal
 
 ActiveWorkbook.Close