Taras
asked on
MS Access Docmd export to excel do not over write existing file
I ms access 2013 have a query that I want to export to folder and gave user option to locate folder and chose a name.
On a form I have command button and behind this code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xm l, "Qry_My_Query", saveFileAs, True
Problem is when user what to use the same file name that exit.
I was prompted do I want to over wright file name I click on “Yes” button but it does not do over wright.
On a form I have command button and behind this code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xm
Problem is when user what to use the same file name that exit.
I was prompted do I want to over wright file name I click on “Yes” button but it does not do over wright.
ASKER
How do you know where is location???
this is not location where they want to save a file.
xlFile="C:\Folder\myexcel. xlsx"
this is not location where they want to save a file.
xlFile="C:\Folder\myexcel.
ASKER
Rey,
When dialog box for save is open user navigate to existing file then he decided to use existing file name not to gave new name he is prompted with do you want to over wright existing file. This is happening and you would think that everything is ok.
However when you go and check saved file it did not over write it and file has old data in.
When dialog box for save is open user navigate to existing file then he decided to use existing file name not to gave new name he is prompted with do you want to over wright existing file. This is happening and you would think that everything is ok.
However when you go and check saved file it did not over write it and file has old data in.
@Taras,
when posting a question, include all the information, codes not like you want to chat about your problem.
when posting a question, include all the information, codes not like you want to chat about your problem.
ASKER
Rey.
Behind my command button I have just this line of code that I posted.
What I did not included?
I am not sure what you mean.
Behind my command button I have just this line of code that I posted.
What I did not included?
I am not sure what you mean.
where did you get the dialog box "<<When dialog box for save is open user >>"
ASKER
from function SaveFileAs
Public Function saveFileAs() As String
Dim fd As FileDialog, fileName As String
On Error GoTo ErrorHandler
Set fd = Application.FileDialog(mso FileDialog SaveAs)
If fd.Show = True Then
If fd.SelectedItems(1) <> vbNullString Then
fileName = fd.SelectedItems(1)
End If
Else
'Stop Code Execution for Null File String
End
End If
saveFileAs = fileName
'Cleanup
Set fd = Nothing
Exit Function
ErrorHandler:
Set fd = Nothing
MsgBox "Error " & Err & ": " & Error(Err)
End Function
Public Function saveFileAs() As String
Dim fd As FileDialog, fileName As String
On Error GoTo ErrorHandler
Set fd = Application.FileDialog(mso
If fd.Show = True Then
If fd.SelectedItems(1) <> vbNullString Then
fileName = fd.SelectedItems(1)
End If
Else
'Stop Code Execution for Null File String
End
End If
saveFileAs = fileName
'Cleanup
Set fd = Nothing
Exit Function
ErrorHandler:
Set fd = Nothing
MsgBox "Error " & Err & ": " & Error(Err)
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you a lot Rey.
dim xlFile as s tring
xlFile="C:\Folder\myexcel.
if dir(xlfile)<>"" then kill xlFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xm