• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

ERROR in VBA

V

vjvijay88

New Member
vba showing sub script out of range run time error 9 in this line

Set ThisWS = ThisWB.Worksheets(WS.Name)
 

Attachments

  • Report.xlsm
    25.3 KB · Views: 5
Hi ,

If we remove all the lines of code which do not pertain to the problem , we are left with the following :

strFileName = "fo26DEC2016bhav.csv"
strDestinationFolder = ActiveWorkbook.Path
Set ThisWB = ActiveWorkbook

If Right(strDestinationFolder, 1) <> "\" Then strDestinationFolder = strDestinationFolder & "\"

Set WB = Workbooks.Open(strDestinationFolder & strFileName)

For Each WS In WB.Worksheets
Set ThisWS = ThisWB.Worksheets(WS.Name)
WS.UsedRange.Copy ThisWS.Range("A1")
ThisWS.UsedRange.EntireColumn.AutoFit
Next WS

Normally , use of the keyword ActiveWorkbook is not favoured when the keyword ThisWorkbook can be used , since ActiveWorkbook can refer to any workbook that is open , whereas ThisWorkbook refers specifically to the workbook which has the code that is being executed.

In your case , if your code is in a workbook named Report.xlsm , then ThisWorkbook will refer to the workbook named Report.xlsm

I assume therefore that the VBA variable ThisWB refers to the file Report.xlsm

WB refers to the workbook which is created when Excel opens the .csv file. This can have only one worksheet , and it will have the same name as the .csv file.

Now , if the workbook referred to by ThisWB , which should be Report.xlsm , does not have a worksheet named fo26DEC2016bhav , Excel will generate an error.

You have to check whether this is what you want to do. Is the code written correctly ? Only you can verify this.

Narayan
 
thanks for the reply sir,
Below VBA CODE working properly to extract data from zip file if my zip file presents in C:\Users\max\Downloads..
i.e if i import data , it unzip the zipped file and copy the csv data inside zip file and paste it in the workbook which i need, but the problem is "fo26DEC2016bhav.csv.zip" zip name will change daily ,and i need to download the data and update it , in my workbook worksheet, so my vba code has to be changed as per it can daily update data irrespective of zip name and worksheet name in my workbook, so kindly help me to correct

Code: 
Sub ImportDailyData()
Dim strFileName As String, str7ZIP As String, strZipFile As String, strDestinationFolder As String, strCMD As String
Dim WshShell As Object, fso As Object
Dim WB As Workbook
Dim ThisWB As Workbook
Dim WS As Worksheet
Dim ThisWS As Worksheet

strFileName = "fo26DEC2016bhav.csv"
str7ZIP = "C:\Program Files (x86)\7-Zip\7z.exe"
strDestinationFolder = ActiveWorkbook.Path
strZipFile = strDestinationFolder & "\fo26DEC2016bhav.csv.zip"
Set ThisWB = ActiveWorkbook

If Right(strDestinationFolder, 1) <> "\" Then strDestinationFolder = strDestinationFolder & "\"

Set WshShell = CreateObject("Wscript.Shell")
Set fso = CreateObject("Scripting.FileSystemObject")

If Not fso.FileExists(str7ZIP) Then
    MsgBox "Could not find 7-Zip:  " & vbCrLf & vbCrLf & str7ZIP, vbExclamation, "fo26DEC2016bhav.csv"
    Exit Sub
End If

strCMD = Chr(34) & str7ZIP & Chr(34) & " e -i!" & _
        Chr(34) & strFileName & Chr(34) & " -o" & _
        Chr(34) & strDestinationFolder & Chr(34) & " " & _
        Chr(34) & strZipFile & Chr(34) & " -y"


WshShell.Run strCMD, 0, True

If Not fso.FileExists(strDestinationFolder & strFileName) Then
    MsgBox "Failed to get file:  " & strDestinationFolder & strFileName, vbExclamation, "fo26DEC2016bhav.csv"
    Exit Sub
Else
    '---> Stop Events
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
   
    '---> Open the Import Workbook
    Set WB = Workbooks.Open(strDestinationFolder & strFileName)
   
    '---> Clean Current Data in present workbook
    For Each WS In ThisWB.Worksheets
        If WS.Name <> "Main" Then
            WS.UsedRange.EntireRow.Delete
        End If
    Next WS
    '---> Get Data
    For Each WS In WB.Worksheets
        Set ThisWS = ThisWB.Worksheets(WS.Name)
        WS.UsedRange.Copy ThisWS.Range("A1")
        ThisWS.UsedRange.EntireColumn.AutoFit
    Next WS
   
    '---> Close WB
    Application.DisplayAlerts = False
    WB.Close savechanges:=False
    Kill strDestinationFolder & strFileName
    Application.DisplayAlerts = True
   
    '---> Clean Variables
    Set WB = Nothing
    Set WS = Nothing
   
    '---> Enable Events
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
   
    '---> Advise user
    MsgBox ("Import Daily data successfull.")
End If


End Sub

below is zip file and have upload my excel file

https://www.nseindia.com/content/historical/DERIVATIVES/2016/DEC/fo26DEC2016bhav.csv.zip
Click to expand...
 

Attachments

  • FNOBHAV.xlsb
    928.6 KB · Views: 1

First, it is not the same issue as initial post !

Second, you are acting wild cross posting which is very not appreciate
whatever the forum ! You must post on each forum links to others forums
where you create a thread on same subject …
Whithout those links, as we can't check if you already have a solution
from others forums, we won't waste time and move to help others threads …
​
 
vjvijay88 said:
no sir code is working but my problem is yet to solve
Click to expand...
Hi ,

When you say your file name is fo26DEC2016bhav.csv.zip , and it will change daily , will only the highlighted portion change everyday ?

What will the file name be for say tomorrow's date ?

Narayan
 
Hi ,

See if this works. You will have to change the VBA constant ZIPFILEPATH to suit.

Narayan
 

Attachments

  • FNOBHAV.xlsb
    933.8 KB · Views: 3
vjvijay88 said:
MBr1H
img
Thanks for your effort sir, but it failed to get file, i have changed my zip path file to where i have zip file.
I have posted image file sir
Click to expand...
Hi ,

Where is the file FNOBHAV.xlsb located ?

Where are the .csv files located ?

Where are the .zip files located ?

Narayan
 
Dear sir, one of my fb friend solved the problem and i have uploaded the file, it not only download data from web and it will updata data of separate stocks
 

Attachments

  • Sample.xlsm
    535.4 KB · Views: 3
You must log in or register to reply here.
Back
Top