Problems linking CSV file

[Ruzz2k]

Hi I have opening a csv file from access changing the date to a number and then linking it to access and then doing a lookup.

Problem is the lookup only works if i open the csv file and go to saveas and click yes on the message attached and then when i close the csv it makes me do this once more.

If i do the above it will work but if i dont, the lookup wont work. any ideas.
of course i dont want the user to have to do this to the files.
first part of my code is as follows:

Can anyone help me I dont get why this happens

Private Sub LoadRef2Cal_Click()
On Error GoTo Err_LoadRef2Cal_Click
Dim s As String
Dim i As Long
Dim ExcelWorkbook As Excel.Workbook
s = LaunchCD(Me)
 
    Dim XLApp As Object
    Set XLApp = CreateObject("Excel.Application")
    XLApp.Visible = False
    Set ExcelWorkbook = XLApp.Workbooks.Open(s)
 
    With ExcelWorkbook
For i = 1 To ExcelWorkbook.Sheets.Count
ExcelWorkbook.Sheets(i).Name = "Sheet" & i & ""
ExcelWorkbook.Sheets(i).Columns("A:A").NumberFormat = "0.000000000000000"
Next
End With
 
ExcelWorkbook.SaveAs Left(s, InStrRev(s, ".") - 1) & ".xls", FileFormat:=xlNormal
    ExcelWorkbook.Close
    Set ExcelWorkbook = Nothing
    XLApp.Quit
    Set XLApp = Nothing
DoCmd.TransferSpreadsheet acLink, , "Sheet1", Left(s, InStrRev(s, ".") - 1) & ".xls", True, "Sheet1!A14:E65000"
DoCmd.OpenForm ("frmList1")
Forms![frmList1]![Text0] = Left(s, InStrRev(s, ".") - 1) & ".xls"
Forms![frmList1]![Text00] = "Ref 2 Readings CAL"
Exit_LoadRef2Cal_Click:
    Exit Sub
Err_LoadRef2Cal_Click:
    MsgBox Err.Description
    Resume Exit_LoadRef2Cal_Click
End Sub

 

[Cronk]

I have encountered problems linking to a csv file where I needed to process the data in say a daily file incorporating the date in the file name.

I can't recall the specific problem, but I got around it by setting up a permanent link to a csv file, and when I wanted to process the next day's csv file, I would delete the link file and then change the name of the file to be processed to that file name.