Opening a .dat file in Excel, then saving as .xls, all From Access

A

Annoyed Accountant

I've seen several threads hint at this, but I can't seem to get any of them
to work. I am downloading data from another source and importing it into
Access. The problem is, I can only save the original data as a .dat file.
This .dat file will open in Excel just fine, but I was hoping there was a way
to bypass manually opening excel and convert the file to .xls format for
import into Access using transferspreadsheet.

The below code (from another post) will open an excel file and save it just
fine, but how can I tweak this to convert my .dat to .xls so all of my
transferspreadsheet commands will work. (I have a huge number of these
commands, so an easy fix would be great) Thank you.

Private Sub start_Click()
Dim appExcel As Object
Dim workBook As Object
Dim workSheet As Object
Dim s_period As String

Set appExcel = GetObject("C:\H064.xls")

appExcel.Application.Visible = True
appExcel.Parent.Windows(1).Visible = True

appExcel.Application.DisplayAlerts = False
appExcel.Application.Save
appExcel.Application.DisplayAlerts = True
appExcel.Application.Quit

Set workSheet = Nothing
Set workBook = Nothing
Set appExcel = Nothing
 
J

John Nurick

There's no standard file format associated with the .dat extension (or
to put it another way, the fact that a filename ends .dat says nothing
about how the contents are structured), so it's not easy to help.

If your .dat files are regular text files, consider cutting Excel out
and importing them directly into Access (using TransferText in place of
TransferSpreadsheet).

(If you've tried this and get Run-time error '3027' and/or "Cannot
update. Database or object is read-only", see
http://support.microsoft.com/?id=304206 and
http://support.microsoft.com/?id=306144 .
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top