Why is the template up loadable after reopening, and resaving, and not before?

[VectorW2]

Hello,

I have a macro that takes data downloaded and paste special from one system, codes, reorganizes, formats, and saves it as a ".csv" with a unique name depending on a cell value. The template does all this so this data can be uploaded into a new system. It seems to work fine except each file fails the upload into the new system unless you open it, save it, close it and reupload. then it works. the only difference I see is having to reopen and save. How can I fix this? We are talking about tens of thousands of files and those two additional steps will be a nightmare. The Mini sheet below does not include the conversion or instructions that would be very hidden. These sheets are not part of the upload once the save function has been selected and the workbook is renamed as a .csv file. Thank you in advance. Code below as I could not upload the mini sheet I created for this. Other pertinent information, using MicroSoft 360.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$P$2" Then
Select Case Target.Value
Case "Clear":
Range("A:P").ClearContents
Range("A1").FormulaR1C1 = "Paste Special Here"
Range("A1").Interior.Color = RGB(223, 202, 228)
Range("J1").FormulaR1C1 = "L / M #"
Range("L1").FormulaR1C1 = "Name"
Range("L4").FormulaR1C1 = "Your File's Name:"
Range("L5").FormulaR1C1 = "=IF(R[-4]C[-11]=""Paste Special Here"","""",IF(R[-3]C[-2]="""",R[-3]C[-11]&"" - ""&R[-3]C&"" - ""&R[-3]C[2],R[-3]C[-2]&"" - ""&R[-3]C&"" - ""&R[-3]C[2]))"
Range("N1").FormulaR1C1 = "Reviewer #"
Range("P1").FormulaR1C1 = "Functions"
Range("P2").Interior.Color = RGB(241, 232, 238)
Sheets("Instructions").Visible = xlVeryHidden
Case "Instructions":
Sheets("Instructions").Visible = True
Sheets("Instructions").Select
Case "Save":
Range("A:H").NumberFormat = "General"
Range("A1").Interior.Color = xlNone
Range("A:A").Insert Shift:=xlToRight
Range("C:C").Cut Destination:=Columns("A:A")
Range("E:E").Cut Destination:=Columns("C:C")
Range("D:D").Cut Destination:=Columns("E:E")
Range("F:F").Cut Destination:=Columns("D:D")
Range("I:I").Cut Destination:=Columns("F:F")
Range("G2:G" & Cells(Rows.Count, "H").End(xlUp).Row).FormulaR1C1 = "=IF(OR(RC[1]="""",RC[1]=0),"""",VLOOKUP(RC[1],Converter!C[-5]:C[-3],2,FALSE))"
Range("I2:I" & Cells(Rows.Count, "H").End(xlUp).Row).FormulaR1C1 = "=IF(OR(RC[-1]="""",RC[-1]=0),"""",VLOOKUP(RC[-1],Converter!C[-7]:C[-5],3,False))"
Range("A1").FormulaR1C1 = "TimePeriod"
Range("B1").FormulaR1C1 = "AccountId"
Range("C1").FormulaR1C1 = "Type"
Range("D1").FormulaR1C1 = "Acquisition"
Range("E1").FormulaR1C1 = "Accelerated"
Range("F1").FormulaR1C1 = "Cost"
Range("G1").FormulaR1C1 = "Table"
Range("I1").FormulaR1C1 = "Life"
Range("A:I").Copy
Range("A:I").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("I:I").Cut Destination:=Columns("H:H")
Range("I:I").Delete
ActiveSheet.Name = Range("L5")
Range("J:P").Delete
Range("A2").Select
ActiveWorkbook.SaveAs Filename:= _
"P:\Confidential\Confidential\" & Sheet.Name & ".csv", _
FileFormat:=xlCSVUTF8, CreateBackup:=False
Range("I:P").Delete
Range("A1").Select
ActiveWorkbook.Save

End Select
End If

End Sub

 

[rlv01]

FileFormat:=xlCSVUTF8, CreateBackup:=False

You are using a specific type of CSV file format and my guess would be that the system you are trying to upload to does not like it. When you save the file again you are changing it to the default CSV format and the system seems to like that better.

Experiment with different csv file types until you get one that works.

FileFormat:=xlCSV
FileFormat:=xlCSVWindows
FileFormat:=xlCSVMSDOS

 

[VectorW2]

FileFormat:=xlCSVUTF8, CreateBackup:=False

You are using a specific type of CSV file format and my guess would be that the system you are trying to upload to does not like it. When you save the file again you are changing it to the default CSV format and the system seems to like that better.

Experiment with different csv file types until you get one that works.

FileFormat:=xlCSV
FileFormat:=xlCSVWindows
FileFormat:=xlCSVMSDOS

Ok, thank you. I will give it a try. I will let you know sometime Monday. Thanks again.

 

[VectorW2]

FileFormat:=xlCSVUTF8, CreateBackup:=False

You are using a specific type of CSV file format and my guess would be that the system you are trying to upload to does not like it. When you save the file again you are changing it to the default CSV format and the system seems to like that better.

Experiment with different csv file types until you get one that works.

FileFormat:=xlCSV
FileFormat:=xlCSVWindows
FileFormat:=xlCSVMSDOS

I tried all four csv extensions again. Only the CSVUTF8 works at all, and only after I reopen the workbook, save it, close, and upload it again. I really don’t understand why this is happening.

 

[rlv01]

I tried all four csv extensions again. Only the CSVUTF8 works at all, and only after I reopen the workbook, save it, close, and upload it again. I really don’t understand why this is happening.

What do you mean that "Only the CSVUTF8 works at all"? Do you mean that no file is saved when say, xlCSV is used in your code instead of xlCSVUTF8 ? That seems unlikely.

Your file is not uploading because something about the file is changing when you save it a 2nd time. You may need to do a binary comparison between the file saved by your macro (which won't upload), and the file saved when you do it again a second time (manually).

 

[VectorW2]

What do you mean that "Only the CSVUTF8 works at all"? Do you mean that no file is saved when say, xlCSV is used in your code instead of xlCSVUTF8 ? That seems unlikely.

Your file is not uploading because something about the file is changing when you save it a 2nd time. You may need to do a binary comparison between the file saved by your macro (which won't upload), and the file saved when you do it again a second time (manually).

The following formats don’t work even after second save: FileFormat:=xlCSV
FileFormat:=xlCSVWindows
FileFormat:=xlCSVMSDOS

The filefirmat:=xlcsvutf8 fails after macro is ran, but once the newly macro created file is opened and saved, it works.

I agree, but how do i do a binary comparison and if i do find something different how do i make the correction to the macro? I suppose that is putting the cart before the horse, but it is ultimately what I need to know.

Thank you for helping me try and figure this out.

 

[VectorW2]

The following formats don’t work even after second save: FileFormat:=xlCSV
FileFormat:=xlCSVWindows
FileFormat:=xlCSVMSDOS

The filefirmat:=xlcsvutf8 fails after macro is ran, but once the newly macro created file is opened and saved, it works.

I agree, but how do i do a binary comparison and if i do find something different how do i make the correction to the macro? I suppose that is putting the cart before the horse, but it is ultimately what I need to know.

Thank you for helping me try and figure this out.

*fileformat
Also, yes to your question. The new system does not work with the other three csv file types. The new system only works with the csvutf8 csv

 

[rlv01]

Excel has many ways to save a file with a lot of user-selectable options when performing a save. What are the exact steps you take when you save it the 2nd time?

The new system only works with the csvutf8 csv

The fact that you have to open the file with Excel and save it a second time undermines that assertion a bit. Likely the 2nd save is converting the format to something the system uploading the file to finds more acceptable. It could also be a character encoding issue. For example if your PC is set up to a regional setting different from the standard Windows 1252 character encoding. The macro save could be in the default Windows 1252 character encoding and then when you save it again, your PC's regional settings change the encoding to something your system likes better. Note that I am NOT saying that is what is happening to you. I'm just illustrating some of the other possibilities. Figuring out any of this all begins with determining what is different about your csv file after the 2nd save.

 

[VectorW2]

Excel has many ways to save a file with a lot of user-selectable options when performing a save. What are the exact steps you take when you save it the 2nd time?

The fact that you have to open the file with Excel and save it a second time undermines that assertion a bit. Likely the 2nd save is converting the format to something the system uploading the file to finds more acceptable. It could also be a character encoding issue. For example if your PC is set up to a regional setting different from the standard Windows 1252 character encoding. The macro save could be in the default Windows 1252 character encoding and then when you save it again, your PC's regional settings change the encoding to something your system likes better. Note that I am NOT saying that is what is happening to you. I'm just illustrating some of the other possibilities. Figuring out any of this all begins with determining what is different about your csv file after the 2nd save.

To get the csv to upload, the file is opened, the save button in the ribbon is clicked, then the file is closed and uploaded. I also noticed this same result when I took a failed csv file the macro had created, copied the csv file and paste it several times. The excel default names the first with “copy” at the end then “copy 1”, etcetera… Does that help at all? Also, how do i go about figuring out what changed between the macro’s csv save and the manual save?

 

[VectorW2]