svae Workbooks.Open as new worksheet

[marc005]

The VBA code to download my csv from my account at dropbox is:

Sub OpenCSVDropbox()
Workbooks.Open Filename:= _
"http://bit.ly/15YwK10"
End Sub

However, a new workbook is always open. I want a new worksheet in the current workbook named "data1"
to open not a new workbook.

 

[marc005]

I have tried:

Sub OpenCSVDropbox()
Workbooks.Open Filename:= _
"http://bit.ly/15YwK10" & "data1"
End Sub

but that does not work he he

 

[p45cal]

try:

Sub OpenCSVDropbox()
Set xx = Workbooks.Open(Filename:="http://bit.ly/15YwK10").Sheets(1)
xx.Name = "data1"
xx.Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End Sub

 

[marc005]

thanx for your input but it did not work Excel opens a new workbook as usual hummm

 

[p45cal]

thanx for your input but it did not work Excel opens a new workbook as usual hummm

Yes, but it get's closed when the sheet is moved. Is that a problem? You get your new sheet, correctly named,added to the existing workbook.

 

[marc005]

I get an error from the line marked with bold below:

Sub OpenCSVDropbox()
Set xx = Workbooks.Open(Filename:="http://bit.ly/15YwK10").Sheets(1)
xx.Name = "data1"
xx.Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End Sub

the worksheet is never added to the existing workbook it is created in a new workbook. sorry if I am being a picky.

 

[p45cal]

Where have you put this code? In the Personal workbook? Put it into the workbook you want to have the sheet added to, in a standard code-module.

Or,

try

Sub OpenCSVDropbox()
Set yy = ActiveWorkbook
Set xx = Workbooks.Open(Filename:="http://bit.ly/15YwK10").Sheets(1)
xx.Name = "data1"
xx.Move After:=yy.Sheets(yy.Sheets.Count)
End Sub

 

[marc005]

thanx for your patient with me Yeahhh I put it in personal (old habit). sorry for that.
By the way your last code works great and most likely the first one too if I just manage to put it in the right place he he
Thanx for your help. It saved me loads of time

 

[p45cal]

Or a different approach, though fraught with dangers:

Sub Macro8()
Set newsht = ActiveWorkbook.Worksheets.Add
Set qry = newsht.QueryTables.Add(Connection:="TEXT;http://bit.ly/15YwK10", Destination:=Range("A1"))
With qry
  .FieldNames = True
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .RefreshStyle = xlInsertDeleteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .TextFilePromptOnRefresh = False
  .TextFilePlatform = 850
  .TextFileStartRow = 1
  .TextFileParseType = xlDelimited
  .TextFileTextQualifier = xlTextQualifierDoubleQuote
  .TextFileConsecutiveDelimiter = False
  .TextFileTabDelimiter = False
  .TextFileSemicolonDelimiter = False
  .TextFileCommaDelimiter = True
  .TextFileSpaceDelimiter = False
  .TextFileTrailingMinusNumbers = True
  .Refresh BackgroundQuery:=False
End With
qry.Delete
newsht.Name = "data1"
newsht.Move after:=newsht.Parent.Sheets(newsht.Parent.Sheets.Count)
End Sub