0

I have a 32 bit excel 2016. The excel has a proprietary add-on which allows to query a proprietary database. the add-on includes a function DvCHRaw() which return an array of data.

I am writing a mother excel vba that list the query conditions. the vba tends to open new workbook and perform the query then close the workbook. for example, I will loop 1000 columns per day, and loop 10 days. I will save each day's data in its own folder with 1000 csv files.

However, the excel will be stuck at certain point, for example, 9 days, or 5 days. Not sure if it is memory related issue though I set wb = nothing after workbook closing.

Can anyone give a hand here?

here is the code I wrote

Sub raw_data_extract()

    Set StartDate = Range("B1")
    nextdate = StartDate
    Set EndDate = Range("B2")


    Do While nextdate <= EndDate
        Set tagname = Range("E1")
    
        savedate = Month(nextdate) & "-" & Day(nextdate) & "-" & Year(nextdate)
        newfolderpath = "D:\rawdata\" & savedate
        
        
        If Dir(newfolderpath, vbDirectory) = "" Then
            MkDir (newfolderpath)
        End If
        
        
        Do While tagname.Value <> ""
            Set wb = Workbooks.Add()
            Range("A1").Value = nextdate
            Range("B1").Value = tagname
            Range("C1").Value = "=ROWS(DvCHRaw(""APP"",B1,FALSE,""Timestamp;Value"",""Local"",A$1,A$1+1,0,0,-1))"
            
            Set arraystart = Range("A2")
            Set arrayend = arraystart.Offset(Range("C1") - 1, 1)
            
            Range(arraystart, arrayend).FormulaArray = "=DvCHRaw(""APP"",B1,FALSE,""Timestamp;Value"",""Local"",A$1,A$1+1,0,0,-1)"
            Range("C1").Clear
            
            Range("A2").Select
            Range(Selection, Selection.End(xlDown)).NumberFormat = "m/d/yyyy h:mm:ss.000"
            
            
            Range("A1").CurrentRegion.Select
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
            Application.DisplayAlerts = False
            replaceslash = Replace(tagname, "/", "#")
            replaceslashdot = Replace(replaceslash, ".", "#")
            
            newname = replaceslashdot & "_" & savedate
            Application.ActiveWorkbook.SaveAs newfolderpath & "\" & newname, xlCSV
            
            Application.DisplayAlerts = True
            ActiveWorkbook.Close savechanges = False
            
            Set tagname = tagname.Offset(1, 0)
            
            Set wb = Nothing
    
        Loop
        nextdate = nextdate + 1
    Loop
    
    MsgBox "Your Automated Task successfully ran at " & TimeValue(Now), vbInformation
    
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True

End Sub
pittlei
  • 1
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • Also make sure all your `Range` and `Cells` objects reference to a workbook and worksheet. Otherwise Excel guesses wich workbook and worksheet you mean and it might guess wrong. Don't give Excel room for guessing and be as precise as possible. E.g. `wb.Worksheets("Sheet1").Range` – Pᴇʜ Oct 29 '20 at 07:42
  • thank you. that is a very good point – pittlei Oct 30 '20 at 14:34

1 Answers1

-1

Yes, it is most likely memory issue.

You will be better off using Task scheduler opening your .xlsm or .xlsb file every day. This is not exact answer to your question but a solution which will help. Just remember to include in your macro Application.Quit code after you are done.

  1. Open Start.
  2. Search for Task Scheduler, and click the top result to open the experience.
  3. Right-click the "Task Scheduler Library" branch, and select the New Folder option.
  4. Type a name for the folder. For example, MyTasks.
  5. Click the OK button.
  6. Expand the "Task Scheduler Library" branch, and select the MyTasks folder.
  7. Click the Action menu.
  8. Select the Create Basic Task option.
  9. In the "Name" field, type a short descriptive name for the task.
  10. Click the Next button.
  11. Select the Daily option.
  12. Click the Next button.
  13. Select the Start a program option to execute a script/Excel file.
  14. In the "Program/script" field, specify the path for the Excel file.
  15. Click the Finish button.

You are done

And, you will not regret it. I bet you will start using Task scheduler even more often for other automations as it has various triggers and is independent from any application running or not.

Short note about Excel

Excel anyhow have tendency to hangup after long unexpected time and it is hard to tell why. In my personal opinion it is such a enormous but excellent application, that developers for years scratch their heads of, how to solve these kind of issues. Nevertheless, the main focus in Microsoft is making everything simpler, more intuitive and cloud integrated.

On top VBA has been abandoned long time ago and considered by Microsoft as legacy solution. Despite new versions of Excel since v. 2010, no additional functionalities were included. If I am not mistaken since then in MS Office package it is still version 6.0. They just don't kill it because to many businesses rely on (sometimes excellent and very professional) important macros which cannot be easily replaced (or at all) with new paradigm of extensions written in JS in order to work everywhere.

Good luck!

Having problems? Ask me anything in comments section.

Kamil
  • 676
  • 5
  • 12