Euro5
asked on
VBA saving file message display
The code below give me an question "There is a file already, do you want to save over existing?" even though I have
Application.DisplayAlerts = False
Can anyone help?
Application.DisplayAlerts = False
Can anyone help?
Sub PullDataNewOrigin_Details()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim DSN As String
Dim Password As Variant
If Sheets("Queries").Range("E3").Value = True Then Password = InputBox("Please enter your password here:") Else Password = ""
DSN = Sheets("Selection").Range("B2")
cn.Open "DSN=" & DSN & ";password =" & Password & "; ConnectionTimeout=0;"
'Dim Query As String
Dim Query As String
'Query = Sheets("Queries").Range("C2").Value
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandTimeout = 0
cmd.CommandText = Query
'Set rs = cmd.Execute()
'Sheets("Shipping").Range("A2").CopyFromRecordset rs
'Set rs = Nothing
Query2 = Sheets("Queries").Range("C73").Value
cmd.CommandText = Query2
Sheets.Add
ActiveSheet.Name = "Rerate Changed Origin Detail"
Dim iCols As Integer
Set rs = cmd.Execute()
For iCols = 0 To rs.Fields.Count - 1
Worksheets("Rerate Changed Origin Detail").Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
Sheets("Rerate Changed Origin Detail").Range("A2").CopyFromRecordset rs
Set rs = Nothing
cn.Close
ActiveWorkbook.Sheets("Rerate Changed Origin Detail").Move
Dim ws As Worksheet
Dim flPath As String, flName As String
Set ws = Worksheets("Rerate Changed Origin Detail")
flPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
'sPath = Sheets("Queries").Range("C59").Value
flName = "Rerate Changed Origin Detail" & Format(Date, "yyyymmdd") & ".csv"
ws.SaveAs Filename:=flPath & flName, FileFormat:=xlCSV, CreateBackup:=False
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = False
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Use line#63 before line#62.
We posted almost at the same time Martin. :)
ASKER
Thanks very much Martin!
You're welcome Euro5.