sql - VB.net Faster way of creating folders, subfolders and Excel files within those folders -

i'm using vb 2010, sql server 2012 excel 2010.

the users @ company have requirement use specific excel report hospitals , doctors.

i have table in sql server contains data , name of person assigned hospital/doctor office, hospital name , lastly address of hospital name/doctor office.

the folders can have following structure:

c:\test\assignee name\hospital name\hospital or doctor address\hospital or doctor address.xlsx 

alternatively hospital name blank ending following structure:

c:\test\assignee name\hospital or doctor address\hospital or doctor address.xlsx 

here code have until now, takes 4 hours 4 of these assignee folders , reports generated.

i need have 26 assignee folders, 3831 subfolders, 7281 files.

i'm looking faster way of doing this, please.

here code:

private sub create_cqm_folders_vb()

private sub create_cqm_folders_vb()     dim t1 datetime = datetime.now     dim t2 datetime     dim duration timespan             dim connectionstring string = "server=server1;initial catalog=database1;integrated security=sspi"      using conn new sqlconnection(connectionstring)         dim command new sqlcommand("select distinct [chase assignment],[folder grouping],standardprovideraddress2 sites_assigned", conn)         dim count integer = 1         conn.open()          dim myreader sqldatareader = command.executereader()         //myreader = command.executereader         while (myreader.read())             stringvar = trim(myreader(0).tostring)             stringvar2 = trim(myreader(1).tostring)             stringvar3 = trim(myreader(2).tostring)              if string.isnullorempty(stringvar2)                 mkdir("c:\test\" & stringvar & "\" & stringvar3)                 call create_main_cqm_report()                  if val(xlappsumm.version) < 12                     //you use excel 2000-2003                     fileextstr = ".xls" : fileformatnum = -4143                 else                     //you use excel 2007                     fileextstr = ".xlsx" : fileformatnum = 51                 end if                  //save each of reports corresponding paths                  dim xlsxfilename string                 xlsxfilename = stringvar3                 xlworkbooksumm.saveas("c:\test\" & stringvar & "\" & stringvar3 _                          & "\" & xlsxfilename & fileextstr, fileformat:=fileformatnum)                  xlworkbooksumm.close()                 xlappsumm.quit()                 // end if             else                 mkdir("c:\test\" & stringvar & "\" & stringvar2 & "\" & stringvar3)                 call create_main_cqm_report()                  if val(xlappsumm.version) < 12                     //you use excel 2000-2003                     fileextstr = ".xls" : fileformatnum = -4143                 else                     //you use excel 2007                     fileextstr = ".xlsx" : fileformatnum = 51                 end if                  //save each of reports corresponding paths                 dim xlsxfilename string                 xlsxfilename = stringvar3                  xlworkbooksumm.saveas("c:\test\" & stringvar & "\" & stringvar2 & "\" & stringvar3 _                          & "\" & xlsxfilename & fileextstr, fileformat:=fileformatnum)                  xlworkbooksumm.close()                 xlappsumm.quit()                 //end if                 count = count + 1             end if         end while         conn.close()     end using      t2 = datetime.now     duration = t2.subtract(t1)      //get difference , display        messagebox.show(string.format("elapsed time:  {0} hours, {1} minutes, {2} seconds", duration.hours, duration.minutes, duration.seconds))     messagebox.show("all reports have been created") end sub 

private sub create_main_cqm_report()

private sub create_main_cqm_report()     //show cqm report in datagridview     try         dim connstring string = "server=server1;initial catalog=database1;integrated security=sspi"         dim conn new sqlconnection(connstring)         dim dbcomd new sqlclient.sqlcommand("select * rpt_assigned_reports order [chase assignment], [folder grouping], standardprovideraddress2", conn)          conn.open()         dbcomd.commandtype = commandtype.text         // dbcomd.commandtimeout = 900          dim adapters new sqldataadapter(dbcomd)         dim dset new dataset()         adapters.fill(dset)         datagridview1.datasource = dset.tables(0)         datagridview1.allowusertoaddrows = false          dim dv new dataview()         dv = dset.tables(0).defaultview         // dataview datatable         if string.isnullorempty(stringvar2)             dv.rowfilter = "[chase assignment] = '" & stringvar & "' , standardprovideraddress2 = '" & stringvar3 & "'"         else             dv.rowfilter = "[chase assignment] = '" & stringvar & "' , [folder grouping] = '" & stringvar2 & "' , standardprovideraddress2 = '" & stringvar3 & "'"         end if          conn.close()      catch ex exception         messagebox.show("error while retrieving records table..." & ex.message, "retrieve records")             //system.windows.messagebox.show("records retrieved")     end try      dim misvalue object = system.reflection.missing.value     dim lastrow long     dim curr_date string = datetime.now.tostring("mm.dd.yyyy hh.mm.ss")      copyalltoclipboard()     xlappsumm = new excel.application()     xlworkbooksumm = xlappsumm.workbooks.add(misvalue)     xlworksheetsumm = xlworkbooksumm.sheets("sheet1")     xlworksheetsumm.range("a1").pastespecial(excel.xlpastetype.xlpasteall)     //xlworksheetsumm.columns("a:a").delete()     xlappsumm.visible = false      xlworksheetsumm         //save each of reports corresponding paths         .columns("a:an").entirecolumn.autofit()     end      my.computer.clipboard.clear()      //clear datagridview next sheet     call clear_dgv1() end sub