Define As String variably using If & ElseIf

[SamanthaNZ]

Hi there experts!

I have a preexisting VBA code to create and submit data files into a Snowflake Data Warehouse. Now, they'd like me to change the data file names to have a version suffix, to differentiate files in case we need to resubmit them during the month. For example "..._01", "..._02", "..._03" etc. It is very unlikely we would submit more than four times per month.

I have "Dim FileName As String". This currently includes the file location and file name which includes a fixed title component, the month and year. I would now like to define FileName to also include the appropriate suffix. If this can be achieved, I won't have to amend the entirety of the VBA code where FileName is repeatedly referred to. I have tried the code below, but when the macro progresses it won't allow me to save the new file as FileName, so I must not have defined it correctly.

Is it possible to change the definition of FileName depending on what files already exist?

Thank you so much!!
Samantha

 

[Akuini]

Hi, @SamanthaNZ, welcome to the Forum
It should be:

If Dir(Checkfile1) = "" Then

it means if Checkfile1 doesn't exist.
Do the same to all other Checkfile

 

[SamanthaNZ]

Hello Akunini! Thank you so much for your help. I made the changes, but am still getting the same error when I go to save the new data file referring to FileName. Any ideas would be amazing. ?

 

[Akuini]

Can you post your code as text not as an image?

 

[Alex Blakenburg]

You actually have multiple issues.

• Dir (CheckFile1) is returning ""
  because it needs ".xlsx" added to the name
  eg
  CheckFile1 = PublicLocation & Name1 & ".xlsx"
  
  
• ActiveWorkbook.SaveAs PublicLocation & FileName, 51
  Needs to be changed to
  ActiveWorkbook.SaveAs FileName, 51
  FileName already includes the filepath
  eg FileName based on CheckName1 returns > C:\Users\Public\Documents\APAC_Actual_Resin202108_01.xlsx

 

[SamanthaNZ]

Hi Akuini, my apologies, I will post the code as text in future. ?

Hi Alex, OMG that fixed everything!!! You are so clever. Thank you SO SO much. That is amazing, you've made my day! ??

 

[Alex Blakenburg]

And you have made mine. Glad we could help.