|
Save as
What is wrong with the following line of code?
I would like it to save a copy of the current workbook to the directory listed with the name as todays date formatted dd-mmm-yy TIA ActiveWorkbook.saveas ("C:\Documents and Settings\ThoemmE1 \Desktop\Shares\Web Query Data\UK Shares by Sector\" & Format(Date, dd-mmm-yy) & ".xls") |
Save as
Hi
try dim fname as string fname = "C:\Documents and Settings\ThoemmE1\Desktop\Shares\Web Query Data\UK Shares by Sector\" &Format(Date, "dd-mmm-yy") & ".xls" ActiveWorkbook.SaveAs filename:=fname -- Regards Frank Kabel Frankfurt, Germany Edgar wrote: What is wrong with the following line of code? I would like it to save a copy of the current workbook to the directory listed with the name as todays date formatted dd-mmm-yy TIA ActiveWorkbook.saveas ("C:\Documents and Settings\ThoemmE1 \Desktop\Shares\Web Query Data\UK Shares by Sector\" & Format(Date, dd-mmm-yy) & ".xls") |
Save as
Hi
Thanks for this, i have used this and it worked fine the first time i used it and now it is coming up with the following error. Compile error: Wrong number of arguments or invalid property assignment!! Any ideas Sub WebImport() Dim hLink As String Dim hname As String Dim i As Long Dim fname As String 'fname = "C:\Documents and Settings\ThoemmE1 \Desktop\Shares\Web Query Data\UK Shares by Sector\" & Format(Date, "dd-mmm-yy") & ".xls" fname = "C:\Documents and Settings\ThoemmE1 \Desktop\Shares\Web Query Data\UK Shares by Sector\" & Format(Date, "dd-mmm-yy") & ".xls" For i = 1 To 98 Step 1 hLink = Worksheets("Index").Cells(i, 2).Text hname = Worksheets("Index").Cells(i, 1).Text 'Worksheets.Add Worksheets.Add After:=Worksheets(Worksheets.Count) With ActiveSheet.QueryTables.Add(Connection:="URL;" & hLink, _ Destination:=Range("A1")) .Name = "news.bbc.co.uk" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "11" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With ActiveSheet.Name = hname Columns("D:M").NumberFormatLocal = "0.00_ " Rows("3:5").Select Selection.Delete Shift:=xlUp Rows("1:1").Select Selection.Delete Shift:=xlUp Columns("A:A").Select Selection.Delete Shift:=xlToLeft Next i ActiveWorkbook.saveas Filename:=fname End Sub -----Original Message----- Hi try dim fname as string fname = "C:\Documents and Settings\ThoemmE1 \Desktop\Shares\Web Query Data\UK Shares by Sector\" &Format(Date, "dd-mmm-yy") & ".xls" ActiveWorkbook.SaveAs filename:=fname -- Regards Frank Kabel Frankfurt, Germany Edgar wrote: What is wrong with the following line of code? I would like it to save a copy of the current workbook to the directory listed with the name as todays date formatted dd-mmm-yy TIA ActiveWorkbook.saveas ("C:\Documents and Settings\ThoemmE1 \Desktop\Shares\Web Query Data\UK Shares by Sector\" & Format(Date, dd-mmm-yy) & ".xls") . |
Save as
Edgar said it was fixed in a later posting in a related thread.
-- Regards, Tom Ogilvy "Edgar" wrote in message ... Hi Thanks for this, i have used this and it worked fine the first time i used it and now it is coming up with the following error. Compile error: Wrong number of arguments or invalid property assignment!! Any ideas Sub WebImport() Dim hLink As String Dim hname As String Dim i As Long Dim fname As String 'fname = "C:\Documents and Settings\ThoemmE1 \Desktop\Shares\Web Query Data\UK Shares by Sector\" & Format(Date, "dd-mmm-yy") & ".xls" fname = "C:\Documents and Settings\ThoemmE1 \Desktop\Shares\Web Query Data\UK Shares by Sector\" & Format(Date, "dd-mmm-yy") & ".xls" For i = 1 To 98 Step 1 hLink = Worksheets("Index").Cells(i, 2).Text hname = Worksheets("Index").Cells(i, 1).Text 'Worksheets.Add Worksheets.Add After:=Worksheets(Worksheets.Count) With ActiveSheet.QueryTables.Add(Connection:="URL;" & hLink, _ Destination:=Range("A1")) .Name = "news.bbc.co.uk" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "11" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With ActiveSheet.Name = hname Columns("D:M").NumberFormatLocal = "0.00_ " Rows("3:5").Select Selection.Delete Shift:=xlUp Rows("1:1").Select Selection.Delete Shift:=xlUp Columns("A:A").Select Selection.Delete Shift:=xlToLeft Next i ActiveWorkbook.saveas Filename:=fname End Sub -----Original Message----- Hi try dim fname as string fname = "C:\Documents and Settings\ThoemmE1 \Desktop\Shares\Web Query Data\UK Shares by Sector\" &Format(Date, "dd-mmm-yy") & ".xls" ActiveWorkbook.SaveAs filename:=fname -- Regards Frank Kabel Frankfurt, Germany Edgar wrote: What is wrong with the following line of code? I would like it to save a copy of the current workbook to the directory listed with the name as todays date formatted dd-mmm-yy TIA ActiveWorkbook.saveas ("C:\Documents and Settings\ThoemmE1 \Desktop\Shares\Web Query Data\UK Shares by Sector\" & Format(Date, dd-mmm-yy) & ".xls") . |
Save as
Hi Tom
thanks for the info, didn't see that other posting :-) -- Regards Frank Kabel Frankfurt, Germany Tom Ogilvy wrote: Edgar said it was fixed in a later posting in a related thread. "Edgar" wrote in message ... Hi Thanks for this, i have used this and it worked fine the first time i used it and now it is coming up with the following error. Compile error: Wrong number of arguments or invalid property assignment!! Any ideas Sub WebImport() Dim hLink As String Dim hname As String Dim i As Long Dim fname As String 'fname = "C:\Documents and Settings\ThoemmE1 \Desktop\Shares\Web Query Data\UK Shares by Sector\" & Format(Date, "dd-mmm-yy") & ".xls" fname = "C:\Documents and Settings\ThoemmE1 \Desktop\Shares\Web Query Data\UK Shares by Sector\" & Format(Date, "dd-mmm-yy") & ".xls" For i = 1 To 98 Step 1 hLink = Worksheets("Index").Cells(i, 2).Text hname = Worksheets("Index").Cells(i, 1).Text 'Worksheets.Add Worksheets.Add After:=Worksheets(Worksheets.Count) With ActiveSheet.QueryTables.Add(Connection:="URL;" & hLink, _ Destination:=Range("A1")) .Name = "news.bbc.co.uk" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "11" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With ActiveSheet.Name = hname Columns("D:M").NumberFormatLocal = "0.00_ " Rows("3:5").Select Selection.Delete Shift:=xlUp Rows("1:1").Select Selection.Delete Shift:=xlUp Columns("A:A").Select Selection.Delete Shift:=xlToLeft Next i ActiveWorkbook.saveas Filename:=fname End Sub -----Original Message----- Hi try dim fname as string fname = "C:\Documents and Settings\ThoemmE1 \Desktop\Shares\Web Query Data\UK Shares by Sector\" &Format(Date, "dd-mmm-yy") & ".xls" ActiveWorkbook.SaveAs filename:=fname -- Regards Frank Kabel Frankfurt, Germany Edgar wrote: What is wrong with the following line of code? I would like it to save a copy of the current workbook to the directory listed with the name as todays date formatted dd-mmm-yy TIA ActiveWorkbook.saveas ("C:\Documents and Settings\ThoemmE1 \Desktop\Shares\Web Query Data\UK Shares by Sector\" & Format(Date, dd-mmm-yy) & ".xls") . |
| All times are GMT +1. The time now is 01:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com