Re-execute if the workbook exists or not using vba Excel -


i want execute, if workbook exists re- run if not exists create workbook.

i have uniques values(x) , array(names). need compare them if both equal if not has create workbook name of array(names) not had in uniques values(x)

my code:

sub mac()  dim c integer dim x range dim s_agingscm string dim array_scm_aging variant dim newbook workbook dim newbook_scm workbook dim master_workbook workbook dim rngcopy_aging range dim rngfilter_ws2 range  c = lbound(array_scm_aging) ubound(array_scm_aging)             set master_workbook = thisworkbook             s_agingscm = array_scm_aging(c, 1)             set x = master_workbook.sheets("bass").range("ay" & c)                     if x = s_agingscm                                                                                 rngfilter_ws2                                  .autofilter field:=32, criteria1:="<>(a)  0 - 360", operator:=xlfiltervalues                                 .autofilter field:=37, criteria1:=s_agingscm, operator:=xlfiltervalues                           set rngcopy_aging = .specialcells(xlcelltypevisible)                                              .autofilter ' switch off autofilter                          end                           rngcopy_aging.copy newbook.worksheets("aging inventory").cells(1, 1)                          application.displayalerts = false                               else                     dim fso: set fso = createobject("scripting.filesystemobject")                     dim folder: set folder = fso.getfolder("c:\")                     dim file, filenames                                         dim rngcopy_scmaging range                                         each file in folder.files                             if right(file.name, 4) = "xlsx"                             filenames = filenames & file.name & ";"         ' give list of filenames                             end if                     next                      if instr(filenames, s_agingscm) = 0                                                   newbook_scm                                                              set newbook_scm = workbooks.add                             .title = s_agingscm                           newbook_scm.worksheets("sheet1").name = "aging inventory"                             rngfilter_ws2                                  .autofilter field:=32, criteria1:="<>(a)  0 - 360", operator:=xlfiltervalues                                 .autofilter field:=37, criteria1:=s_agingscm, operator:=xlfiltervalues                                  set rngcopy_scmaging = .specialcells(xlcelltypevisible)                                                        .autofilter ' switch off autofilter                             end                                  rngcopy_scmaging.copy destination:=newbook_scm.worksheets("aging inventory").cells(1, 1)                                                        .saveas filename:="kpi" & " " & s_agingscm & " " & format_date & ".xlsx"                           application.displayalerts = false                           newbook_scm.close                                                    end '                      else                     end if  end sub

i stuck here since 2 days. want if workbook exists overwrite new workbook or else if not exists create new workbook.

can please me out.

a quick way placing: -

if fso.fileexists(application.defaultfilepath & "\kpi" & " " & s_agingscm & " " & format_date & ".xlsx")     fso.deletefile application.defaultfilepath & "\kpi" & " " & s_agingscm & " " & format_date & ".xlsx", true end if

above line

.saveas filename:="kpi" & " " & s_agingscm & " " & format_date & ".xlsx"

but not account if file not deleted (i.e. open)


-

Comments

Post a Comment

Popular posts from this blog

PySide and Qt Properties: Connecting signals from Python to QML -

i trying run simple pyside appliccation design made in qml. call function in qml python in separated thread. i have following qml view: import qt 4.7 rectangle { visible: true width: 100; height: 100 color: "lightgrey" text { id: datestr objectname: "datestr" x: 10 y: 10 text: "init text" font.pixelsize: 12 function updatedata(text) { datestr.text = qstr(text) console.log("you said: " + text) } } } here application: some imports.... #!/usr/bin/python import sys import time pyside import qtdeclarative, qtcore pyside.qtgui import qapplication pyside.qtdeclarative import qdeclarativeview the qthread worker.... #worker thread class worker(qtcore.qthread): updateprogress = qtcore.signal(int) def __init__(self, child): qtcore.qthread.__init__(self) self.child = child def run(self):
Read more

c# - DevExpress.Wpf.Grid.InfiniteGridSizeException was unhandled -

Image
when using devexpress, see error: devexpress.wpf.grid.infinitegridsizeexception unhandled message="by default, infinite grid height not allowed since grid rows rendered , hence grid work slowly. fix issue, should place grid container give finite height grid, or should manually specify grid's height or maxheight. note can avoid exception setting gridcontrol.allowinfinitegridsize static property true, in case grid run slowly." the problem dxgrid has infinite height. to fix, set height non-infinite. snoop absolutely invaluable this: if "height" xaml element infinite (i.e. 0 or nan ), can set using one of following: option 1: height="{binding path=actualheight, relativesource={relativesource mode=findancestor, ancestortype=uielement}}" option 2: verticalalignment="stretch" option 3: height="auto" hint: use verticalalignment="stretch" if child of grid <rowdefinition height=
Read more

scala - 'wrong top statement declaration' when using slick in IntelliJ -

Image
i'm porting project scala 2.9 2.10, therefore have use slick instead of scalaquery. i'm using slick 2.1.0 since supports ms access. according this tutorial , upgrade guide changed robs object class , added val robs : why error message wrong top statement declaration , how rid of it? edit: i'm new scala... according this question seems can't put val outside of methods or classes, right? code above directly in packge. right approach slick then? should move code in class or trait? according this changed val robs = tablequery[robs] to object robs extends tablequery(new robs(_)) {} . no warnings or errors anymore. :)
Read more