Copy Cell to Text/Query File

[LadyDi]

Is there a way to program a spreadsheet to copy a cell to a Text file and then save it with the extension .qry?

I have a formula set up to create the text for a query. I currently have to take that cell, copy it to NotePad, then save it with the extension.qry. After that, I have programming set up to run the query I saved and automatically export the results to another spreadsheet. I would like to eliminate the manual steps if I can because I want to post this spreadsheet on a network drive for several of my co-workers to use.

 

[RayH]

Sub WriteQueryCelltoFile()
Dim Filelocation As String
Dim Queryname As String

Dim ws As Worksheet

Dim rownum As Long
Dim colnum As Long

Filelocation = "C:\queryfolder\"
Queryname = "CellQuery.qry"

rownum = 1
colnum = 1

Set ws = Worksheets("Sheet1")
With ws
    Open (Filelocation & Queryname) For Output As #1
        Print #1, ws.Cells(rownum, colnum);
    Close #1
End With
End Sub

You can change the rownum and colnum to suit the cell you want to use. Also, FileLocation and Queryname too.

 

[LadyDi]

That works perfectly. Thank you so much. I really appreciate it.

 

[LadyDi]

I have one more quick question. Is there a way to view this file after it has been created? My co-workers would like to have the query open on their screens after it is created. I tried commenting out the Close #1 line, but I still don't see the query when I do that. It says its open, but I can't see it.

 

[Rx_]

Would something like this help?
Sub OpenInNotepad()
' Rx Prescribed way to open a text file in notepad
Dim MyTxtFile
MyTxtFile = Shell("C:\WINDOWS\notepad.exe C:\Test\TESTING.txt", 1)
End Sub