We help IT Professionals succeed at work.

renaming a file extension

Frank Freese
Frank Freese asked
on
Folks,
I was given this procedure, which works well. Another opportunity has presented itself.
One of the files that I will be using is saved as as .cvs. There is no way the data center will change their routine. When that occurs I need to change the extension to be .xls

Private Sub cmdCollectData_Click()
On Error GoTo Err_CollectData_Click

Dim fileName As String
Dim result As Integer
With Application.FileDialog(msoFileDialogOpen)
    .Title = "Select File"
    .Filters.Add "Excel Files", "*.xls*"
    .InitialFileName = "C:\"
    result = .Show
    If (result <> 0) Then
        fileName = Trim(.SelectedItems(1))
    End If
End With
Comment
Watch Question

BRONZE EXPERT
Top Expert 2016

Commented:

.Filters.Add "Excel Files", "*.xls*"
.Filters.Add "Csv Files", "*.csv*"       'add this line
BRONZE EXPERT
Top Expert 2016

Commented:
.Filters.Add "Excel Files", "*.xls"
.Filters.Add "Csv Files", "*.csv"       'add this line


remove the * after .xls
BRONZE EXPERT
Top Expert 2016

Commented:
<When that occurs I need to change the extension to be .xls>

do you mean getting the .csv and saving it as .xls?

Author

Commented:
Inserted new code and deleted the * after .xls.  I got "Invalid procedure call or argument"
BRONZE EXPERT
Top Expert 2016

Commented:
if that is the case try this sub

Sub saveasXLS()
Dim xlObj As Object, xlFile As String, csvFile
csvFile = "C:\myCSV.csv"
xlFile = "C:\myExcel.xls"
Set xlObj = CreateObject("excel.Application")
    xlObj.workbooks.Open (csvFile)
    xlObj.activeworkbook.saveas xlFile, FileFormat:=46, CreateBackup:=False
    xlObj.activeworkbook.Saved = True
    xlObj.Quit
End Sub

Author

Commented:
Woop! Ignore last comment. My fault.
To answer you other question:

I need to change the file name from xxxxx.csv to xxxxx.xls
BRONZE EXPERT
Top Expert 2016

Commented:

check again the codes you added, should look like this

        .Filters.Add "Excel Files", "*.xls"
        .Filters.Add "Csv Files", "*.csv"

Author

Commented:
capricorn1:
Again my apologies. I made the changes correctly this time what you first suggested and Access return "Subscript out of range".
I have not tried the new sub you gave me.
BRONZE EXPERT
Top Expert 2016

Commented:
see my comment at http:#a20736972

Author

Commented:
Ok! Let me chew on that after I return from my meeting. Where should I call this from?

Author

Commented:
If you'd let me know where to call this procedure from I can close this out.
Thanks
BRONZE EXPERT
Top Expert 2016

Commented:
it depends, on how you want to do this, just keep in mind that
 the sub must be called prior to importing the .xls file
 

Author

Commented:
Since the user can select either file type (xls or csv) I would think that if they selected a .csv then it appears that I would need to check to see which .Filter.Add to evaluate. If it is a xls nothing changes from what we did orginally. Here's my thinking on the revised subCollectData_Click:

Private Sub cmdCollectData_Click()
On Error GoTo Err_CollectData_Click

Dim fileName As String
Dim result As Integer

With Application.FileDialog(msoFileDialogOpen)
    .Title = "Select File"
If Trim(.selected(Items(1))= ".xls" then
    .Filters.Add "Excel Files", "*.xls"
Else
    .Filters.Add "Csv Files", "*.csv"
    Call saveasXLS
End If    
.InitialFileName = "C:\"
    result = .Show
    If (result <> 0) Then
        fileName = Trim(.SelectedItems(1))
    End If
End With

   DoCmd.SetWarnings False
   Call AddColumnNames(fileName)
 
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblExcel", fileName, True, "Sheet1!K:Q"
   Call DeleteRow(fileName)
   DoCmd.OpenQuery "qryExceldateChange"
   DoCmd.SetWarnings True

Exit_CollectData_Click:
    Exit Sub

Err_CollectData_Click:
    If Err.Number = 1004 Then
        MsgBox "You did not select a file!", vbOKOnly, "No File Selected"
        Exit Sub

Author

Commented:
My logic is wrong. I'll work through this if just mentor me please.

In the cmdCoolectData sub:

With Application.FleDialog(msoFileDialogOpen)
....
....
...
If (result <>0 then
 filename=Trim(.SelectedItems(1))
(here is where I need to evaluate filename. If the last three characters are csv then I should run the
saveasXLS sub. If true, I need help doing that, please.)
End if

BRONZE EXPERT
Top Expert 2016

Commented:
that is one reason why i made you decide which way to go.. you are learning fast

'modify the sub

Sub saveasXLS(csvFile as string)
Dim xlObj As Object, xlFile As String
xlFile = Replace(csvFile,".csv",".xls")
Set xlObj = CreateObject("excel.Application")
    xlObj.workbooks.Open (csvFile)
    xlObj.activeworkbook.saveas xlFile, FileFormat:=46, CreateBackup:=False
    xlObj.activeworkbook.Saved = True
    xlObj.Quit
End Sub



this is the correct place to check if the extension is .csv

Private Sub cmdCollectData_Click()
On Error GoTo Err_CollectData_Click

Dim fileName As String
Dim result As Integer

With Application.FileDialog(msoFileDialogOpen)
    .Title = "Select File"
If Trim(.selected(Items(1))= ".xls" then
    .Filters.Add "Excel Files", "*.xls"
Else
    .Filters.Add "Csv Files", "*.csv"
    Call saveasXLS
End If    
.InitialFileName = "C:\"
    result = .Show
    If (result <> 0) Then
        fileName = Trim(.SelectedItems(1))
    End If
End With

"**** try this
if right(fileName,4)=".csv" then
     dim csvFile as string
     csvFile=fileName
     call saveasXLS(fileName)
     fileName=Replace(csvFile,".csv",".xls")
end if
'****

   DoCmd.SetWarnings False
   Call AddColumnNames(fileName)
 
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblExcel", fileName, True, "Sheet1!K:Q"
   Call DeleteRow(fileName)
   DoCmd.OpenQuery "qryExceldateChange"
   DoCmd.SetWarnings True

Author

Commented:
Question:

If Trim(.selected(Items(1))= ".xls" then
    .Filters.Add "Excel Files", "*.xls"
End If

Should the trim statement be Trim(.selectedItems(1)) with out the extra  (  before Items?
On the try this, if the first doesn't work then try this?

Thanks for the encouragement. I am really excited about learning and you are so kind to help.

Author

Commented:
In studying the code it does not make sense to me. Here's my thinking:

With Application.FileDialog(msoFileDialogOpen)
    .Title = "Select File"
    .Filters.Add "Excel Files", "*.xls"
    .Filters.Add "Csv Files", "*.csv"
    .InitialFileName = "C:\"
    result = .Show
    If (result <> 0) Then
        fileName = Trim(.SelectedItems(1))
    End If
    If Right(fileName, 4) = "csv" Then
       Dim csvFile As String
        csvFile = fileName
        fileName=Replace(csvFile,"csv",xls")
       Call saveasxLS(fileName)
End If

Here's why.
After I get the file I evaluate the last postions of the string. If it end in csv then I call saveasXLS(fileName). Else, I do nothing.
BRONZE EXPERT
Top Expert 2016

Commented:
sorry didn't notice that, you don't need those if else then.. in there

With Application.FileDialog(msoFileDialogOpen)
    .Title = "Select File"
    .Filters.Add "Excel Files", "*.xls"
    .Filters.Add "Csv Files", "*.csv"
   
    .InitialFileName = "C:\"
    result = .Show
    If (result <> 0) Then
        fileName = Trim(.SelectedItems(1))
    End If
End With

BRONZE EXPERT
Top Expert 2016

Commented:
this is not correct

    If Right(fileName, 4) = "csv" Then
       Dim csvFile As String
        csvFile = fileName
        fileName=Replace(csvFile,"csv",xls")
       Call saveasxLS(fileName)
End If


stick with this

if right(fileName,4)=".csv" then
     dim csvFile as string
     csvFile=fileName
     call saveasXLS(fileName)
     fileName=Replace(csvFile,".csv",".xls")
end if



Author

Commented:
Several things:
I changed:
If Right(fileName,3) = "csv"
from
If Right(fileName,4="csv".
I guess I could have change the second expression and add a (.) before csv.

I noticed that
1. The file that was csv to xls is named, for example, fileNamexls.xml, rather than fileName.xls And here's where it is happening:

Sub saveasXLS(csvFile As String)
x1Flie=Replace(csvFile,".csv","xls")
So I simply changed to .xls

In the sub AddColumnNames(sFileName As String)
where
objXL.workbooks.Open sFileName
returns an err.number 1004 that I interpret to be a file was not selected.
Up to this point everything is looking ok.
BRONZE EXPERT
Top Expert 2016

Commented:
can you check first the codes i posted without alteration.

Author

Commented:
I believe I can but I need a little help here. Which sub?
BRONZE EXPERT
Top Expert 2016

Commented:
this whole code

Sub saveasXLS(csvFile as string)
Dim xlObj As Object, xlFile As String
xlFile = Replace(csvFile,".csv",".xls")
Set xlObj = CreateObject("excel.Application")
    xlObj.workbooks.Open (csvFile)
    xlObj.activeworkbook.saveas xlFile, FileFormat:=46, CreateBackup:=False
    xlObj.activeworkbook.Saved = True
    xlObj.Quit
End Sub



Private Sub cmdCollectData_Click()
On Error GoTo Err_CollectData_Click

Dim fileName As String
Dim result As Integer
With Application.FileDialog(msoFileDialogOpen)
    .Title = "Select File"
    .Filters.Add "Excel Files", "*.xls"
    .Filters.Add "Csv Files", "*.csv"
   
    .InitialFileName = "C:\"
    result = .Show
    If (result <> 0) Then
        fileName = Trim(.SelectedItems(1))
    End If
End With

if right(fileName,4)=".csv" then
     dim csvFile as string
     csvFile=fileName
     call saveasXLS(fileName)
     fileName=Replace(csvFile,".csv",".xls")
end if


   DoCmd.SetWarnings False
   Call AddColumnNames(fileName)
 
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblExcel", fileName, True, "Sheet1!K:Q"
   Call DeleteRow(fileName)
   DoCmd.OpenQuery "qryExceldateChange"
   DoCmd.SetWarnings True

Exit_CollectData_Click:
    Exit Sub

Err_CollectData_Click:
    If Err.Number = 1004 Then
        MsgBox "You did not select a file!", vbOKOnly, "No File Selected"
        Exit Sub
    End if
End sub

Author

Commented:
Here's what I found:

Sub saveasXLS(csvFile as String)
Dim xlObj As Object, x1File As String
x1File=Replace(csvFile, ".csv", "xls") (here's what it returns: "D:\:label audit report.xls")
Set xlObj = CreateObject ("excel.Application")
      xlObj.workbooks.Open (csvFile) (This is where I get the error)

Therefore, rather than csvFile should I use x1File?
BRONZE EXPERT
Top Expert 2016
Commented:
ok use this

Sub saveasXLS(csvFile as string)
Dim xlObj As Object, xlFile As String
xlFile = Left(csvFile, Instr(csvFile,".csv")-1) & ".xls"
Set xlObj = CreateObject("excel.Application")
    xlObj.workbooks.Open (csvFile)
    xlObj.activeworkbook.saveas xlFile, FileFormat:=46, CreateBackup:=False
    xlObj.activeworkbook.Saved = True
    xlObj.Quit
End Sub

Author

Commented:
Capriciorn1:
They have changed the data again. I'm going to close this and reopen with a different set of problems, that are not as extensive. By the way, I did try that last sub you presented and it failed again. Just an FYI Look for new question shortly

Explore More ContentExplore courses, solutions, and other research materials related to this topic.