Type Mismatch error when trying to save

Xanosect

New Member
Joined
May 10, 2022
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
I've read through other threads on this but can't seem to to piece together what I'm missing. Everything seems to run fine until it gets to the save as line where it throws a type mismatch error. There are steps taken between these two Subs but they seemed the two that are relevant to the issue.

I'm creating a folder and then trying to save a workbook to it. The folder creation works but the save errors out.

Variables Folderuse, Currentmonth, Territory, Currentbook are all defined as strings and all appear to be passing correctly (except for Currentbook which doesn't get to because the error occurs on the Activeworkbook.SaveAs line).

Folderuse = "April 2022"
Currentmonth = "April"
Territory = "TC1 PNW"

Eventually the program is going to be churning out multiple saved files for different geographic regions so I need to save with variables that will count through in the process.

VBA Code:
Sub Makefolder(Folderuse)
    Dim fdObj As Object
    Application.ScreenUpdating = False
    Set fdObj = CreateObject("Scripting.FileSystemObject")
    If fdObj.FolderExists("C:\Users\10000\Documents\CI Documents\POS File\Month End Reporting\" & Folderuse) Then
        MsgBox "Found it.", vbInformation, "Kutools for Excel"
    Else
        fdObj.CreateFolder ("C:\Users\10000\Documents\CI Documents\POS File\Month End Reporting\" & Folderuse)
        MsgBox "It has been created.", vbInformation, "Kutools for Excel"
    End If
    Application.ScreenUpdating = True

End Sub

VBA Code:
Sub name(Currentmonth, Territory, Currentbook, Folderuse)
ActiveWorkbook.SaveAs Filename:="C:\Users\10000\Documents\CI Documents\POS File\Month End Reporting\" & Folderuse \ Territory & " Mid Month " & Currentmonth, FileFormat:=51
Currentbook = ActiveWorkbook.name
End Sub
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
I think you still need to add...
Code:
& ".xlsx"
after the Currentmonth even though U declared the file format. HTH. Dave
 
Upvote 0
Folderuse \ Territory is an issue

It should be:
Folderuse & "\" & Territory & "\" &
 
Upvote 0
Folderuse \ Territory is an issue

It should be:
Folderuse & "\" & Territory & "\" &
That was it. no second slash after territory but the & "\" & missing between the final folder and the workbook name. thanks for spotting that for me.
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top