VBA Code to Save ExcellSheet as file format

[NiceMan331]

hi
i have AN access database ,which in one part of it will analyse a big amount of data with excell sheet format
i am using this code to import the sheet to my database

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Calls", "d:\My Documents\myfile.xls", True, "details!b1:h1068"

when trying to perform this code , it gives an error message that object do'snt in corect format , then i go to that workbook , save it as excell 97-2000 , then performing the code again , it success .
the problem is the big number of workbooks , it is so difficult to open it one by one , save as , repaet it every time ,
i tried this code to perform automatic open and save as , but it fail

Sub OpenSpecific_xlFile()
    Dim oXL As Object
    Dim oExcel As Object
    Dim sFullPath As String
    Dim sPath As String
    
        
    Set oXL = CreateObject("Excel.Application")
    
    
    On Error Resume Next
    oXL.UserControl = True
    On Error GoTo 0
    On Error GoTo ErrHandle
 
     sFullPath = CurrentProject.Path & "\myfile.xls"
     With oXL
        .Visible = True
        .workbooks.Open (sFullPath)
        .workbooks.SaveAs FileName:=(sFullPath), FileFormat:=56
    
        
        
    End With
    
    
ErrExit:
    Set oXL = Nothing
    Exit Sub
    
ErrHandle:
    oXL.Visible = False
    MsgBox Err.Description
    GoTo ErrExit
End Sub

but it break at save as point , it said this proberty not allowed here
any idea ?

 

[JANR]

Try this code which I use.

Public Sub ConvertXL(ByVal Location As String, ByVal Target As String)
On Error GoTo Err_ConvertXL
 
Dim oxl As Object
Set oxl = CreateObject("Excel.Application")
oxl.Visible = False
oxl.UserControl = False
    
    With oxl
        .Workbooks.Open FileName:="" & Location & ""
        .ActiveWorkbook.SaveAs FileName:="" & Target & "", FileFormat:= _
         56, CreateBackup:=False
        .DisplayAlerts = False
        .ActiveWindow.Close
        .DisplayAlerts = True
        .Quit
    End With
    Set oxl = Nothing
    
    On Error GoTo 0
    Exit Sub
Exit_ConvertXL:
Set oxl = Nothing
Exit Sub
 
Err_ConvertXL:
     MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ConvertXL of Module basGetFiles"""
     Resume Exit_ConvertXL
End Sub

To call it:

ConvertXL "c:\Somefolder\somefile.xls" , "c:\Somefolder\somefile.xls"

JR

 

[NiceMan331]

thanx janr
it works
but how to avoide alert of confirm replacing the exciting excell file
i note that alertdesplay=false , but it still appearing
you know with a big number of workbooks , it is necessarry to avoiding like this alert

 

[JANR]

Have you tried to set the Target to a different folder than Location? It could be that a file that writes over itself would behave like this as in give you a warning regardless.

Also it is

.DisplayAlerts = false

not

.alertdisplay = false

JR

 

[NiceMan331]

yes janr , you are right
i changed the target to another folder , it works well now
another thing , is there a way to programmataly adding filename as additional filed in access table to label the data
cause now too much data added to the table , but i wish to know the source file for each one