SaveAS dialog box gives run time error 1004 in Excel VBA
 

I'm downloading a Watch List of stocks from Fidelity.com into an Excel 2007
spreadsheet. The download format is "stocks.csv." The VBA code below opens
that file and saves it as an Excel 2007 workbook, "new stocks." The processs
elicits a dialog box saying that "stocks.csv" already exists and asks if I
want to replace it. If I click "Yes" the code excecutes ok. But If I click
"No" or "Cancel" I get

"Run time Error 1004"
"Method 'SaveAS of Object'_Workbook' failed"

Can someone tell me what's happening here and how I can correct this? Thanks
in advance for any help you can give me.

Here is the VBA code I'm using:

' ChangeDownloadFormat Macro
' Changes Downloaded Fidelity Watch Lists Format to Excel 2007 format
'
Workbooks.Open "C:\Users\TEST\Desktop\Stocks.csv"
'Workbooks("Stocks.csv").Activate
ActiveWorkbook.SaveAs Filename:="C:\Users\TEST\Desktop\new stocks.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False


ActiveWorkbook.Close

SaveAS dialog box gives run time error 1004 in Excel VBA
 

Workbooks.Open "C:\Users\TEST\Desktop\Stocks.csv"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Users\TEST\Desktop\new stocks.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True


If this post helps click Yes
---------------
Jacob Skaria


"JCIrish" wrote:

I'm downloading a Watch List of stocks from Fidelity.com into an Excel 2007
spreadsheet. The download format is "stocks.csv." The VBA code below opens
that file and saves it as an Excel 2007 workbook, "new stocks." The processs
elicits a dialog box saying that "stocks.csv" already exists and asks if I
want to replace it. If I click "Yes" the code excecutes ok. But If I click
"No" or "Cancel" I get

"Run time Error 1004"
"Method 'SaveAS of Object'_Workbook' failed"

Can someone tell me what's happening here and how I can correct this? Thanks
in advance for any help you can give me.

Here is the VBA code I'm using:

' ChangeDownloadFormat Macro
' Changes Downloaded Fidelity Watch Lists Format to Excel 2007 format
'
Workbooks.Open "C:\Users\TEST\Desktop\Stocks.csv"
'Workbooks("Stocks.csv").Activate
ActiveWorkbook.SaveAs Filename:="C:\Users\TEST\Desktop\new stocks.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False


ActiveWorkbook.Close

SaveAS dialog box gives run time error 1004 in Excel VBA
 

I think this works. It occurs in several places. I'll try them and get back
to you. Many thanks.

"Jacob Skaria" wrote:

Workbooks.Open "C:\Users\TEST\Desktop\Stocks.csv"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Users\TEST\Desktop\new stocks.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True


If this post helps click Yes
---------------
Jacob Skaria


"JCIrish" wrote:

I'm downloading a Watch List of stocks from Fidelity.com into an Excel 2007
spreadsheet. The download format is "stocks.csv." The VBA code below opens
that file and saves it as an Excel 2007 workbook, "new stocks." The processs
elicits a dialog box saying that "stocks.csv" already exists and asks if I
want to replace it. If I click "Yes" the code excecutes ok. But If I click
"No" or "Cancel" I get

"Run time Error 1004"
"Method 'SaveAS of Object'_Workbook' failed"

Can someone tell me what's happening here and how I can correct this? Thanks
in advance for any help you can give me.

Here is the VBA code I'm using:

' ChangeDownloadFormat Macro
' Changes Downloaded Fidelity Watch Lists Format to Excel 2007 format
'
Workbooks.Open "C:\Users\TEST\Desktop\Stocks.csv"
'Workbooks("Stocks.csv").Activate
ActiveWorkbook.SaveAs Filename:="C:\Users\TEST\Desktop\new stocks.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False


ActiveWorkbook.Close

SaveAS dialog box gives run time error 1004 in Excel VBA
 

Jacob,

Your code ends the error just fine but it supress the dialog box completely.
Is there a way to retain the dialog box where a "Yes" works fine (as it
always did) but the "No" and "Cancel" choices (the apparent Excel defaul
arrangement) are also functional?

Thanks again for your help

jcirish

"Jacob Skaria" wrote:

Workbooks.Open "C:\Users\TEST\Desktop\Stocks.csv"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Users\TEST\Desktop\new stocks.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True


If this post helps click Yes
---------------
Jacob Skaria


"JCIrish" wrote:

I'm downloading a Watch List of stocks from Fidelity.com into an Excel 2007
spreadsheet. The download format is "stocks.csv." The VBA code below opens
that file and saves it as an Excel 2007 workbook, "new stocks." The processs
elicits a dialog box saying that "stocks.csv" already exists and asks if I
want to replace it. If I click "Yes" the code excecutes ok. But If I click
"No" or "Cancel" I get

"Run time Error 1004"
"Method 'SaveAS of Object'_Workbook' failed"

Can someone tell me what's happening here and how I can correct this? Thanks
in advance for any help you can give me.

Here is the VBA code I'm using:

' ChangeDownloadFormat Macro
' Changes Downloaded Fidelity Watch Lists Format to Excel 2007 format
'
Workbooks.Open "C:\Users\TEST\Desktop\Stocks.csv"
'Workbooks("Stocks.csv").Activate
ActiveWorkbook.SaveAs Filename:="C:\Users\TEST\Desktop\new stocks.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False


ActiveWorkbook.Close