Access Opens Excel, Edit, SaveAs, then Close

[jadown]

Hello,



I am trying to open an Excel Workbook from a FileDialog where the
user can select the file, make changes (in this case formatting), then SaveAs a
different name in the same path as the original file from a command button on an
Access form. Everything works except saving the file as a different name.




Quote:

Dim fd As Object, sFileName As String Set fd = Application.FileDialog(3) With fd .AllowMultiSelect = False .Title = "Browse to Select a File" If .Show = -1 Then sFileName = .SelectedItems(1) End With 'Change the column headings on the spreadsheet If Len(sFileName) = 0 Then Exit Sub Dim xlObj As Object Set xlObj = CreateObject("Excel.Application") With xlObj .Workbooks.Open sFileName .Visible = True .Worksheets.Select .Cells.Select .Selection.Replace What:="=", Replacement:="'=" .ActiveWorkbook.SaveAs FileName:="NewFile" & sFileName .Quit End With Set fd = Nothing Set xlObj = Nothing

[June7]

What happens - error message, wrong results, nothing?

[jadown]

Nothing happens

[June7]

The SaveAs code is wrong.

The value of sFileName is the full file path/name, like: C:\foldername\Book1.xlsx

This means cannot just concatenate to more text.

.ActiveWorkbook.SaveAs FileName:=Replace(sFileName, ".xlsx", "_NewFile.xlsx")

[jadown]

Thanks for you help. I do have a question though. Since, I am opening the file from a dialog box how can I make the save as save to where ever the user opens the file? Because multiple users will be opening the file from their local computers.

[jadown]

Okay I tried to add a string for the select path but now I'm getting a run-time error '1004. Microsoft Excel cannot access the file C:\6D233000. The file name or path does not exist, The file is being used by another program...etx.

Dim intChoice As Integer
Dim strPath As String
Dim fd As Object, sFileName As String
'Opens the Excel file
Set fd = Application.FileDialog(3)
With fd
.AllowMultiSelect = False
.Title = "Browse to Select a File"
If .Show = -1 Then sFileName = .SelectedItems(1)
End With
 
'Change the column headings on the spreadsheet
If Len(sFileName) = 0 Then Exit Sub
Dim xlObj As Object
Set xlObj = CreateObject("Excel.Application")
'Collects current path
'make the file dialog visible to the user
intChoice = Application.FileDialog(4).Show
'determine what choice the user made
If intChoice <> 0 Then
    'get the file path selected by the user
    strPath = _
        Application.FileDialog(4).SelectedItems(1)
    'displays the result in a message box
End If
'This will
With xlObj
.Workbooks.Open sFileName
.Visible = True
.Worksheets.Select
.Cells.Select
.Selection.Replace What:="=", Replacement:="'="
.Selection.Replace What:="=", Replacement:="'="
.ActiveWorkbook.SaveAs FileName:=strPath & "NewFile.xlsx"
End With
Set fd = Nothing
Set xlObj = Nothing

[June7]

My suggested code should save the renamed file back to the same location originally opened from. Your latest code does not show implementing my suggestion.