Posts by novice_user

    Hi Roy,


    So using the example above me.tb_folderpath already exists (it is the companys L drive), so the additional folders I want to create are the year --> the quarter --> then the company code. So essentially I am trying to create 3 new folders all inside of each other. Are you saying first I would have to create the year then If statement again for the Qtr & then if statement for the company code?


    Thanks,


    Mel

    Hi Everyone,


    I keep receiving the run-time error '76' saying path is not found & I can not understand why. I have tried using as many sites as possible to understand but it doesn't make sense.


    Background:

    I am writing the code in a userform & the individual who runs the macro has the option to change the file path (using combo boxes for the correct year & quarter which it is ran). I use an IF statement to check whether the directory already exists (majority of the time it doesn't) and then using MkDir to create my file path, which is the point it fails at. I have highlighted in red the point it fails. Please see short code below:


    The MkDir filepath is where it fails. Does anyone have any suggestions to why it may be failing? I will put the whole code below to help put somethings into context for you. (the me.tb_folderpath etc are the combo & text boxes where the individual can identify which paths they want the files to be saved in). If anyone would like more information/additional code please let me know.



    I have tested the filepath & it returns exactly what I want it too, so I am unsure how to resolve this issue.


    Thanks,


    Mel:)

    Hi Roy,


    Thanks for this! That is great however as this needs to run for 10-17 files, I do not want to write the name for every file as I would like the macro to run in the background. Is there a code which brings up the file dialog to select my path for the files to save in & then save the file name as "(company code) TB Flux 2020 Q1" where the company code changes based on this: Work.Cells(i, 4).


    Also, I use VBA to open the template because it contains a format which I need to use for every country/company code. So the template brings in information about the accounts I am analysis. If you agree that there is a better way could you please explain further how & why.


    In addition, could you please explain why you need to use the if statement? Sorry if this seems like a silly question, as mentioned I am pretty novice with VBA & it is all self taught from online forums so there are several gaps in my knowledge. Also, thanks for the links to your website! These will definitely come in handy & I will be sharing this with the individuals in my company who are keen to learn more!


    Thanks,


    Mel

    Hey everyone!


    This is my first post so I apologize if I have posted wrong/not followed guidelines.


    I am having trouble with saving my excel file. I have used SaveAs before & it has always worked fine so I would like your help with solving this please!


    Background: I am creating a file which uses an excel template to create multiple files. I need to create 10-17 files each month with new data from SAP, so to save time I thought of creating one file which uses Do loop (for the changing variable of company code) to do this. It incorporates SAP Scripting. The debugger pop up box says the following:

    "Run-time error '1004':

    The file could not be accessed. Try one of the following:

    - make sure the specified folder exists.

    -make sure the folder that contains the file is not read-only.

    make sure the filename and folder path do not contain any of the following characters: < > ? [ ] : or *

    -make sure the filename and folder path do not contain more than 218"


    As mentioned I am opening an excel template to do this & saving the file once I input some information. I am then calling the macro which has the SAP scripting in it. The reason I am saving prior to calling the macro is so that in the SAP scripting macro is it easy to distinguish between my files. The suggested name with path is only 133 characters. I have removed the path for privacy of the company.


    The code is:

    Please help! Is there anything clearly wrong with the code which can explain why I am having this error!?


    Thanks ,


    Mel:)