Help!! Open Excel Wkbk, Edit, SaveAs differnt name (1 Viewer)

[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.

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

 

[AOB]

Take a look at this line :

.ActiveWorkbook.SaveAs FileName:="NewFile" & sFileName

The path for the new file is being prefixed by the string "NewFile"

So if the selected file (sFileName) is "//server/folder/subfolder/file.xlsx"

Then you are trying to save it as "NewFile//server/folder/subfolder/file.xlsx"

Which isn't a valid path?

I'm guessing you want to save it as "//server/folder/subfolder/NewFile.xlsx"?

 

[jadown]

Correct, I will like to save it as the same path as the initial file. Is that possible?

 

[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

 

[AOB]

You're still not considering what this line is actually doing :

.ActiveWorkbook.SaveAs FileName:=strPath & "NewFile.xlsx"

You're simply concatenating two strings - but the end result is not a valid path?

So if the selected file (sFileName) is "//server/folder/subfolder/file.xlsx"

Now you are trying to save it as "//server/folder/subfolder/file.xlsxNewFile.xlsx"

Try something like this :

.ActiveWorkbook.SaveAs FileName:=Replace(strPath,".xlsx"," (NewFile).xlsx"

So again, if the selected file (sFileName) is "//server/folder/subfolder/file.xlsx"

It will attempt to save it as "//server/folder/subfolder/file (NewFile).xlsx"

 

[Solo712]

You're still not considering what this line is actually doing :

.ActiveWorkbook.SaveAs FileName:=strPath & "NewFile.xlsx"

You're simply concatenating two strings - but the end result is not a valid path?

So if the selected file (sFileName) is "//server/folder/subfolder/file.xlsx"

Now you are trying to save it as "//server/folder/subfolder/file.xlsxNewFile.xlsx"

Try something like this :

.ActiveWorkbook.SaveAs FileName:=Replace(strPath,".xlsx"," (NewFile).xlsx"

So again, if the selected file (sFileName) is "//server/folder/subfolder/file.xlsx"

It will attempt to save it as "//server/folder/subfolder/file (NewFile).xlsx"

Correct. There is no dialogue necessary btw if what's wanted is to put the file in the same path as the previous file with a known name. All that's needed is isolating the path from the file name like this :

strPath = Left(sFileName, Len(sFileName) - InStr(StrReverse(sFileName), "\"))
.ActiveWorkbook.SaveAs FileName:=strPath & "\NewFile.xlsx"

Best,
Jiri

 

[jadown]

Thanks everyone for your help. It works now.