Save as

  • Thread starter Thread starter Edgar
  • Start date Start date
E

Edgar

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")
 
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
 
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
 
Back
Top