Solved

Open a Text File Using Open Method (Excel.Application) With A Space & Tab Delimiter

Posted on 2004-08-04
3
3,044 Views
Last Modified: 2013-12-25
Dear Expert,
          I need to open a text file in excel using the open method.
I need to know how to use the delimiter within the open method.
The delimiter required is a space and tab.

Can you please help.....Urgently.

This is what I have so far.

Set appExcel = CreateObject("excel.application")
Set objWorkbook = appExcel.Workbooks.Open(FileName, delimiter)
                                                         \
                                                          What do I do here?
Kind Regards
Claude B
                                                         
0
Comment
Question by:BushyC
  • 2
3 Comments
 
LVL 11

Expert Comment

by:Steiner
Comment Utility
The delimiter can only be 1 character, not a sequence. If you provide more, only the first character will be used (at least in Excel97) To provide a user defined delimiter, use:

Application.Workbooks.Open filename:="e:\test\text.txt",format:=6,delimiter:="x"
0
 

Author Comment

by:BushyC
Comment Utility
Steiner, thanks for the info above.
I did some research, and apparently, I should be using OpenText Method.
Still coming up with errors.

'Expected Function Or Variable'

Set appExcel = CreateObject("excel.application")

Set objWorkbook = appExcel.Workbooks.OpenText(FileName, ConsecutiveDelimiter:=False, Tab:=True, Space:=True)

Set wSheet = objWorkbook.Worksheets(1)

Cheers
Claude B
0
 
LVL 11

Accepted Solution

by:
Steiner earned 500 total points
Comment Utility
I regularly don't use OpenText, but I'd think that this one would mean "Tab OR Space". Or is that what you wanted?

Another point is that OpenText is a method, so it has no return value, while Open is a function which returns a workbook object (don't ask me why this is so ;-)) But as a workaround, use this:

Application.Workbooks.OpenText FileName, ConsecutiveDelimiter:=False, Tab:=True, Space:=True
Set objWorkbook = ActiveWorkbook

Daniel
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now