Export Excel Spread Sheet as a text file using Access VBA

[nic6000]

Hi,

I wonder if any one can help me? I am trying to save an Excel Worksheet as a tab delimited txt file using Access VBA. I need to get the file into text format so I can then Import it into Access (The data is pretty naf so need the functionality of an Importspec).

I have created something that’s does most of the job, but it mucks up the date fields for some reason i.e. the date 15/01/2007 (dd/mm/yyyy), but when I save it, the format changes to 1/15/2007. This causes Import error problems!

However, when I go into Excel and save the file as txt delimited manually, the dates stay in the same format.

I have attached the code I'm using below. Am I way of the mark in terms of how I should be doing this? Any advice will be appreciated.


If strType = "IMPORT_TXT" Then
Set objXL = New Excel.Application
Set objWkb = objXL.Workbooks.Open(fs.Foundfiles(i))
objWkb.SaveAs StrFileSave, xlUnicodeText 'tried all types of properties, none seem to do the trick?
objWkb.Close False

Thanks in advance

Nic

 

[combo]

In SaveAs... command add parameter Local:=True.

combo

 

[nic6000]

Hi,

I'm returning error 'named argument not found' using 'local: =true' , I presume I'm not adding it correctly, as quite new access VBA. Any extra info would be apreciated.

Thanks in advance.

Nic

 

[combo]

Assume that you use excel 2k (9) or lower reference. I found 'Local' in xp and it was working. Just checked in 2000 and nope, no such argument.
You are using early binding, so F1 key will open excel vba help.
It could be instead any of xlFileFormat constants, you could try the optional 'TextCodePage' argument.

combo

 

[nic6000]

Hi,

Thanks for looking Combo. I will be moving over to XP in a few months so your info on the Local Parameter should be very handy.

Have tried just about everything that the saveas method has to offer in 2000 but to no avail - I wonder if it is a registry issue?

 

[combo]

This workaround works for me in case of simple excel file. Requires references to MSForms and Scripting. The code works from inside excel, should not be hard to translate to excel automation (Application = excel application):

Dim FSO As Scripting.FileSystemObject
Dim TS As Scripting.TextStream
Dim DObject As MSForms.DataObject
Set DObject = New MSForms.DataObject
ThisWorkbook.Worksheets(1).UsedRange.Copy
DObject.GetFromClipboard
Application.CutCopyMode = False
Set FSO = New Scripting.FileSystemObject
Set TS = FSO.CreateTextFile("c:\test.txt", True)
TS.Write DObject.GetText
TS.Close

combo

 

[bunglemutt]

Combo -

Thanks for the code snippet - happened to be almost the perfect solution to a current challenge! Was wondering though if it could be modified to process only the selected area of the worksheet in question as opposed to the "UsedRange"?

-bunglemutt

 

[bunglemutt]

...figured it out!

Dim FSO As Scripting.FileSystemObject
Dim TS As Scripting.TextStream
Dim DObject As MSForms.DataObject
Dim SelectedRange As Range
'
Set DObject = New MSForms.DataObject
'
ActiveWindow.RangeSelection.Copy
DObject.GetFromClipboard
Application.CutCopyMode = False
Set FSO = New Scripting.FileSystemObject
Set TS = FSO.CreateTextFile("c:\test.txt", True)
TS.Write DObject.GetText
TS.Close

Thanks!

-bunglemutt