VBA - Save excel in a new format with the same name

[snirben]

Hi, <gs id="15679284-25bd-49f1-9ea9-1eea49fd8be5" ginger_software_uiphraseguid="d7f3613a-3f8d-4eca-b5dd-83cd884cb307" class="GINGER_SOFTWARE_mark">i</gs> have excel that <gs id="8dd26a21-5427-47e7-85d8-6814c1ae4230" ginger_software_uiphraseguid="d7f3613a-3f8d-4eca-b5dd-83cd884cb307" class="GINGER_SOFTWARE_mark">i</gs> store all of my macros.

<gs id="19952d46-aa72-41ae-81b6-7bf760da52b4" ginger_software_uiphraseguid="827b5346-8358-408c-91b2-3d6a912ca1fe" class="GINGER_SOFTWARE_mark">i</gs> have build a macro to save my excel file in the same folder and with the same name, but <gs id="a0156244-1887-4515-b675-010bfd3fe425" ginger_software_uiphraseguid="827b5346-8358-408c-91b2-3d6a912ca1fe" class="GINGER_SOFTWARE_mark">i</gs> need to change the code to save it in XLSX Format and close the <gs id="7d28cddf-57b3-4df8-9b24-3335ce1f59b8" ginger_software_uiphraseguid="827b5346-8358-408c-91b2-3d6a912ca1fe" class="GINGER_SOFTWARE_mark">xls</gs> file and open the new XLSX one.

And my code not working that well in saving xD so need your help.

  Dim <gs id="c49bc9df-9259-4387-8acf-85f1ac49c65e" ginger_software_uiphraseguid="cb90572d-a50a-418e-b30b-5ade04b1e6af" class="GINGER_SOFTWARE_mark">thisWb</gs> As Workbook, d As Integer    
    Set <gs id="d833f773-733f-4fe7-9d71-c3453a1f0dd8" ginger_software_uiphraseguid="8b3ebdb5-74ff-4c99-a7ad-69d7b9d40990" class="GINGER_SOFTWARE_mark">thisWb</gs> = ActiveWorkbook
    Workbooks<gs id="8a3e72a9-2ae4-487c-bc68-b74671fa9acd" ginger_software_uiphraseguid="1242d18d-f84b-449e-9621-a5549e0ba993" class="GINGER_SOFTWARE_mark">.</gs>Add
    <gs id="5d532443-4aa5-411c-8ead-498105808973" ginger_software_uiphraseguid="f35ee94e-527b-4123-92d3-5efda19e0da9" class="GINGER_SOFTWARE_mark">d</gs> = InStrRev<gs id="2203ed25-d714-4852-9d94-b6e96f835996" ginger_software_uiphraseguid="f35ee94e-527b-4123-92d3-5efda19e0da9" class="GINGER_SOFTWARE_mark">(</gs><gs id="c5266cae-a805-4521-82a8-6c4c2a4a9188" ginger_software_uiphraseguid="f35ee94e-527b-4123-92d3-5efda19e0da9" class="GINGER_SOFTWARE_mark">thisWb</gs><gs id="36d9fa5c-fbe8-4dc8-b352-ae492add2357" ginger_software_uiphraseguid="f35ee94e-527b-4123-92d3-5efda19e0da9" class="GINGER_SOFTWARE_mark">.</gs>FullName, ".")
    ActiveWorkbook.SaveAs FileName:=Left(thisWb.FullName, d - 1) & Format(Now, " dd_mm_yyyy hhmmss") & Mid(thisWb.FullName, d)
    
    <gs id="cdb61452-8294-4b56-9b43-7fab0a1ffcda" ginger_software_uiphraseguid="fb202e83-dfbc-4ea0-a6fe-1c5788a961f8" class="GINGER_SOFTWARE_mark">ActiveWorkbook</gs><gs id="36b00abe-764e-434c-82cd-6081209ccb54" ginger_software_uiphraseguid="fb202e83-dfbc-4ea0-a6fe-1c5788a961f8" class="GINGER_SOFTWARE_mark">.</gs>Close <gs id="25170763-d051-4e4f-b4f4-77a2602aa626" ginger_software_uiphraseguid="fb202e83-dfbc-4ea0-a6fe-1c5788a961f8" class="GINGER_SOFTWARE_mark">savechanges</gs><gs id="1ef29bc3-342a-4ea2-b24f-aa208eb83930" ginger_software_uiphraseguid="fb202e83-dfbc-4ea0-a6fe-1c5788a961f8" class="GINGER_SOFTWARE_mark">:</gs>=False

 

[Rijnsent]

Hi Snirben,
see this tutorial by Ron de Bruin, it covers the various extensions and saveas: Use VBA SaveAs in Excel 2007-2016
Cheers,
Koen

 

[snirben]

Hi Snirben,
see this tutorial by Ron de Bruin, it covers the various extensions and saveas: Use VBA SaveAs in Excel 2007-2016
Cheers,
Koen

sry didnt help, can you be more speciefc?

 

[Rijnsent]

Hi Snirben,
sure, could you post the new code you created?
Thanks,
Koen

 

[snirben]

</gs>Hi Snirben,
<gs id="265be471-2430-4c2e-9f5b-fe163df021e3" ginger_software_uiphraseguid="a89d55d7-5471-4f3a-904c-bec02e396e8b" class="GINGER_SOFTWARE_mark">sure</gs>, could you post the new code you created?
Thanks,
Koen<gs id="634e7738-72e2-477a-aa59-0c133c2f2ae1" ginger_software_uiphraseguid="733558b4-7dd4-47c7-835f-c1be718c0ae7" class="GINGER_SOFTWARE_mark">[</gs>/QUOTE]

I solved with a workaround, because I had bad time finding an answer

So <gs id="93bbea96-035c-4a1d-8432-275f665f1b57" ginger_software_uiphraseguid="a34943fa-8625-4f2d-a2cd-491e8d6ec9ad" class="GINGER_SOFTWARE_mark">i</gs> make a macro to type in A1 the name of the file <gs id="fd606dd5-0d48-4499-b15f-43daf7020310" ginger_software_uiphraseguid="a34943fa-8625-4f2d-a2cd-491e8d6ec9ad" class="GINGER_SOFTWARE_mark">i</gs> want
And when it finishes, the A1 is Deleted


And then:

[CODE<gs id="751d9ca1-6c7f-4e5a-8a0c-1e56c0e3e5a0" ginger_software_uiphraseguid="492c34bc-427e-4636-a5aa-c4bc4ef5b9fd" class="GINGER_SOFTWARE_mark">]</gs>Dim <gs id="0cf6f38c-15c9-4728-adf4-244e4ab66715" ginger_software_uiphraseguid="492c34bc-427e-4636-a5aa-c4bc4ef5b9fd" class="GINGER_SOFTWARE_mark">relativePath</gs> As String, <gs id="28be9564-7a59-4da3-99a5-2f2617c45496" ginger_software_uiphraseguid="492c34bc-427e-4636-a5aa-c4bc4ef5b9fd" class="GINGER_SOFTWARE_mark">sname</gs> As String <gs id="99e41efb-da82-41ec-8572-915514dfe42d" ginger_software_uiphraseguid="7c9096f2-c32e-4ae8-bf4f-18ca1b693ef0" class="GINGER_SOFTWARE_mark">sname</gs> = ActiveWorkbook<gs id="f912b89a-0651-48c6-925a-39c0cc73523b" ginger_software_uiphraseguid="7c9096f2-c32e-4ae8-bf4f-18ca1b693ef0" class="GINGER_SOFTWARE_mark">.</gs>Worksheets<gs id="43f0e583-af25-4887-a676-e990a87e4aff" ginger_software_uiphraseguid="7c9096f2-c32e-4ae8-bf4f-18ca1b693ef0" class="GINGER_SOFTWARE_mark">(</gs>1)<gs id="741d5048-e353-44a9-9dad-6f3ff04ccc2b" ginger_software_uiphraseguid="7c9096f2-c32e-4ae8-bf4f-18ca1b693ef0" class="GINGER_SOFTWARE_mark">.</gs>Range<gs id="3dbe65c7-3965-4c9c-a60b-036ea2a1bf9d" ginger_software_uiphraseguid="7c9096f2-c32e-4ae8-bf4f-18ca1b693ef0" class="GINGER_SOFTWARE_mark">(</gs>"A1") & "<gs id="b39320d2-09b0-44d7-a2a8-5677b0269dbf" ginger_software_uiphraseguid="7c9096f2-c32e-4ae8-bf4f-18ca1b693ef0" class="GINGER_SOFTWARE_mark">.</gs><gs id="3dc0344d-2d92-48ab-be70-01979e395387" ginger_software_uiphraseguid="7c9096f2-c32e-4ae8-bf4f-18ca1b693ef0" class="GINGER_SOFTWARE_mark">xlsx</gs>"
<gs id="74915b28-5fe2-4950-a456-d6bcf9fe06f7" ginger_software_uiphraseguid="8c4e91c3-6e22-4462-8146-dc58e372a527" class="GINGER_SOFTWARE_mark">relativePath</gs> = Application<gs id="2b465f9e-ab46-4e99-8673-2b7121cd9697" ginger_software_uiphraseguid="8c4e91c3-6e22-4462-8146-dc58e372a527" class="GINGER_SOFTWARE_mark">.</gs>ActiveWorkbook<gs id="5b98ba27-6b39-447d-a933-d397bb9a8cea" ginger_software_uiphraseguid="8c4e91c3-6e22-4462-8146-dc58e372a527" class="GINGER_SOFTWARE_mark">.</gs>Path & "" & <gs id="fcfa8d08-be86-4e80-a5dd-9d52c3d0512c" ginger_software_uiphraseguid="8c4e91c3-6e22-4462-8146-dc58e372a527" class="GINGER_SOFTWARE_mark">sname</gs>
Application<gs id="730c4b67-1b95-4775-a786-dc234c161019" ginger_software_uiphraseguid="96f35e69-0fd9-41a2-8869-cf966dcb17b7" class="GINGER_SOFTWARE_mark">.</gs>DisplayAlerts = False
<gs id="c917783d-1a42-4005-b159-019d200f22e1" ginger_software_uiphraseguid="0a5b0642-71aa-45e8-9c28-ea6dbdf2c7ec" class="GINGER_SOFTWARE_mark">ActiveWorkbook</gs><gs id="ebd5e9eb-e72b-4ee4-a8a9-d649ea503385" ginger_software_uiphraseguid="0a5b0642-71aa-45e8-9c28-ea6dbdf2c7ec" class="GINGER_SOFTWARE_mark">.</gs><gs id="95800562-3a11-4b04-832c-02442181ed8c" ginger_software_uiphraseguid="0a5b0642-71aa-45e8-9c28-ea6dbdf2c7ec" class="GINGER_SOFTWARE_mark">CheckCompatibility</gs> = False
<gs id="f784db48-ce1c-4974-a9d3-2e624542d880" ginger_software_uiphraseguid="f98eb933-691c-44cf-8595-08447dfb1199" class="GINGER_SOFTWARE_mark">ActiveWorkbook</gs><gs id="a71b58c0-d6f1-46bb-8e06-7dbc7b9421ec" ginger_software_uiphraseguid="f98eb933-691c-44cf-8595-08447dfb1199" class="GINGER_SOFTWARE_mark">.</gs>SaveAs Filename<gs id="d1e03621-3416-4aa1-8d23-96c1e17a7fd2" ginger_software_uiphraseguid="f98eb933-691c-44cf-8595-08447dfb1199" class="GINGER_SOFTWARE_mark">:</gs>=relativePath, <gs id="ae73b51b-ecb4-4e5e-b9fb-26ba67de33cb" ginger_software_uiphraseguid="f98eb933-691c-44cf-8595-08447dfb1199" class="GINGER_SOFTWARE_mark">FileFormat</gs><gs id="5f1d0c5c-ee0a-414b-94e3-e38cbe75d81b" ginger_software_uiphraseguid="f98eb933-691c-44cf-8595-08447dfb1199" class="GINGER_SOFTWARE_mark">:</gs>=51

Application<gs id="97704fc6-ed0c-4b3e-8dc3-3c668485ef36" ginger_software_uiphraseguid="1e3be853-447e-4dfe-9f05-4491e778c080" class="GINGER_SOFTWARE_mark">.</gs>DisplayAlerts = True<gs id="1ba1b21d-65f0-459d-9a60-471fa2d0c6bb" ginger_software_uiphraseguid="1e3be853-447e-4dfe-9f05-4491e778c080" class="GINGER_SOFTWARE_mark">[</gs>/CODE]