Link to home
Start Free TrialLog in
Avatar of Euro5
Euro5Flag for United States of America

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?

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Use line#63 before line#62.
We posted almost at the same time Martin. :)
Avatar of Euro5

ASKER

Thanks very much Martin!
You're welcome Euro5.