• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 671
  • Last Modified:

renaming a file extension

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
0
Frank Freese
Asked:
Frank Freese
  • 13
  • 13
1 Solution
 
Rey Obrero (Capricorn1)Commented:

.Filters.Add "Excel Files", "*.xls*"
.Filters.Add "Csv Files", "*.csv*"       'add this line
0
 
Rey Obrero (Capricorn1)Commented:
.Filters.Add "Excel Files", "*.xls"
.Filters.Add "Csv Files", "*.csv"       'add this line


remove the * after .xls
0
 
Rey Obrero (Capricorn1)Commented:
<When that occurs I need to change the extension to be .xls>

do you mean getting the .csv and saving it as .xls?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Frank FreeseAuthor Commented:
Inserted new code and deleted the * after .xls.  I got "Invalid procedure call or argument"
0
 
Rey Obrero (Capricorn1)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
0
 
Frank FreeseAuthor 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
0
 
Rey Obrero (Capricorn1)Commented:

check again the codes you added, should look like this

        .Filters.Add "Excel Files", "*.xls"
        .Filters.Add "Csv Files", "*.csv"
0
 
Frank FreeseAuthor 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.
0
 
Rey Obrero (Capricorn1)Commented:
see my comment at http:#a20736972
0
 
Frank FreeseAuthor Commented:
Ok! Let me chew on that after I return from my meeting. Where should I call this from?
0
 
Frank FreeseAuthor Commented:
If you'd let me know where to call this procedure from I can close this out.
Thanks
0
 
Rey Obrero (Capricorn1)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
 
0
 
Frank FreeseAuthor 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
0
 
Frank FreeseAuthor 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

0
 
Rey Obrero (Capricorn1)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
0
 
Frank FreeseAuthor 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.
0
 
Frank FreeseAuthor 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.
0
 
Rey Obrero (Capricorn1)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

0
 
Rey Obrero (Capricorn1)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



0
 
Frank FreeseAuthor 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.
0
 
Rey Obrero (Capricorn1)Commented:
can you check first the codes i posted without alteration.
0
 
Frank FreeseAuthor Commented:
I believe I can but I need a little help here. Which sub?
0
 
Rey Obrero (Capricorn1)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
0
 
Frank FreeseAuthor 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?
0
 
Rey Obrero (Capricorn1)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
0
 
Frank FreeseAuthor 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
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 13
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now