CODEWITHSUNDEEP
vbaexcel
Dan Donoghue
Dan Donoghue

Reputation: 6216

Is it possible to associate an Excel Macro with a file extension?

I have built a macro for building and running SQL Queries. I am pretty happy with it so far. The only thing I would like to add is the ability in Windows to double click a .sql file and it opens inside the macro. Sample is below:

SQL Query Tool

This is the code that opens an SQL file when Load Query is pressed.

Sub LoadQuery()
    Dim fNameAndPath As Variant
    fNameAndPath = Application.GetOpenFilename(FileFilter:="SQL Query Files (*.sql), *.sql", Title:="Select File To Be Opened")
    If fNameAndPath = False Then Exit Sub
    Open fNameAndPath For Input As #1
    Sheets("Sheet1").SQL_Query = Input$(LOF(1), 1)
    Close #1
    Sheets("Sheet1").SQLFileName.Caption = fNameAndPath
End Sub

Is this even possible? I don't think it will be but thought I would check with you guys first.

What have I tried so far? Nothing, because I don't even know where to start, Uncle Google didn't hook a brother up in fact he just confused me even more.

Using the ideas below I went with this:

Option Explicit
Dim xlApp, xlBook, fNameAndPath

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("G:\Analytics Reporting Archive\SQL Client.xlsm", 0, True)
xlApp.Open WScript.Arguments(0) For Input As #1
xlBook.Sheets("Sheet1").SQL_Query = Input$(LOF(1), 1)
xlApp.Close #1
xlBook.Sheets("Sheet1").SQLFileName.Caption = fNameAndPath
Set xlBook = Nothing
Set xlApp = Nothing
WScript.Quit

This didn't work, it didn't like opening the text file (sql file) so I went with creating a small routine in the Excel app:

Sub LoadQueryDBLClick(QueryFileName As String)
    Open QueryFileName For Input As #1
    Sheets("Sheet1").SQL_Query = Input$(LOF(1), 1)
    Close #1
    Sheets("Sheet1").SQLFileName.Caption = fNameAndPath
End Sub

This is then called in the VBS like so:

Option Explicit
Dim xlApp, xlBook, fNameAndPath

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("G:\Analytics Reporting Archive\SQL Client.xlsm", 0, True)
xlApp.Run "LoadQueryDBLClick " & WScript.Arguments(0)
Set xlBook = Nothing
Set xlApp = Nothing
WScript.Quit

Brilliant right?

NO :(. It "would" work, of that I am confident (and extremely grateful to you guys that posted replies for me that got me this far) but alas Citrix strikes again:

Error: ActiveX component can't create object: 'Excel.Application'

I am confident that you guys have solved this in that Citrix is now the issue.

Upvotes: 2

Views: 137

Answers (2)

Tim Williams
Tim Williams

Reputation: 166980

See here for how to create a custom extension and associate it with an action.

https://superuser.com/questions/406985/programatically-associate-file-extensions-with-application-on-windows

What might work in your case is to associate the extension with a vbs file which takes the launching SQL filepath as a parameter and opens your Excel file using automation, then loads the SQL file into the workbook.

Upvotes: 1

Lance
Lance

Reputation: 3932

You'll want to right-click on an SQL file and select Open With. Choose, or browse to Excel.exe. Set it as the default application to open SQL files with.

This will open your SQL files in Excel.

From here you'll need to teach your macro to detect when it was launched by an SQL file and run as desired.

Upvotes: 1

Related Questions