Solved

VBA Save Location

Posted on 2014-12-26
6
209 Views
Last Modified: 2014-12-26
Hi Experts,

I have one WB which download files from web. files which downloaded is saved to active WB location I need to change this location to "D:\AmiBroker Data\NSE\Del" & which is fixed hard coded in Code it self.

here the code
Private Function LastRow(TheWorksheet As Worksheet) As Long
If WorksheetFunction.CountA(TheWorksheet.Cells) > 0 Then
    LastRow = TheWorksheet.Cells.Find(What:="*", After:=TheWorksheet.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If
End Function

Sub Macro1()

Application.ScreenUpdating = False

startDate = Range("C2").Value
stopDate = Range("C3").Value

For xx = startDate To stopDate

If HttpExists("http://nseindia.com/archives/equities/mto/MTO_" & Format(xx, "ddmmyyyy") & ".DAT") Then

Dim WkBk As Excel.Workbook
Dim txtFileName As String
 
txtFileName = Format(xx, "ddmmyyyy")

Set WkBk = Workbooks.Add

    With ActiveSheet.QueryTables.Add(Connection:="URL;http://nseindia.com/archives/equities/mto/MTO_" & Format(xx, "ddmmyyyy") & ".DAT", Destination:=Range("$A$1"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False
    ActiveSheet.Range("H5:H" & LastRow(ActiveSheet)).Value = Format(xx, "dd-mmm-yyyy")
    Application.DisplayAlerts = False
    WkBk.SaveAs Filename:=ThisWorkbook.Path & "\" & txtFileName & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    WkBk.Close (False)
    Application.DisplayAlerts = True
End If

Next xx

Application.ScreenUpdating = True

MsgBox "Done"

End Sub


Function HttpExists(sURL As String) As Boolean
Dim oXHTTP As Object
Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
oXHTTP.Open "HEAD", sURL, False
oXHTTP.send
HttpExists = (oXHTTP.Status = 200)
End Function

Open in new window


See attached file.

Thanks
0
Comment
Question by:Naresh Patel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40518204
0
 
LVL 1

Accepted Solution

by:
Ed70 earned 500 total points
ID: 40518220
If we understand your question properly, you need to change the following statement

WkBk.SaveAs Filename:=ThisWorkbook.Path & "\" & txtFileName & ".csv", FileFormat:=xlCSV, CreateBackup:=False

Open in new window


to something like this:

WkBk.SaveAs Filename:="D:\AmiBroker Data\NSE\Del\" & txtFileName & ".csv", FileFormat:=xlCSV, CreateBackup:=False

Open in new window

0
 
LVL 8

Author Closing Comment

by:Naresh Patel
ID: 40518223
Thanks may I ask follow up question i.e. new question?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Author Comment

by:Naresh Patel
ID: 40518562
may I?
0
 
LVL 1

Expert Comment

by:Ed70
ID: 40518642
If it is a new question, it would be better to ask it as a separate question. And as for me, I do not guarantee I can answer it fast.

Don't forget that this question is already marked as "answered".
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40518649
I mean only new question.Only posting new question link over here.

Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question