Hello and thank you in advance for any advice:
I need to use Excel VBA to save an .xlsx file as a .txt file, then convert that .txt file's extension to .csv for a sweep job that can only detect .csv files. I'm attempting to change the extension using the Name statement but am getting "Run-time error '75': Path/File access error" with the code below. Can anyone tell me why, please? I get the same error whether or not I have file extensions displayed in Windows Explorer.
Dim Ext_M As String
Dim Ext_X As String
Dim Ext_T As String
Dim Ext_C As String
Dim Upload_File_Folder As String
Dim Upload_File_Name As String
Dim Upload_File_Path_M As String
Dim Upload_File_Path_X As String
Dim Upload_File_Path_T As String
Dim Upload_File_Path_C As String
Dim Upload_File_wb_M As Workbook
Dim Upload_File_wb_X As Workbook
Dim Upload_File_ws As Worksheet
Ext_M = ".xlsm"
Ext_X = ".xlsx"
Ext_T = ".txt"
Ext_C = ".csv"
Upload_File_Folder = 'using a Public Const here and can confirm that it is not missing a backslash
Upload_File_Name = "File_Name_" & Format(Now(), "yyyymmdd")
Upload_File_Path_M = Upload_File_Folder & Upload_File_Name & Ext_M
Upload_File_Path_X = Upload_File_Folder & Upload_File_Name & Ext_X
Upload_File_Path_T = Upload_File_Folder & Upload_File_Name & Ext_T
Upload_File_Path_C = Upload_File_Folder & Upload_File_Name & Ext_C
ThisWorkbook.SaveCopyAs (Upload_File_Path_M)
Set Upload_File_wb_M = Workbooks.Open(Upload_File_Path_M)
Upload_File_wb_M.SaveAs Filename:=Upload_File_Path_X, FileFormat:=xlOpenXMLWorkbook
Set Upload_File_wb_X = Workbooks(Upload_File_Name & Ext_X)
With Upload_File_wb_X
With Worksheets("PurchaseData")
Range("PurchaseData_Table[#All]").Copy
Range("PurchaseData_Table[#All]").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
.ListObjects(1).Unlist
End With
For Each Upload_File_ws In .Worksheets
If Upload_File_ws.Name <> "PurchaseData" Then
Upload_File_ws.Delete
Else
Upload_File_ws.Cells.EntireColumn.AutoFit
End If
Next Upload_File_ws
Sheets("PurchaseData").Name = "Sheet1"
.Save
.SaveAs Filename:=Upload_File_Path_T, FileFormat:=xlTextWindows
End With
Kill (Upload_File_Path_M)
Name Upload_File_Path_T As Upload_File_Path_C 'This is the line throwing the error.
You can't rename a file that's open in Excel.
Sheets("PurchaseData").Name = "Sheet1"
.Save
.SaveAs Filename:=Upload_File_Path_T, FileFormat:=xlTextWindows
.Close False '<<<<<
End With
Also in this With
block you need to bind Range
to the worksheet object
With Worksheets("PurchaseData")
.Range("PurchaseData_Table[#All]").Copy
.Range("PurchaseData_Table[#All]").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
.ListObjects(1).Unlist
End With
Might be a bit tidier as:
With Worksheets("PurchaseData").ListObjects(1)
.Range.Value = .Range.Value
.Unlist
End With