Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8,520
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Error 1004 SaveAs Object workbook failed cardfan3206 Excel Programming 16 July 23rd 09 02:36 PM
Run-time error 1004 Method SaveAS of object _Workbook failed David Excel Programming 2 December 18th 08 09:38 PM
runtime error 1004 saveas text file Janis Excel Programming 2 September 1st 06 11:11 PM
error 1004 Method SaveAs of Workbook failed Maury Markowitz Excel Programming 2 October 25th 05 08:45 PM
Error Excel: 1004 SaveAs method of Workbook class failed sebastien Excel Programming 0 August 13th 03 09:59 AM


All times are GMT +1. The time now is 01:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"