Macro not working

You are only changing the file name if the current file name is 15 characters in length. Can you confirm that the file name is this long?

IHi Lorenda

there is no other code.  I noted out the reformat code so I can trouble shoot. Yes about the file name. The orginal will be:
deal (plus date) so deal20091006
the first rev will be deal20091006001
the 2nd will be 20091006002
the 3rd 20091006003

The file naming structure will always be the same

Sorry the original file will be dealmac20091006
the first rev will be dealmac20091006001
the 2nd will be dealmac20091006002
the 3rd dealmac20091006003

Hi Lorenda

Ok I have got it it checks for lenght 15 , but since it is adding length on the second files it should check for length 18!!

I'm walking through the code now - I will post issues as I find them

.Body = "Here is the deal CANCEL AND CORRECT original file name attached" currentFileName

Syntax error - change this line to

.Body = "Here is the deal CANCEL AND CORRECT original file name attached" & currentFileName

And yes - your post above is the only other issue I see. If you change

ElseIf Len(currentFileName) = 15 Then

to

ElseIf Len(currentFileName) = 18 Then

you should be good.

Hi Lorena
Ok on other thing. As you can see the fist part of the code saves the file with a new name. I then reformat the file and give it a little bit of a change,I was able to do this before by using dealmac" & format(Date, "yyyymmdd") & ".xls", or deal " & format(Date, "yyyymmdd") & ".csv", for the seconf format. Not that I am incrementing, is it possible to reuse the file name under from the first saving. So the first file will be named dealmac20091006001. I save the file to the shared drive, send it then reformat it. How can I easily tell it to save the file deal20091006001or --02 or 003 ?

Are you saying that if you are running this on a file called deal20091006002.xls, you want to be able to save it as deal20091006002.csv?

Hi Lorenda

Sort of , the first part saves the code saves the file  as dealmac20091006 + the increment 001,002 or 003 as format xls. the 2nd part of the macro, reformats the file, adding columns etc. the I want to save it as deal20091006 plus the increment.
I am using this
ActiveWorkbook.SaveAs FileName:="C:\Documents and Settings\rsernowski.R-SERNOWSKI\Desktop\deal" & newFileName & ".csv", FileFormat:=xlCSV
that is not working right as it is duplicating the word deal. Actually I need it to remove the word MAC. Hope this makes sense

If I am understanding, this should work. I've done it in steps so you can more easily follow what I'm doing

Dim a As String
 
a = Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) - 4) 'removes the .xls
a = Replace(a, "mac", "") ' finds and removes the mac
a = a & ".csv" ' adds the csv

                                              

1:
2:
3:
4:
5:

ActiveWorkbook.SaveAs FileName:="C:\Documents and Settings\rsernowski.R-SERNOWSKI\Desktop\deal" & newFileName & ".csv", FileFormat:=xlCSV

Should I put your code after or before this?

before - and change the line to

ActiveWorkbook.SaveAs FileName:="C:\Documents and Settings\rsernowski.R-SERNOWSKI\Desktop\" & a ,FileFormat:=xlCSV

Hi,

I am getting a runtime error 1004, on
ActiveWorkbook.SaveAs FileName:="C:\Documents and Settings\rsernowski.R-SERNOWSKI\Desktop\" & a ,FileFormat:=xlCSV
on this line
make sure the folder  exists,

Oops - this was my fault. The Full Name should just be Name

Dim a As String
 
a = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) 'removes the .xls
a = Replace(a, "mac", "") ' finds and removes the mac
a = a & ".csv" ' adds the csv

ActiveWorkbook.SaveAs FileName:="C:\Documents and Settings\rsernowski.R-SERNOWSKI\Desktop\" & a ,FileFormat:=xlCSV

Hi Lorenda
works almost to a tee, except the file is named deal20091006001.xls.csv. It is saved as CSV format but has the XLS extension
Thanks for your help

Can you post your full final code here? Are you missing this line?

a = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) 'removes the .xls

Hi,

The code is there on that line
here is is:
'
' Mac_Trades_Correct Macro
'
    Dim FileName As String
    Dim wb1 As Workbook
    Dim Row As Long
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Today As Date
    Dim ie As Object
    Dim ErrorCount As String
    Dim currentFileName As String
    Dim newFileName As String
    Dim msgReply As Long
    Dim incrementNumber As Integer
    Dim a As String
   

 
currentFileName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
 
If Len(currentFileName) = 15 Then
    'Changes have not yet been saved
   
    newFileName = currentFileName & "001"
   
    Select Case MsgBox("The file will be saved as " & newFileName & ".xls", _
    vbOKCancel, "Save As Filename")
       
    Case vbOK
        'Save the file
        Application.EnableEvents = False
'        ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" _
'        & newFileName & ".xlsm"
        ActiveWorkbook.SaveAs FileName:="\\Share_folder\Common\tickets\" _
        & newFileName & ".xls", FileFormat:=xlExcel8
        Application.EnableEvents = True
    Case vbCancel
        'Do nothing (exit the procedure).
        Exit Sub
    End Select
ElseIf Len(currentFileName) = 18 Then
    'Changes have already been saved at least once.
   
    incrementNumber = Right(currentFileName, 1)
 
    If incrementNumber = 1 Or incrementNumber = 2 Then
        newFileName = Left(currentFileName, Len(currentFileName) - 1) _
        & (incrementNumber + 1)
 
        Select Case MsgBox("The file will be saved as " & newFileName _
        & ".xls", vbOKCancel, "Save As Filename")
        '& ".xlsm", vbOKCancel, "Save As Filename")
 
        Case vbOK
            Application.EnableEvents = False
            ActiveWorkbook.SaveAs FileName:="\\Share_folder\Common\tickets\" _
            & newFileName & ".xls", FileFormat:=xlExcel8
            Application.EnableEvents = True
        Case vbCancel
            'Do nothing (exit the procedure).
            Exit Sub
        End Select
    Else
        MsgBox "You cannot save any new revisions to this file.", vbOKOnly
    End If
End If
 
'Cancel the native Save As dialog box.
If SaveAsUI = True Then Cancel = True
   
   
'Print the work Sheet
Set wb1 = ActiveWorkbook
ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
  End With
      ExecuteExcel4Macro "PRINT(1,,,1,,FALSE,,,,,,2,,,TRUE,,FALSE)"
 
'send the trade ticket to Mackenzie'
If MsgBox("Click OK to EMail, Cancel to STOP.", vbOKCancel) = vbCancel Then Exit Sub
Set OutApp = CreateObject("Outlook.Application")
   OutApp.Session.Logon
   Set OutMail = OutApp.CreateItem(0)
   With wb1
        On Error Resume Next
        With OutMail
        '  .to = ""
          ' .CC = ""
        .BCC = "rsernowski@gmail.com"
        .Subject = " CANCEL AND CORRECT"
                     .Body = " CANCEL AND CORRECT. File name prior to this CANCEL and CORRECT  " & currentFileName
         .Attachments.Add wb1.FullName
            'wb1.FullName
            'You can add other files also like this
            '.Attachments.Add ("C:\test.txt")
            .send   'or use .Display
        End With
        On Error GoTo 0
        End With
        'ActiveWorkbook.Close SaveChanges:=False
        With Application
        .ScreenUpdating = True
        .EnableEvents = True
        End With
        MsgBox "The file was successfully sent "
        'End With
 
'Reformat the file to Mellon Format
Set wb1 = ActiveWorkbook
With wb1
'Add columns and format columns
    Columns("F:F").Hidden = False
    Columns("J:J").Hidden = False
    Columns("R:R").Hidden = False
    Columns("S:S").Hidden = False
    Columns(12).insert
    Columns(14).insert
    Columns(15).insert
    Columns(18).insert
    Columns(22).insert
    Columns(23).insert
    Columns(24).insert
    Columns(25).insert
    Columns(28).insert
    Columns(29).insert
    Columns(30).insert
    Columns(31).insert
    Columns(32).insert
    Columns(33).insert
    Columns(34).insert
    Columns(35).insert
    Columns("AB:AI").ColumnWidth = 1
    Columns("AR:AY").ColumnWidth = 1
    Columns("AL:AO").ColumnWidth = 1
    Columns("V:Y").ColumnWidth = 1
    Columns("R:R").ColumnWidth = 1
    Columns("N:O").ColumnWidth = 1
    Columns("L:L").ColumnWidth = 1
    Columns("C:C").EntireColumn.AutoFit
    Columns("U:U").EntireColumn.AutoFit
    Columns("D:E").NumberFormat = "mm/dd/yyyy"
    Columns("D:E").EntireColumn.AutoFit
    Columns("I:I").NumberFormat = "0.00000"
    Columns("H:H").NumberFormat = "0"
    Columns("I:I").EntireColumn.AutoFit
    Columns("M:M").NumberFormat = "0.00"
    Columns("O:O").NumberFormat = "0.00"
    Columns("Q:Q").NumberFormat = "0.00"
    Columns("S:S").NumberFormat = "0.00"
    Columns("T:T").EntireColumn.AutoFit
    Columns("AA:AA").NumberFormat = "General"
    Columns("L:S").EntireColumn.AutoFit
'First Delete the Header from Toogood
Rows("1:2").Delete
   End With
With ActiveSheet
   
    a = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.FullName) - 4) 'removes the .xls
    a = Replace(a, "Mac", "") ' finds and removes the mac
    a = a & ".csv" ' adds the csv
    ActiveWorkbook.SaveAs FileName:="C:\Documents and Settings\rsernowski.R-SERNOWSKI\Desktop\" & a, FileFormat:=xlCSV
     'ActiveWorkbook.SaveAs FileName:="\\Share_folder\Common\Trade_tickets\Mackenzie\Blue" & format(Date, "yyyymmdd") & ".csv", FileFormat:=xlCSV
 ' wb1.Close SaveChanges:=False
'Open the webpage
 ' Set ie = CreateObject("Internetexplorer.Application")
  'ie.Visible = True
  'ie.Navigate "https://workbench.com/fileupload.do"
End With
End Sub

   
   

You need to change this line


    a = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.FullName) - 4) 'removes the .xls
 

to

a = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) 'removes the .xls

Hi Lorenda

Very nice ,  Thank you. I will accept as solution but perhaps you can help me with the bottom part of the code
Open the webpage
 ' Set ie = CreateObject("Internetexplorer.Application")
  'ie.Visible = True
  'ie.Navigate "https://workbench.com/fileupload.do"
End With
End Sub
I have posted this question but as of yet no reply.  when the conversion to csv is done, a secure website opens up.  I put in user ID and password, and the site opens to a page where the user can browse to the desktop and locate the csv file we just saved. Works, but is it possible to save the file into memory, then when the webpage opens , the user focuses on the browse box  and presses CTRL+V to paste in the path?

Hmm, not sure, I've never tried that. . .
If you were doing that, you would also have to "Pause" your code while you put your login credentials in

or

Have the VBA macro log you in

Hi Lorenda

Ok, well there is a revolving password so the macro can't log the user in. If I can have the code copy the path,, then when the user goes to the website she just presses paste. what do you thinkl

Hi Lorenda
Good idea, but I think browse is the best at this point.
Thanks for all your help
Regards
RS