Posts by mar050703

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • File System Object

    HI Carim


    Thankyou so very much for your patience and helpful guidance.


    It worked, but by changing the line

    Code
    Set wb = Workbooks.Open(Filename:=cmMyPath & file)


    to

    Code
    Set wb = Workbooks.Open(Filename:=file)

    ,


    as the first line now had the path twice (I guess because of fsopath and cmMyPath)?


    Thanks again for aiding my learning.

  • File System Object

    Carim


    Thank you for your comment, I changed

    Code
    Dim cmMyFile as String


    to

    Code
    Dim cmyMyFile as variant


    that did not change the outcome, so maybe I misunderstood.


    Look forward to the explanation that will solve this for me.

  • File System Object

    Thanks Carim


    I tested this, as you suggested in a module of its own, and first instead of opening workbook etc, I called a msgbox to give the file name. This worked, however...


    When I implemented the line


    Code
    Set wb = Workbooks.Open(Filename:=cmMyPath & cmMyFile)


    again it opens the 1st file (Cash Management FEBRUARY 2019 TEST) on each next file.


    Almost there, if you could suggest the issue I really would be grateful.


    Thanks

  • File System Object

    Hi Carim, Yes I have done that. 1st thing I checked. However, I can now confirm by referencing fso like this:

    Code
    Dim fso As Object
Dim fsopath As Scripting.Folder


Set fso = New FileSystemObject
Set fsopath = fso.GetFolder(cmMyPath)


    I now do not get any errors, but it keeps opening up the same file, it is not scrolling through to the next.


    so

    Code
    cmMyFile = Dir(cmMyPath & MyExt)


    appears to be always the 1st file in the sub folder Maureen. (for testing purposes there are 4 files, and only 2 are like "Cash Management"


    Do I need to reference fso.getfiles or something?


    Thanks

  • File System Object

    Hi Carim Thanks for your comments, and no I don't mind, we are all learning, and as I am self taught in VB, even more so.


    Your 3rd point Cell I3 is looking at the folder location - it reads:

    Code
    =CONCATENATE("C:\Users\",I2,"\Dropbox\Accounts Miqlat\")


    I2 being the username.
    This line works, as I use it in other codes within this project.


    Your 2nd Point, I refer to MyFile = "Cash Management v1.4.xltx", is what I need to do on the xlsx file I also do within this xltx file - Please note that that the xltx file is in folder refered to in I3, and the xlsx files will be in a subfolder "Maureen).


    The xltx and the mentioned xlsx workbooks both have 2 sheets ("Expenses" and "Validate") the template become and xlsx workbook, when completed.


    As requested, please find the entire code.


    Please note that when I posted the original yesterday, I noticed I had forgotten to declare fso as an object.



    Any pointers and help would be greatly appreciated.

  • File System Object

    Hi Experts


    I am trying to write some code so that it will loop through all the files in the folder and do certain things is the file name is like...


    My Code is



    I am sure I am missing something, but if someone could point the way, I would be grateful.

  • Count if Filename Like

    Hi there


    I am trying to count the number of files in a particular folder whereby the filename is like something.


    When I have the total count, I need the code to loop through each file and do something


    I am having problems counting the files though.
    My Code looks like this:



    Please note there may be other files in said folder that do not contain "Cash Management"


    Maybe I don't need to count, I just need to loop through each file

  • Find Replace Sheet Array

    Hi KjBox.


    Thanks for the code.


    On sheet Validate1,I also have a ListObject, and is just the driving force for Columns 5 & 6 on the 16 sheets I refer to. It is essentially made up of:


    Header Row - Being the Categorys (Column 5)
    DataBodyRange - Being Sub Categorys (Column 6), so column 6 choice depends of the answer of Column 5.


    Wouldn't your code also the check ListObject(1) on the Validate1 Sheet?


    Hope that make sense?

  • Find Replace Sheet Array

    Hi There,


    Thanks graha_karya I have implemented your solution, and managed to sort the issue. I am sure there is a better/quicker (runtime) than this, so if anyone has a suggestion I would be happy to read.


    My final code is:


    Please note the second "For Each" statement is looking at the sheet and not the ListObject within that sheet (which was my ideal).

  • Find Replace Sheet Array

    Hi, and Thanks for your comments.


    Just to be clear, aside from the 3 sheets which are in the aforementioned Array, I have another 10 sheets each with an Excel Table (Listobject), where by I would need to locate combobox1.value within the 5th Column of that List object (ideally just within Databodyrange).


    Therefore I am unable to change the above code, as that would just look at column 5 of the original sheets in the Array.


    Apologies if I did not make myself clear.

  • Find Replace Sheet Array

    Thanks Carim,


    The final code was

    Code
    For Each Sht In Worksheets(Array("Centre", "Consolidated", "Validate1"))
   Sht.Cells.Replace what:=ComboBox1.Value, Replacement:=TextBox1.Value, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True, _
    SearchFormat:=False, ReplaceFormat:=False
Next Sht


    the error was simply the sheet name was "Consolidated" and not "Consolidate"


    However, I now have a much larger problem, and looking for the quickest way for the code to work.


    I have 10 sheets, all with Excel Tables, and I would like the code to only check a certain column (always column 5) and do the same find replace. (Table per sheet)


    So how do I get it to loop through these 10 tables (please note within the workbook there are more than 10 tables.


    Thanks for your help

  • Find Replace Sheet Array

    Hi,


    I am trying to write code that will find something based on Combobox, and replace with the textbox value. I am trying to do this over an array of sheets.


    I am using the following code:

    Code
    For Each Sheet In Array("Centre", "Consolidate", "Validate1")
   Sheet.Cells.Replace what:=ComboBox1.Value, Replacement:=TextBox1.Value, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True, _
    SearchFormat:=False, ReplaceFormat:=False
Next Sheet


    however I get the object required error on the

    Code
    sheet.cells.replace…

    line


    once I get this working I will also need to do the same on multiple excel tables (10 of them).


    Thanks for your help

  • Check if file exists.

    All Solved, I changed the code so it looked at a filesystemobject, therefore the new code is:


    Code
    If fso.fileexists(SvPath & FileN & ".pdf") = True Then
    'If Dir(SvPath & FileN & ".pdf") <> "" Then
        Do
            n = n + 1
            NewFile = FileN & " (v" & n & ")" 'New File if Exists
        Loop Until Not fso.fileexists(SvPath & NewFile & ".pdf")

  • Check if file exists.

    Hi


    I am trying to get save as PDF and the actual file, and if it the file already exists, then give it a version 1 (or more).


    My code is as follows:


    However, the coding is happy to give a (v1), but then says file already exists and does not increase it to (v2).


    If I change

    Code
    loop until NewFile = ""

    it just keeps scrolling through the loop


    Any suggestions please?

  • VBA Excel Tables

    Carim


    Of course, I was being silly and used DataBodyRange before the ListColumns.


    Sorry, one last question, where I choose field 7 as the filter column, can I refer again to the Column Name ("End Date")


    Something like


    Code
    Sheets("Input").ListObjects(1).Range.AutoFilter field:=ListColumns("End Date"), Criteria1:=""

  • VBA Excel Tables

    Hi,


    I trust someone will be able to help me,


    I currently have a piece of code, which refers to an Excel Table, and I filter a column to give me a new range, but I am currently filtering using the column number;


    What I would like to do is use the refer to the column name, but I keep getting Run Time Error 13.


    The code I have is:

    Code
    Dim FRng As Range


Sheets("Input").ListObjects(1).Range.AutoFilter field:=7, Criteria1:=""
Set FRng = Sheets("Input").ListObjects(1).DataBodyRange.Columns(2).SpecialCells(xlCellTypeVisible)


    The error is the final line - I want to change columns(2) to Columns("Employee").


    I have also tried ListColumns("Employee"), but that gives Run Time Error 438


    The only reason for the change so that if columns are added to the left in the future, it will still use the correct column

  • Loop though different Ranges

    Hi There,


    I am sure there is a better, and faster way for my code to work. What I am trying to do, is loop through 4 different ranges, and clear contents.


    What I have at present is:


    Please can someone suggest a better way - I am sure it has to do looping through the ranges.


    Thanks in advance