Need help with vba

[mike.molina]

Gurus,

I have an Access Database which imports data from an excel workbook located on C:\Drive. My problem is the excel workbook is not enabled so when the import occurs no data is imported into Access. If I manually enable the content on the excel workbook the import will work fine. Is there a VBA I can write that will locate the file, enable the content then import the data? below is what the VBA currently looks like:

 '===============IMPORT SUBINVENTORY DATA=====================
Private Sub Command36_Click()
Dim filepath As String
filepath = "C:\UUAM\Subinv.xls"
If FileExist3(filepath) Then
DoCmd.TransferSpreadsheet acImport, , "Subinv", filepath, True
Else
MsgBox "File not found. Please check file name, file extension or file location."
End If
End Sub
Function FileExist3(sTestFile As String) As Boolean
Dim lsize As Long
On Error Resume Next
lsize = -1
lsize = FileLen(sTestFile)
If lsize > -1 Then
FileExist3 = True
Else
FileExist3 = False
End If
MsgBox ("Sub Inventory Data upload successfully")
End Function

PLEASE HELP

R/s,
Michael

 

[Eljefegeneo]

Is your Excel file password protected? That may be why you have to have it open to work.

 

[mike.molina]

No, the excel file is downloaded to the C:\Drive with no password restrictions. If I manually open the excel file and select enable content, then save and close file. I can then go to the database and select an import button which would then import the data without error...but I want to automate the enabling of the excel file. However, if I do not enable the content within the excel file and attempt to select the import button in Access, I do not receive an error, it just does not bring in the data.

 

[sneuberg]

I don't know about Excel file but if you move an Access file to a trusted location it becomes enabled without having to enable the content when you open it. So I suggest set up a trusted location (in Excel: File, Options, Trust Center, Trust Center Settings, Trusted Locations) and then move the file to this location in your code. I believe this site http://www.rondebruin.nl/win/s3/win026.htm will give you the code you need to move the file.