Compile Error - VBA

ADSkinner

New Member
Joined
Aug 18, 2018
Messages
19
Hello All....

I have a VBA code that should be taking worksheets and saving them each as their own excel file in a new folder. I had previously had this code working, but then made some modifications to my file, and as a result, this code is now giving me a "Compile Error: Wrong number of arguments or invalid property assignment" on this line of code: DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")

Here is the entire code as it is written today. Can anyone help me?


Code:
Sub SplitWorkbook()
'Updateby20140612
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim xWs As Worksheet
Dim xWb As Workbook
Dim FolderName As String
Application.ScreenUpdating = False
Set xWb = Application.ThisWorkbook
DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
FolderName = xWb.Path & "\" & xWb.Name & " " & DateString
MkDir FolderName
For Each xWs In xWb.Worksheets
    xWs.Copy
    If Val(Application.Version) < 12 Then
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        Select Case xWb.FileFormat
            Case 51:
                FileExtStr = ".xlsx": FileFormatNum = 51
            Case 52:
                If Application.ActiveWorkbook.HasVBProject Then
                    FileExtStr = ".xlsm": FileFormatNum = 52
                Else
                    FileExtStr = ".xlsx": FileFormatNum = 51
                End If
            Case 56:
                FileExtStr = ".xls": FileFormatNum = 56
            Case Else:
                FileExtStr = ".xlsb": FileFormatNum = 50
        End Select
    End If
    xFile = FolderName & "\" & Application.ActiveWorkbook.Sheets(1).Name & FileExtStr
    Application.ActiveWorkbook.SaveAs xFile, FileFormat:=FileFormatNum
    Application.ActiveWorkbook.Close False
Next
MsgBox "You can find the files in " & FolderName
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
you need to add () after Now
 
Upvote 0
I added the () as you suggested, but still getting the same error

Code:
DateString = Format(Now(), "yyyy-mm-dd hh-mm-ss")
 
Upvote 0
Do you have Option Explicit turned on?
If so, you will need to declare the DateString variable before using it, i.e.
Code:
Dim DateString as String
 
Upvote 0
No, I do not have that option turned on. However by turning on that option and adding in that declaration, it is still giving me the error at the same position. #frustrating
 
Upvote 0
Which version of Excel are you using?
Are you using Excel for Windows or Mac?

It might be a "red herring", as there is nothing in there that looks like it should be problematic.
Try this short macro and see if this works. If it does, then the issue is actually not that part of your code:
Code:
Sub Test()
    Dim DateString As String
    DateString = Format(Now(), "yyyy-mm-dd hh-mm-ss")
    MsgBox DateString
End Sub
 
Upvote 0
I had saved as Excel 97-2003 Workbook, and have also tried saving it as just Excel Workbook. In both instances, I still get caught on the same line.

I tried the test code you provided MrExcel MVP, and running that also gave me the same error: Compile Error: Wrong number of arguments or invalid property assignment.
 
Upvote 0
I didn't ask the format you are saving the file in, but rather which version of Microsoft Excel you are running.
Also, is it a Mac or Windows based computer?
 
Upvote 0
Also, from the VB Editor, do the following.
Go to the Tools menu and select References.
Which libraries are checked?
Do you see any listed near the top that say "MISSING..."?
 
Upvote 0
Check that you don't have any subs or variables called either Format, or Now
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,226
Members
452,620
Latest member
dsubash

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