Solved

VBA saving file message display

Posted on 2016-09-16
5
72 Views
Last Modified: 2016-09-16
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

0
Comment
Question by:Euro5
  • 2
  • 2
5 Comments
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 41802234
Line 63 be before line 62 and line 65 should say True.
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41802237
Use line#63 before line#62.
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41802239
We posted almost at the same time Martin. :)
0
 

Author Closing Comment

by:Euro5
ID: 41802244
Thanks very much Martin!
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41802257
You're welcome Euro5.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question