excel vba 2016 Type mismatch 13

[JAWHARRAH]

Good day

I am a newbie and I am desperate.
I keep getting a error type mismatch 13 at runtime but I cannot understand why.
My code is going through the worksheets and I am referencing the codename and I am running into an error.
PLEASE NOTE IT PROCESSES FINE IF I STEP THROUGH THE CODE.
However at runtime I get the type mismatch error.
I have put where it causes the error in **.

my code is

    For Each ws In ActiveWorkbook.Sheets
        Application.DisplayAlerts = False
        swsCode = Mid(ws.CodeName, 6, 2)
'        If swsCode = "" Then MsgBox "It's empty"
'        If swsCode = "" Then swsCode = "1"
'        MsgBox swsCode

**        iwsCode = CInt(swsCode)  **
       
        If iwsCode > 5 Then
            ws.Copy
            xfile = FolderPath & "\" & Application.ActiveWorkbook.Sheets(1).Name & ".csv"
            ActiveWorkbook.SaveAs xfile, FileFormat:=xlCSV
            ActiveWorkbook.Close savechanges:=False
        End If
   
    Next

Please please help me.
Let me know if you need any more info

Jo

 

[JAWHARRAH]

With your original code, when you get the error, Debug and hover over the variable name swsCode in the yellow line. What value shows in the pop-up?

Thanks for the tip. I hope I do it right

 

[Peter_SSs]

I hope I do it right

You showed the pop-up correctly, but you have quoted my post multiple times and your own post that didn't need quoting. I've fixed that for you.

The error pop-up shown the problem. You cannot convert "" to an integer. It would seem that you have a sheet whose codename is less than 6 character long.

 

[JAWHARRAH]

I will give you the background.
My Workbook with the code is called "MATERIALS PLAN.xlsm.
When I run the code it does many processes. It coverts word docs to *.txt files.
Then you import the txt file into a NEWLY CREATED workbook, lets call it 111222.xlsx.
during the import stage it generates 5 new worksheets - Sheet1, Sheet2, Sheet3, Sheet4, Sheet5. Each sheet has the same data but it has been manipulated by extracting, deleting, copying etc. The worksheets are renamed, however the code names remain the same.
The next stage the user selects a value and based on his/her selection a new worksheet is created Sheet6. This is repeated as many times as required - it may be up to 1 - 7 times.
The final stage is the user selecting the button to export from Sheet6 onwards.
When he/she selects this button, it prompts the user asking if they are finished and want to send the files to csv files.
After selecting yes it starts the process of looking past sheet5 and saving each sheet as an individual csv file.
Like I said, the error type mismatch 13 happens. When going into debug, the above line is at error.
The thing that confuses the heck out of me is, why is it when I reset then run THAT subroutine by hitting F5, it continues and there is no further error. All files are successfully created.
Even when it was in error all of the sheet codenames show the correct value???
I am thanking you all in advance for your assistance in this.

 

[Peter_SSs]

Hidden sheets?

Looks like you have tried to do something similar to this before, but what happens if you replace the whole For Each ws ... next block with this?

For Each ws In ActiveWorkbook.Sheets
    Application.DisplayAlerts = False
    swsCode = Mid(ws.CodeName, 6, 2)
    If Len(swsCode) = 0 Then
      MsgBox "Sheet name: " & vbTab & ws.Name & vbLf & "Codename: " & vbTab & ws.CodeName & vbLf & "Sheet visible: " & vbTab & IIf(ws.Visible, "Yes", "No")
    Else
    
      iwsCode = CInt(swsCode)
    
      If iwsCode > 5 Then
          ws.Copy
          xfile = FolderPath & "\" & Application.ActiveWorkbook.Sheets(1).Name & ".csv"
          ActiveWorkbook.SaveAs xfile, FileFormat:=xlCSV
          ActiveWorkbook.Close savechanges:=False
      End If
    
    End If

Next

 

[JAWHARRAH]

Hidden sheets?

Looks like you have tried to do something similar to this before, but what happens if you replace the whole For Each ws ... next block with this?

For Each ws In ActiveWorkbook.Sheets
    Application.DisplayAlerts = False
    swsCode = Mid(ws.CodeName, 6, 2)
    If Len(swsCode) = 0 Then
      MsgBox "Sheet name: " & vbTab & ws.Name & vbLf & "Codename: " & vbTab & ws.CodeName & vbLf & "Sheet visible: " & vbTab & IIf(ws.Visible, "Yes", "No")
    Else
   
      iwsCode = CInt(swsCode)
   
      If iwsCode > 5 Then
          ws.Copy
          xfile = FolderPath & "\" & Application.ActiveWorkbook.Sheets(1).Name & ".csv"
          ActiveWorkbook.SaveAs xfile, FileFormat:=xlCSV
          ActiveWorkbook.Close savechanges:=False
      End If
   
    End If

Next

Thanks for that code, I really appreciate it.
While you did get me thinking you had resolved the issue because, in fact, the first 3 sheets are hidden, however this did not resolve the main issue. (it is tidier though re msg(no error)
I tested the code with sheets visible. The msgbox came up with expected results*, but no other error. *it displayed the msg for every sheet stating the tab name but the code name had no result
I tested the code with sheets hidden. The msgbox did not come up nor was there any error.
Both resulted in no files being created.
I did the same test through debug and both resulted in files being created.
I have no idea why it is not picking up the codename?
You have tried so much to help and I do appreciated it, although I am getting stressed about this.
I created this program at home as a favour for a colleague and to save time for the people at work. So fare the guy loves it it has saved him hours of work. From 4 hours to five minutes.
If you have some more time to see why the code name has no value, I would appreciate it.

 

[Peter_SSs]

If you have some more time to see why the code name has no value, I would appreciate it.

I think the only way that I could investigate further is if I had access to a copy of a file that was displaying this behaviour. Is it possible for you to upload a copy with any sensitive data removed/disguised to DropBox, Google Drive etc and provide a shared link here?

 

[JAWHARRAH]

Thanks Peter.
I will do that tomorrow.
Regards
Jo

 

[JAWHARRAH]

HI Peter
Thank you for looking into this.
instructions for app are there. please use import text and follow prompts.
if you want to go straight to code on the page programming there is a hidden button before the destruction msg.
let me know if any issues.
don't forget I'm a newbie so code may make you sick
Excel - Google Drive

 

[Peter_SSs]

For me it is impossible to use that without knowing what is going on.

 

[JAWHARRAH]

Hi Peter
Ok, no problem.
I have created the excel file to the point where the error happens.
Open MATERIALS PLAN, (userform will open)
Open 111222_2020_09_06.
Go to the export to CSV page on MATERIALS PLAN userform.
On MATERIALS PLAN select the export to csv button.
It will ask if you are ready to export.
Once you say ok, it goes through the code you gave me, (showing msgboxes with no sheet name).
It should be exporting 5 csv files.

Excel - Google Drive

drive.google.com