starting another program

[Guest]

Hi
How can i with a command buttom stating
another program not excel.

Hope someone can help

alvin

 

[Jari Toukkari]

Alvin

Use Shell. Help should be available in vba help.

Cheers, Jari

 

[Jari Toukkari]

If someone knows how to open a file in another program, I would be grateful.
For example open a certain xxxx.txt file in the Notepad.
Regards, Jari

 

[Guest]

Hi jari
its the same i want to
I have try call shell but that only working with a exe fil
if i have maby a mdb(access) file
name c:\grub.mdb i want to open this file with access

if i try that with call shell i get a error

alvin

 

[Tom Ogilvy]

Sub ABC()
Shell "C:\Windows\Notepad.exe 'C:\Myfolder\Myfile.txt"
End Sub

worked for me.

 

[Guest]

Thanks
its working
Maybe you can help more?
If the file name is in a txt file
how can i get this filename?

Best ragards
Alvin

 

[Tom Ogilvy]

You can open a text file with low level io and extract the file name:

http://www.applecore99.com/gen/gen029.asp
File I/O Using VBA
Applecore pages on Microsoft Access

http://support.microsoft.com/default.aspx?scid=kb;en-us;151262&Product=xlw
Working with Sequential Access Files


then construct a string which forms a proper argument to the shell command.

You can also manipulate Access using OLE Automation. You might look at the
Automation help file:
http://support.microsoft.com/?kbid=167223
OFF97: Microsoft Office 97 Automation Help File Available

 

[Gary Keramidas]

i found a few ways to import a text file and was wondering if this solution
was acceptable. it works for me, anyway.

this code brings in the whole text file

Do While FilesInPath <> ""
Open FileDir & FilesInPath For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
RowNdx = RowNdx + 1
Wend

i needed only line 25, so i adapted it to this:

Do While FilesInPath <> ""
Open FileDir & FilesInPath For Input Access Read As #1
x = 1
Do While x < 25
WholeLine = ""
Line Input #1, WholeLine
x = x + 1
Loop

While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
RowNdx = RowNdx + 1
Wend

is this the proper way?


thanks

 

[Guest]

Thanks tom I have try this
Private Sub Command1_Click()
On Error GoTo E_Handle
Dim strImport As String
Dim lngChars As Long
Dim intFile As Integer
intFile = FreeFile
Open "C:\alm.txt" For Input As intFile
lngChars = LOF(intFile)
strImport = Input(lngChars, intFile)
Shell ("c:\windows\notepad.exe " & strImport)


sExit:
On Error Resume Next
Reset
Exit Sub
E_Handle:
MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
Resume sExit




End Sub

If i use a msgbox i can see the file is ok
but in Shell command i get a error what do i wrong here

regards

alvin

 

[Tom Ogilvy]

Try adding single quotes

Shell ("c:\windows\notepad.exe '" & strImport & "'")

If you aren't using Windows 9x or Millenium, then Windows should probably be
Winnt

 

[Guest]

Hi tom
I must do something else wrong
se here
its working if i use StrImport ="c:\gemme.txt"
but it dosn't work if i use
strImport = Trim(Input(lngChars, intFile))

i don't understand why in
"C:\alm.txt" i have one line with c:\gemme.txt

regards alvin


On Error GoTo E_Handle
Dim strImport As String
Dim lngChars As Long
Dim intFile As Integer
intFile = FreeFile
Open "C:\alm.txt" For Input As intFile
lngChars = LOF(intFile)
'strImport = Trim(Input(lngChars, intFile))
strImport = "c:\gemme.txt"
Shell ("c:\windows\notepad.exe '" & strImport & "'")





MsgBox strImport


sExit:
On Error Resume Next
Reset
Exit Sub
E_Handle:
MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
Resume sExit




End Sub

 

[Tom Ogilvy]

Looks good to me. Why don't you use it to answer Sifar's question about
transposing a text file.

 

[Tom Ogilvy]

This worked fine for me:

Sub mno()
On Error GoTo E_Handle
Dim strImport As String
Dim lngChars As Long
Dim intFile As Integer
intFile = FreeFile
Open "C:\alm.txt" For Input As intFile
lngChars = LOF(intFile)
strImport = Trim(Input(lngChars, intFile))
Shell ("c:\windows\notepad.exe '" & strImport & "'")
E_Handle:
Close #intFile

End Sub

 

[Tom Ogilvy]

Just another thought. After you get lngChars, look at it with msgbox

msgbox lngChars

if it is longer than 12, then that is your problem. If you created the test
file in Notepad, you should type in the 12 characters and then do file save.
No Return at the end of the 12 characters.

 

[Guest]

yes
thanks
if i use c:\gemme.txt it dosn't work
but if i use c:\gem.txt its working

thanks.
alvin

 

[Gary Keramidas]

ok, thanks.

i posted a reply to sifar, not sure about the structure of his file after
the 1 example, though.

 

[Gary Keramidas]

this is the other method i used to achieve the same result. is one
preferable to the other?

With ActiveSheet.QueryTables.Add(Connection:= _
"Text;" & "" & FileDir & FilesInPath & "", Destination:=Range("A" &
lastrow))
'.Name = "test"
.FieldNames = False
' .RowNumbers = False
' .FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
' .SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 25
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
ActiveCell.Offset(1, 0).Select
NumberOfFiles = NumberOfFiles + 1
lastrow = lastrow + 1