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

VectorW2

New Member
Joined
Sep 11, 2013
Messages
34
Office Version
  1. 365
  2. 2010
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,810
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
To get the csv to upload, the file is opened, the save button in the ribbon is clicked, then the file is closed
Some random thoughts.

1. Why don't you use the macro recorder to record a macro of yourself performing the above action? Then inspect the code to see what it is doing differently than your macro.
2. If instead of "Save", you instead do a "Save-As", will the file still upload?
3. If you google "binary file comparison tool" or "free binary file comparison tools" you should get a lot of hits.
 

VectorW2

New Member
Joined
Sep 11, 2013
Messages
34
Office Version
  1. 365
  2. 2010
I just noticed something. The error message in the failed upload says “Duplicate column headers”. I do not see a duplicate column or row. Also once reopened, of course there is only one header row and no duplicate columns. I will see if the alternate ways to save changes anything. If not I will have to research the binary data as you also suggest. Thank you.
 

Forum statistics

Threads
1,176,265
Messages
5,902,239
Members
434,953
Latest member
matthiasarnbert

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
Top