Access VBA to open Excel workbook, save as a dbf (I think)

[Barb Reinhardt]

I'm working on a procedure to run through Excel and need to open an Excel
workbook in access and save it as a dbf (I think). I don't use access at
all, so am not familiar with any of the terminology.

I want to do the following
Dim myAccApp As Access.Application
Dim myAccFile As Variant 'Not sure how to set this

Set myAccApp = CreateObject(, "Access.Application")

'Open Excel file. Want to have it assigned to an access object
'I can figure out the filepath.

Set myAccFile = myAccApp.OpenAccessProject(filepath)
myaccfile.saveas ... Not sure what to do here

myAccApp.Quit
Set myAccApp = Nothing

Thanks for your assistance.

Barb Reinhardt

 

[Jack Leach]

I've never actually done this myself, but based on various posts I've seen I
think the syntax would somewhat resemble this:

Dim o as Object
Set o = CreateObject("Excel.Application")
With o
'do your automation stuff here
End With
'Close the object?? not sure how...

something like that anyway... maybe it will give you a start.


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Klatuu]

If what you want to do is import data from Excel into Access, use the
TransferSpreadsheet method. If you are using Macro's, it would be the
TransferSpreadsheet Action.

The only reason to open an instance of Excel is if you want to manipulate
the Excel spreadsheet using VBA. Be aware, this takes a lot of VBA because
the Excel Object Model is extensive.

 

[Krzysztof Naworyta]

Barb Reinhardt wrote:
| I'm working on a procedure to run through Excel and need to open an
| Excel workbook in access and save it as a dbf (I think). I don't use
| access at all, so am not familiar with any of the terminology.
|
| I want to do the following
| Dim myAccApp As Access.Application
| Dim myAccFile As Variant 'Not sure how to set this
|
| Set myAccApp = CreateObject(, "Access.Application")
|
| 'Open Excel file. Want to have it assigned to an access object
| 'I can figure out the filepath.
|
| Set myAccFile = myAccApp.OpenAccessProject(filepath)
| myaccfile.saveas ... Not sure what to do here
|
| myAccApp.Quit
| Set myAccApp = Nothing


Dim myAccApp As Access.Application
Dim sql as string

Set myAccApp = CreateObject("C:\folder1\db.mdb")

sql = "SELECT * INTO [dBase
III;HDR=YES;IMEX=1;DATABASE=C:\folder1\].[dbf1#dbf]" & _
" From [Excel 8.0;HDR=YES;IMEX=2;DATABASE=C:\folder1\my.xls].[Sheed1$]"

myAccApp.CurrentDb.execute sql

 

[Barb Reinhardt]

I'm quite familiar with the Excel object model. It's Access that I am
unfamiliar with.

I have two things I want to do

1) Open an excel workbook into access
2) Save the active sheet as a dbf

How do I do that in VBA in Access. I can get it to work in Excel once I
have the code.

 

[Douglas J. Steele]

It's not clear to me why you think you need to do this in Access...

Within Access, instantiate an instance of Excel. Open the workbook in that
instantiation, use Excel's SaveAs method to save the active sheet as a dbf,
then clean up after yourself (i.e. close the workbook, quit Excel, set the
instantiation variable to Nothing)