By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,731 Members | 1,104 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,731 IT Pros & Developers. It's quick & easy.

Macro to automatically open a file with a specific extension.

P: 34
Hello, I have a fairly simple macro where I can guide the user to the correct directory and file path, however I would like the file with a specific extension (in this example it's an Output file, or ".dat") to automatically open, and then save as a text in Notebook.

The macro thus far is:

ChDir "T:\"
Workbooks.Open Filename:=([OutputFile])
Pattern = "*.txt*"

Where ([OutputFile]) is a dynamic link that will get the user to the correct folder, and there's only one Output file.

Problem is, the data keeps opening in an Excel workbook, and the data goes well beyond 65K rows (and no we haven't converted to Excel '07 yet).

Any suggestions to open the file in Notepad?

Thank you!

Matt
Apr 23 '08 #1
Share this Question
Share on Google+
1 Reply


kadghar
Expert 100+
P: 1,295
Hello, I have a fairly simple macro where I can guide the user to the correct directory and file path, however I would like the file with a specific extension (in this example it's an Output file, or ".dat") to automatically open, and then save as a text in Notebook.

The macro thus far is:

ChDir "T:\"
Workbooks.Open Filename:=([OutputFile])
Pattern = "*.txt*"

Where ([OutputFile]) is a dynamic link that will get the user to the correct folder, and there's only one Output file.

Problem is, the data keeps opening in an Excel workbook, and the data goes well beyond 65K rows (and no we haven't converted to Excel '07 yet).

Any suggestions to open the file in Notepad?

Thank you!

Matt
Since Notepad doesnt have ActiveX, you'll have to open it using Shell, like

Expand|Select|Wrap|Line Numbers
  1. Dim A
  2. A = Shell("C:\WINDOWS\NOTEPAD.EXE", 1)
Or declare ShellExecute, to open it with the default application:

Expand|Select|Wrap|Line Numbers
  1. Private Declare Function ShellExecute Lib "shell32.dll" Alias _
  2.     "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
  3.     ByVal lpFile As String, ByVal lpParameters As String, _
  4.     ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
To use it:

Expand|Select|Wrap|Line Numbers
  1. dim res as long 
  2. res = ShellExecute(Me.hwnd, "Open", "c:\document.txt","", "", 1)
HTH
Apr 23 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.