Jump to content
Macro Express Forums

reading excel file


Recommended Posts

The native Excel file is not a delimited format and you can’t do that.

 

Generally people have lists of things and they put it in excel just because it’s a handy way you have a grid. If this is the case I suggest you save the file to CSV instead. This way you can open and edit it on Excel and use ASCII File Process in MEP. However there’s no formatting, no multiple worksheets, and you can’t have it open at the same time.

 

Other alternatives is to have the user copy the range of cells to your clipboard and parse that with a split command in MEP. When you copy like that all clipboard data is inherently a tab separated value format. IE like CSV but using tabs instead.

 

But if you need to access the data in the Excel file format without changing it then you will need to use some VBScript and the External Script command to get to it. I have one I use often that will simply export one worksheet to a temp TSV file that I can deal with. Let me know if you need help, I’m available.

Link to comment
Share on other sites

The native Excel file is not a delimited format and you can’t do that.

 

Generally people have lists of things and they put it in excel just because it’s a handy way you have a grid. If this is the case I suggest you save the file to CSV instead. This way you can open and edit it on Excel and use ASCII File Process in MEP. However there’s no formatting, no multiple worksheets, and you can’t have it open at the same time.

 

Other alternatives is to have the user copy the range of cells to your clipboard and parse that with a split command in MEP. When you copy like that all clipboard data is inherently a tab separated value format. IE like CSV but using tabs instead.

 

But if you need to access the data in the Excel file format without changing it then you will need to use some VBScript and the External Script command to get to it. I have one I use often that will simply export one worksheet to a temp TSV file that I can deal with. Let me know if you need help, I’m available.

 

thanks cory, Now I will try for that and complete my work. :-))

Link to comment
Share on other sites

  • 2 weeks later...

Excel files are binary files (i.e. not plain text files), so you can't use AsciiFileBeginProcess on them. You might want to check out XLS2CSV:

 

www.brothersoft.com/xls2csv-69870.html

 

Does exactly as its name says. Small, fast and free. You don't even need to have Excel installed. Once data is in CSV format, it is a lot easier to read with MEP. It works from the command line, so you can call it from within macros with the Program Launch command.

 

I've was using XLS2CSV for a project where I needed to extract data from dozens of xls files every day.

Publisher's description

Microsoft Excel (XLS) file to Comma Seperated Values (CSV) file conversion program. This product helps you to convert your Microsoft Excel files to the standard Readable CSV file format which can be useful for your software or web site.

Link to comment
Share on other sites

Generally people have lists of things and they put it in excel just because it’s a handy way you have a grid.

[...]

 

Yea, I've seen this quite often. Usually, they're just storing data with no calculations or formulas. Sometimes there aren't even numbers. On their PCs, they almost always have Word too, so I would suggest to them to use Word's tables and its table wizard instead. Data entry is a lot easier in a Word table, and the tables are very customisable.

Link to comment
Share on other sites

I don't agree:) If they use Excel and just change the file format to CSV they can edit and manipulate in Excel and yet naively access it with MEP. Also one can link to such CSV files in Excel. This way you can have a linked source data file in one worksheet and simply reference it in others' of your workbook to do all kinds of formulas and such.

 

However the VBScript to export a worksheet in Excel to a CSV file is dead simple. Create object, set worksheet, export.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...