excelvbafilenamessave-as

excel filename by vba script causes 2 filenames

I have a workbook with a script which save an excel document with the filename on 2 different locations. I have tested and tried the script and it worked perfectly. Today I used the same script for 3 older files and I noticed the files were not saved with the same filename (and the first not on the correct, specified location. I do not understand why. I have rights to change files or add files to both locations.

sub replace()

[some declarations]

'bepaalt het bestandspad en de bestandsnaam van het geopende xlsm bestand
wbfilepath0 = ThisWorkbook.Path
wbFilepath1 = "I:\KMAR\LTC\SPT\IV&CIS\IV PLANS\BICC_BVI_IMPORT\03_TCB\"
wbFilename0 = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5)

'sluit een eventueel geopend GAB Database .xlsx bestand zonder wijzigingen op te slaan
myWB = wbFilename0 & ".xlsx"

For Each WB In Workbooks
    If WB.Name = myWB Then
        WB.Close SaveChanges:=False
        Else
    End If
Next WB

[some lines of code]
wbfilepath0 = ThisWorkbook.Path
wbFilepath1 = "I:\KMAR\LTC\SPT\IV&CIS\IV PLANS\BICC_BVI_IMPORT\03_TCB\"
wbFilename0 = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5)

'kopieert de 2 benodigde tabbladen naar een nieuw bestand
ThisWorkbook.Sheets(Array("Hoofdbestand", "Database")).Copy

Application.DisplayAlerts = False

'Onderstaande slaat het nieuwe bestand 2x op maar op verschillende locaties. Als .XLSX bestand en kan alleen als het bestand met deze naam niet al geopend is, daar is aan het begin van deze procedure op gecheckt
With ActiveWorkbook
.SaveAs Filename:=wbfilepath0 & wbFilename0 & ".xlsx", FileFormat:=51
.SaveAs Filename:=wbFilepath1 & wbFilename0 & ".xlsx", FileFormat:=51
.Close

[some other lines of code]

end sub

wbFilename0 = GAB Database 2023.xlsm

The first saveas results is '2023 GAB Database 2023.xlsx' where the second saveas result is 'GAB Database 2023.xlsx'

and where the first saveas saved the file not in this directory "[..]\118 (UPD WEKELIJKS) Update\2023" but in this directory: "[..]\118 (UPD WEKELIJKS) Update\"

Is there any reason in this code for this? The file was not opened by someone.

I did this for the 2024 file and it worked fine for both files.

Edit: in the directory properties where the files have to be save, I saw 'read-only' is checked. However, I can read/write/save files to the directories with no problem, but could this be a problem for the script? directory properties

Solution

  • The file of the first SaveAs seems to have two 2023 which is according to you is wrong, this is because the file path also includes another 2023 and missing a \ behind the 2023 I strongly believe.

    FIX : the directory should be *[..]\118 (UPD WEKELIJKS) Update\2023* to make sure the filename does not stick with the 2023

    The first file did not save in the file location as you want maybe because you use thisworkbook.path meaning it will use the exact path of the workbook your code is written on.

    FIX : You could try to be more specific with the desired path instead of thisworkbook.path