Run Time Error 1004
in [Visual Studio]
|
From: nidhi jaie on 14 Jul 2010 04:53 Hi, I am trying to use the below code for moving all the tabs into seperate file and save them. But I am getting Run time error whenever the no. of tabs are more then 5. Anyone please help.. Private Sub Save_file() Dim sFileName As String Dim sPurpose As String sPurpose = InputBox(Prompt:=" Please Specify the whether its Projected Revenue, Inter company or any other Purpose", Title:="Purpose of Saving Files") If sPurpose = vbNullString Then Exit Sub End If 'Show the open dialog and pass the selected file name to the String variable "sFileName" Application.DisplayAlerts = False sFileName = Application.GetOpenFilename If sFileName = "False" Then Exit Sub Workbooks.Open sFileName isheetcount = ActiveWorkbook.Worksheets.Count For isheet = 1 To (isheetcount) 'MsgBox (iSheetCount & " no of sheets and for loop count is " & iSheet) Worksheets(isheet).Move ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name & "_" & sPurpose & ".xls", FileFormat:=56 ActiveWorkbook.Close isheetcount = ActiveWorkbook.Worksheets.Count isheet = 1 If isheetcount = 1 Then ActiveWorkbook.Sheets(isheet).Activate If ActiveSheet.Name = "Mysheet" Then 'Form_Save_tab.Active Exit Sub Else Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "MySheet" Worksheets(isheet).Move ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name & "_" & sPurpose & ".xls", FileFormat:=56 ActiveWorkbook.Close MsgBox "Tabs have been saved as seperate files" ActiveWorkbook.Close False 'Form_Save_tab.Active Exit Sub End If End If Next End Sub Thanks Nidhi
From: Mayayana on 14 Jul 2010 09:26 Is that VB.Net? Or maybe MS Office VBA? Whatever it is, it's not VB. This group is for VB (VB5/6) questions. For VB.Net try here: microsoft.public.dotnet.languages.vb MS Office: microsoft.public.office.developer.vba Note that these groups are no longer available directly from the MS news server - news.microsoft.com. But many other news servers still carry them. There's a good, free server you can sign up with here: http://eternal-september.org/ Also, when you get to the right group, it's best to explain the error message. Do you expect people to look up error 1004 just to answer your question? | I am trying to use the below code for moving all the tabs into | seperate file and save them. | | But I am getting Run time error whenever the no. of tabs are more then | 5. Anyone please help.. | | Private Sub Save_file() | Dim sFileName As String | Dim sPurpose As String | sPurpose = InputBox(Prompt:=" Please Specify the whether its | Projected Revenue, Inter company or any other Purpose", | Title:="Purpose of Saving Files") | If sPurpose = vbNullString Then | Exit Sub | End If | | 'Show the open dialog and pass the selected file name to the String | variable "sFileName" | Application.DisplayAlerts = False | sFileName = Application.GetOpenFilename | | If sFileName = "False" Then Exit Sub | Workbooks.Open sFileName | isheetcount = ActiveWorkbook.Worksheets.Count | For isheet = 1 To (isheetcount) | 'MsgBox (iSheetCount & " no of sheets and for loop count | is " & iSheet) | Worksheets(isheet).Move | ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name & "_" & | sPurpose & ".xls", FileFormat:=56 | ActiveWorkbook.Close | isheetcount = ActiveWorkbook.Worksheets.Count | isheet = 1 | If isheetcount = 1 Then | ActiveWorkbook.Sheets(isheet).Activate | If ActiveSheet.Name = "Mysheet" Then | 'Form_Save_tab.Active | Exit Sub | Else | | Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "MySheet" | Worksheets(isheet).Move | ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name & | "_" & sPurpose & ".xls", FileFormat:=56 | ActiveWorkbook.Close | MsgBox "Tabs have been saved as seperate files" | ActiveWorkbook.Close False | 'Form_Save_tab.Active | Exit Sub | End If | End If | | Next | End Sub | | | | Thanks | Nidhi
From: Auric__ on 14 Jul 2010 10:50 On Wed, 14 Jul 2010 08:53:21 GMT, nidhi jaie wrote: > I am trying to use the below code for moving all the tabs into > seperate file and save them. > > But I am getting Run time error whenever the no. of tabs are more then > 5. Anyone please help.. [big snip] > Application.DisplayAlerts = False Always always *ALWAYS* comment out the above line while debugging *anything* in *any* MS Office product. You may *need* those alerts. > ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name & "_" & > sPurpose & ".xls", FileFormat:=56 This line consistantly gives me your error ("Run-time error '1004': Method 'SaveAs' of object '_Workbook' failed") regardless of the number of sheets. (That it works at all for you makes me wonder, though.) The problem (here, at least) is with FileFormat; removing it kills the error. I suggest you try either replacing the 56 with a named constant (such as "xlWorkbookNormal"), or else totally remove it and accept the default (which is normally "xlWorkbookNormal", but can be changed in the program options). If that doesn't fix it, I suggest you try asking in an Excel-specific group. microsoft.public.excel.programming is the best, if Google carries the MS groups (which it *should*; if not, try something besides Google). -- You will show me your Chipmunk Style, or I will karate your city!
From: Nobody on 14 Jul 2010 11:13 "Auric__" <not.my.real(a)email.address> wrote in message news:Xns9DB54FCAE25FCauricauricauricauric(a)85.214.73.210... > If that doesn't fix it, I suggest you try asking in an Excel-specific > group. > microsoft.public.excel.programming is the best, if Google carries the MS > groups (which it *should*; if not, try something besides Google). MS removed the Excel group, but here is a free news server that still carries it: news://news.aioe.org/microsoft.public.excel.programming
From: Nobody on 14 Jul 2010 11:58
"Auric__" <not.my.real(a)email.address> wrote in message news:Xns9DB54FCAE25FCauricauricauricauric(a)85.214.73.210... > If that doesn't fix it, I suggest you try asking in an > Excel-specific group. > microsoft.public.excel.programming is the best, if Google carries > the MS > groups (which it *should*; if not, try something besides Google). > > -- news.eternal-September.org should be carrying too. |