Open an excel file from Access

[Bruce]

I am trying to open an excel file from Access but the code below stops at line
Workbooks.Open Filename:=impdir & strFile

I have checked that impdir & strFile is building the correct string.

Access is telling me object reuqired. What have I overlooked?


Sub test()
Dim strFile As String, impdir As String

impdir = CurrentProject.Path & "\Import\"
strFile = (Dir(impdir & "*.xls"))
If Len(strFile) > 0 Then
Do While Len(strFile) > 0

Workbooks.Open Filename:=impdir & strFile
Call AddApostrophes
' Range("A1").Select
ActiveWorkbook.Save
ActiveWindow.Close

strFile = Dir
Loop
Else
End
End If
End Sub

Bruce

 

[Paul Overway]

You need to instantiate an Excel object before you can open a workbook in
it, i.e,

Set obj = CreateObject("Excel.Application")
obj.Workbooks.Open Filename:=impdir & strFile

 

[Bruce]

Thanks Paul,

Thats got me a bit further but I'm still having some issues....Let me
explain what I am really trying to do.

I have some excel data to import in which one of the fields has both numeric
and aplphanumeric data. The destination field is of type text but I am still
having soem import error (as some numeric values are being converted to
scientific notation.

My answer is to add a ' to the data field in Excel prior to data import to
ensure all values are treated as text. My macro AddApostrophes works if I
drop this in the excel file, and after that I can import successfully into
Access.

However in this situation it is more convenient if I can get this to work
from Access but the following problems exist.

1) The code halts on the AddApostrophes macro 'user-defined type not defined'.

2) Even though the code has run through these lines;

obj.Workbooks.Open Filename:=impdir & strFile
obj.Workbooks(strFile).Activate

I cannot set an open copy of excel or file workbook on my screen. If I go to
explorer and open the file it says its in use and access is read only. Is
this part of my problem?

See full code below
Bruce



Sub test()
Dim strFile As String, impdir As String

impdir = CurrentProject.Path & "\Import\"
strFile = (Dir(impdir & "*.xls"))
Set obj = CreateObject("Excel.Application")

If Len(strFile) > 0 Then
Do While Len(strFile) > 0

obj.Workbooks.Open Filename:=impdir & strFile
obj.Workbooks(strFile).Activate
Call AddApostrophes
' Range("A1").Select
ActiveWorkbook.Save
ActiveWindow.Close

strFile = Dir
Loop
Else
End
End If
End Sub

Sub AddApostrophes()

Dim C As Excel.Range
Application.ScreenUpdating = False
myRange = "C2:C" & endrow
With ActiveWorkbook.ActiveSheet
For Each C In Range(myRange)
C.Formula = "'" & C.Formula
Next
End With
Application.ScreenUpdating = True
End Sub

Function endrow()
endrow = (Range("A1").End(xlDown).Row)
End Function

 

[Paul Overway]

If you actually want to see Excel, you need to make it visible, i.e.,
obj.Visible =True. You also need to save and quit when you're done working
with the file.