MS Excel VBA macro activated through right-click+send

AlfredoQuintero

Lifetime Supporting Member
A
Join Date
Feb 2015
Location
Yokohama
Posts
1,544
I have not been able to crack this one out, even after much thinking and googlingo_O. I would be so grateful if I can get some guidance from the forum.

I have developed a Windows application that stores tab-separated text files with data from some PLC applications. The end user has MS Office installed on the same PC, and at this moment the customer has to open MS Excel, locate the text file, open it up, parse the data and convert it into MS Excel, doing so manually each and every time.

In order to make it even easier for the customer, increase their level of satisfaction and increase the probability of new jobs, without having yet committed to the customer, I would like to develop an MS Excel VBA macro that would work as I describe below (which would automate the task described in the above paragraph):

1) From the standard Windows file explorer, right-click the text file and in the "send-to" options I would have registered a pointer to the MS Excel file with the macro that I am trying to develop.

2) Once the file is sent to the pointer, the macro is opened by MS Excel, the macro then opens the text file, works out the full path of the text file and remembers the full path.

3) The macro then parses the tab-separated fields into separate cells, as in a normal MS Excel spreadsheets.

4) Using the remembered full path but changing the extension from TXT or CSV into an Excel workbook, the file would be saved in MS Excel.

Is this actually possible?

Thanks for reading down to this point!o_O
 
No, I don't think it'll work that way.

You can create the macro in Excel, but they would still have to choose the file, unless it is the same file every time. If it is the same text file name every time, you could hard code that location into the macro.

They could then:
1) Open Excel File
2) Run macro

Here is the Macro I recorded:
Code: 
Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PATH\FILE.txt", Destination:=Range("$A$1"))
        .Name = "one"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.SaveAs Filename:="C:\PATH\FILE.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
End Sub
 
Thank you very much, ranckhornjp. You have confirmed my suspicion that the detection of the file name and path, (which will be always different) has to be resolved by other means and Excel cannot do this. I will try to find a solution and if I do will post it here, as I think it would be helpful to other subscribers.
 
hi what about a working folder, where you open/ parse/delete all files in turn until all files are done
 
AlfredoQuintero said:
Thank you very much, ranckhornjp. You have confirmed my suspicion that the detection of the file name and path, (which will be always different) has to be resolved by other means and Excel cannot do this. I will try to find a solution and if I do will post it here, as I think it would be helpful to other subscribers.
Click to expand...

What determines the file name?
 
Can you adjust your windows application to shoot out the data in a more usable format?

Alternatively you can use a powershell script to do what you want but you still need a way to specify the file name. Or you can use task scheduler and schedule a powershell script to run every night or on start up to parse the documents and save them somewhere else.

Still sounds better if the data comes out properly in the first place.
 
mp91 said:
Can you adjust your windows application to shoot out the data in a more usable format?

Alternatively you can use a powershell script to do what you want but you still need a way to specify the file name. Or you can use task scheduler and schedule a powershell script to run every night or on start up to parse the documents and save them somewhere else.

Still sounds better if the data comes out properly in the first place.
Click to expand...


Hello. The application is producing the data in the correctly desired tab-separated format. The file name of the resulting excel application is the same file name as the TXT file, but with the Excel extension.
 
AlfredoQuintero said:
Hello. The application is producing the data in the correctly desired tab-separated format. The file name of the resulting excel application is the same file name as the TXT file, but with the Excel extension.
Click to expand...

If it is in the correctly desired format then why do you need to change the file type?

I am saying that it would be better to export from your application already as a .xlsx file extension.
 
mp91 said:
If it is in the correctly desired format then why do you need to change the file type?

I am saying that it would be better to export from your application already as a .xlsx file extension.
Click to expand...


Hello mp91:
Sorry for my incomplete, unclear explanation.
By "desired" I mean the original request from the customer.

The reason I cannot directly store the data in .xlsx file format is that the application I "developed" was actually configuring a utility that only can save the data in CSV or TAB separated format. By development I mean the configuration of the address space for the PLC and some logic for triggering some data capturing. But I have no way to store directly the data into .xlsx, as this particular utility does not support such format.

Anyways, thanks very much for your advice. I have some other ideas I will be working in the future, and if I succeed I will post the solution.
 
Here ya go. Let me know if you have any questions.

2 parts.

1 - "GetLatestFile") gets the latest file in a specific folder (by date)

2 - "GetData") pulls the tab separated data from that file and inserts it in the excel spreadsheet
 

Similar Topics

A
XLM (Excel Macro) vs. VBA for data logging application
Hello. For a PLC data logging application at a candy factory, a customer requested me to setup an add-in for MS Excel called "MX Sheet" which is...
2
Replies
22
Views
7,959
AlfredoQuintero
A
JesperMP
MS Excel VBA experts ?
Hi. I have a task of creating checklists for a plant that contain many standard items. My idea is to do this in Excel, whereby in one sheet I...
Replies
11
Views
3,307
JesperMP
JesperMP
S
Writing to PLC Tags on OPC Server Using Excel VBA
Hello, I am looking for help writing to PLC tags from Excel. I am using Factory Talk Gateway as an OPC server and Excel VBA to write out to an...
Replies
5
Views
3,078
widelto
widelto
L
Excel vba to control logix data dump
Good evening, I'll start by explaining what i want to do and then what I've tried. We have an excel document at work that contains our material...
Replies
4
Views
2,277
ldrewes
L
J
Excel VBA Error 429 on Office 2010, Runs on Office 2016
Folks, I have an issue with my Excel VBA. When I run the upload script from my machine (Win10 Office 2016) it runs fine. But when I run it from a...
Replies
6
Views
2,846
Dravik
D
Back
Top Bottom