Solved

VBA Save Location

Posted on 2014-12-26
6
203 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:itjockey
  • 4
  • 2
6 Comments
 
LVL 8

Author Comment

by:itjockey
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:itjockey
ID: 40518223
Thanks may I ask follow up question i.e. new question?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 8

Author Comment

by:itjockey
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:itjockey
ID: 40518649
I mean only new question.Only posting new question link over here.

Thanks
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

820 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