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
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.