Free Microsoft Excel 2013 Quick Reference

VBA - opening files with Shell command

I'm writing a macro in Excel which finds other files (not Excel files)
with a given name and then opens them. I'm using the Windows registry
to find the programs associated with the files and then using the Shell
command to open the files with the given program. My problem is that
for some programs the Shell command opens a new instance of the program
for each additional file, i.e. each AutoCAD file opens into its own
instance of AutoCAD even if AutoCAD is already running.

So my question is, is there a switch for the Shell command that tells
it to check to see if an instance of a program is already running
before starting a new one, and, if the program is already running,
opens the file with the existing instance? Or is there another way of
doing this without getting into the API of each program in question?

Here's a snippet of my code:

strCmd = appWord.System.PrivateProfileString("", _
"HKEY_CLASSES_ROOT" & regType & "shellOpencommand", _
"")

If Len(strCmd) > 0 Then
strCmd = Replace(strCmd, "%1", fileWithPath) 'for pdf & dwg files
strCmd = Replace(strCmd, "/dde", "/one " & """" & fileWithPath &
"""") 'for solidworks files
Shell strCmd, vbNormalFocus
Else
MsgBox prompt:="Could not find an application" & vbCr & _
"registered to display file.", _
Buttons:=vbCritical + vbOKOnly, _
Title:="Not Registered"
End If

Thanks!
Eric


Post your answer or comment

comments powered by Disqus
hi all - I have a switchboard and I'm trying to open a file when my user clicks on a button. Problem is that there are spaces in the file name (I can't change this - it's just how it is). So i was wondering how to do this ... Right now I have

	VB:
	
openDW = Shell("MSAccess.exe F:JQUtilitiesJust QuarterlyDatabase Application Program.mde", 1) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and this obviously throws errors because of the spaces.

any ideas on how to do this correctly?

thanks in advance

Hello

I am trying to open a binary file in excel using a hex editor called frhed.exe which is in my C drive. I am selecting the file to be open ( with .i2C extension)using getopenfile command and using the returned string in the shell command as shwon below, It is giving error. The i2c files are in G drive of the computer.With the name of the file to be opend placed in the shell command as follws works fine Shell("c:hexfrhed.exe " c:hextest.i2c, vbNormalFocus)

	VB:
	
 gh() 
    Dim retvalue, datafiles As String 
    ActiveSheet.Cells.ClearContents 
    datafiles = Application.GetOpenFilename(FileFilter:="I2CFiles,*.I2C", FilterIndex:=1, Title:="Select Files",
MultiSelect:=False) 
    retvalue = Shell("c:hexfrhed.exe " & datafiles, vbNormalFocus) ' open a txt document
    AppActivate retvalue 
    SendKeys "%fe{ENTER}", True 
    SendKeys "%{F4}", True 
    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("A1") 
    Range("A1").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ 
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _ 
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _ 
    :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ 
    Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _ 
    ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _ 
    (20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), _ 
    Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1)) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a report I generate daily. From another application I see if Excel is running. If Excel is not I start Excel with the report file in the command line and the report is generated. If Excel is open I have the ability to call a macro from the other application (but not send a file open command). I am trying to use a macro in personal.xls to open my report file. It does open the file but then hangs at the “Workbooks(File_Name).Activate” line. Another thing I noticed is the VBA code up to that line did not happen. I am at a loss here so any help would be greatly appreciated. Here is the code I am using.

Personal.xls
Dim AlarmLoc As Variant
Dim AlarmFile As Variant
Dim AlarmOpenPath As Variant

Sub ExcelOpenAlarm()
AlarmLoc = "C:C7_LineAlarms"
AlarmFile = "Alarms.xls"
AlarmOpenPath = AlarmLoc + AlarmFile
Application.Workbooks.Open (AlarmOpenPath)
Workbooks("Alarms.xls").Activate
End
End Sub

Alarms.xls
Dim File_Dir As String
Dim File_Name As String
Dim File_Dir_Name As String
Dim newHour, newMinute, newSecond, waittime
Dim wbActiveBook As Workbook
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Private Sub Workbook_Activate()
'Auto runs report on file open rename file to debug
If ActiveWorkbook.Name = "Alarms.xls" Then
Call MakeAlarmLog
End If
End Sub

Sub MakeAlarmLog()
Application.ScreenUpdating = False
'Make date portion of file name
Range("I7").Select
Selection.Copy
Range("I8").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
'Refresh DB query
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
'Retreive file name from spread sheet
File_Dir = "C:C7_LINEAlarms"
File_Name = ThisWorkbook.Sheets("Sheet1").Range("I8").Value
File_Dir_Name = File_Dir & File_Name
'Initial save file
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=File_Dir_Name, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
'Clear non alarm DB junk from spreadsheet
Workbooks(File_Name).Activate
Columns("G:J").Select
Selection.Delete
Range("a1").Select
'Remove query reference
For Each Qy In Sheet1.QueryTables: Qy.Delete: Next Qy
'Delete Macros from Report
Set wbActiveBook = ActiveWorkbook
Set VBComps = wbActiveBook.VBProject.VBComponents
For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
'Restore default settings save file and close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
ActiveWorkbook.Save
If Workbooks.Count = 2 Then
Application.Quit
Else
ActiveWorkbook.Close
End If
End Sub

Also as another item I would like to make sure there are no files named Alarms.xls already open so all these functions can happen without any user intervention.

Best regards,
MN Mike the chilly VBA novice

To open files with a macro, I am currently using:

Workbooks.Open Filename:= "C:file_path.xls"

How can I use a variable as part of the file path?

In other words, before I try to open the file in my VBA code I will set a
variable (let's call it date_extension). Then I want to open a file that I
know will exist with a file path whose final characters before the .xls will
be the date_extension. How can I get the file path to be recognized with the
variable?

Thanks for your help

(P.S. I am a total novice, so I may not be familiar with commands or
procedures that are fairly basic for you folks.)

Hi All, I have run into a problem using the shell command with VBA on a userform.

I need to open a explorer window and navigate to our document management software.

This does not work.  It will open the explorer window but only show the "my documents" folder.  

the problem is "documentumCustom Queryxyz_prod_doc" is not a physical address.
I can use the shell command to open any physical address (aka, C:temp) with no problems.

When explorer is open, we typically paste "documentumCustom Queryxyz_prod_doc" into the address bar and then hit enter. My goal is to replicate this with VBA, and if i can get that far, I'd like to pre-populate a sql query from the VBA as well to automate some document searching. (So I am also interested in passing other information to the explorer window)

Essentially, i need to replicate opening explorer, pasting a address, (as i don't seem to be able to pass it with shell), and hitting enter, to start.

Thanks for any suggestions or ideas.

Hi there.
I want to open text files with vba in excel using:
open filename for input as #1 ....
As long as the file is in the My Documents folder, I have no problem. When the file is in another folder and another drive, and I have to give the path of the file, I use the chdir() function. However this doesn't work. ( actaually sometimes it does and sometimes it doesn't) .

Thanks for the advise.

To open files with a macro, I am currently using:

Workbooks.Open Filename:= "C:file_path.xls"

How can I use a variable as part of the file path?

In other words, before I try to open the file in my VBA code I will set a
variable (let's call it date_extension). Then I want to open a file that I
know will exist with a file path whose final characters before the .xls will
be the date_extension. How can I get the file path to be recognized with the
variable?

Thanks for your help

(P.S. I am a total novice, so I may not be familiar with commands or
procedures that are fairly basic for you folks.)

What is the code to open a URL or local html file with a command button?

Thank you for helping me, I'm a rookie.

Excel 2000

Thank you to colonal18 and Gary's Student, through combining your advice I'v
managed to come up with this code which opens the file who's name is in the
current cell and opens it with the application I want.

Sub playfiles()
Dim PlayFile
PlayFile = Shell("C:Program FilesWinampwinamp.exe C:MP3" &
ActiveCell.Value, 1)
End Sub

The only problem is it doesn't work on files with more than one word in
their name. If the cell I have selected has a file with 2 words in its name
e.g. Happy Birthday.mp3 - it opens WinAmp and treats it as if it was being
asked to open 2 files(one called Happy, one called Birthday) neither of which
exist.

The obvious (and impractical) way around this is to manually rename every
file with underscores_instead_of spaces.

Does anyone know a better solution?

Thanks

Phil

I can't seem to google this answer. I have a button in a spreadsheet that is
supposed to open a TIFF file. There are 3-4 users, and they all have
different default applications that open the TIFF format. How can I shell
open the TIFF files with whatever the default opener for that file type?

On my computer, it looks like this:

Private Sub OpenImage(FileName)

Dim OpenImaging

OpenImaging = Shell("C:Program FilesWindows
NTAccessoriesImageVuekodakimg.exe
(continue same line) K:Imagefolder" & FileName & ".tif", 1)

End Sub

But I know that I will not work on the other comps. Help, please!

Hi
I let user to open his/her own file with this VBA......

Sub ConsolFile()
Application.Workbooks.Open (GetFileName)
End Sub

Function GetFileName()
'Get a FileName (may change current drive/directory.)
Filename = Application.GetOpenFilename
'Get the current directory and drive
TempDir = CurDir()
'Switch back to original drive
ChDrive Mid(TempDir, 1, 1)
'Switch back to the orginal directory
ChDir TempDir
'Return the filename
GetFileName = Filename
End Function

It doesn't work 'cause it return false when user cancel....How can I fix
this problem?

Thanks
Jafery

Excel VBA Shell command issue: I have an .exe (non-Microsoft, report generator) that I need to open from Excel. When I open this .exe normally, the first window to open is a login window. Like any login, I input the username and password, gain access and go to work. However, when opening this .exe with the Shell command, I have a huge issue. When the Shell command fires, the login window opens as it should. BUT - the UN & PW fail. Everytime. SO, open this .exe normally and I can login. Open it with Shell, the same Un & PW fail. Something about the Shell is preventing the login window from working. I've done a fair bit of reading and can find no one that has had this issue. Any thoughts from any one?

I've got a macro that I run from a button in my Cal_file_processor.xls spreadsheet. It prompts the user to select workbooks using "CalFiles = Application.GetOpenFilename" which opens an Open File Dialogue window with multiselect enabled, then later I refer to the individual files as "Calfiles(counter)" with the counter counting up through all the files in the array as each one is opened and formatted by a called function.

I would like to automate this macro so that I can just run it as a scheduled task with no user input. The files being processed will always be in the same folder, although the file names and quantities will change, and it should run the formatting macro on every file in that folder. The question is, is there a simple plug and play substitute for GetOpenFilename that will automatically pull in an array of files names from a specific folder?

Sub Cal_file_Coverter()

Dim CalTemplate, CalSheet
Dim MywSheet As Worksheet
Dim Sht, counter, StatusCell, MacroPath, IdCounter
' set the array to a variable
Dim CalFiles As Variant
    
    'Opens multiple files loops through each file
   'True is for multi-select
   StatusCell = Range("E2").Address
   IdCounter = 0
    Range("D2:G300").ClearContents
   CalFiles = Application.GetOpenFilename(FileFilter:="microsoft excel files (*.xls), *.xls", Title:="Select
Cal Files to Extract Cal Sheets from", MultiSelect:=True)
           counter = 1
       'check to see if cancel selected in the box, which cause this to be an  error
     On Error Resume Next
     If CalFiles(1) = "" Then
         MsgBox "No Files Selected"
         End
         
      End If
       'turn off error checking
      On Error GoTo 0
     
 ' ubound determines how many items in the array
     While counter <= UBound(CalFiles)
     'file #_ of _ files status display on Cal Processor spreadsheet
     Workbooks("Cal_file_processor.xls").Sheets("Sheet1").Range("A9") = ("Processing
") & counter & (" of ") & UBound(CalFiles) & (" files")
           Workbooks.Open Filename:= _
            CalFiles(counter), ReadOnly:=True
            ActiveWorkbook.Sheets(1).Activate
            'Display current file name in Cal Processor spreadsheet
            StatusCell = Range("D3").Offset(IdCounter, 0).Address
            Workbooks("Cal_file_processor.xls").Sheets("Sheet1").Range(StatusCell) = ActiveWorkbook.Name
            'loops through sheets
       For Each MywSheet In ActiveWorkbook.Worksheets
            ActiveWorkbook.Sheets(MywSheet.Name).Activate
          Call CalSheetExtractor(StatusCell, IdCounter)
            'Debug.Print MywSheet.Name
       Next MywSheet
        Application.DisplayAlerts = False
        ActiveWindow.Close
        Application.DisplayAlerts = True
        
         'increment counter
         counter = counter + 1
     Wend
        Workbooks("Cal_file_processor.xls").Sheets("Sheet1").Range("A9") = _
        ("   Finished Processing ") & counter - 1 & (" of ") & UBound(CalFiles) & ("
files")
        Workbooks("Cal_file_processor.xls").Sheets("Sheet1").Range("A9").Interior.ColorIndex =
4
        
   End Sub
  
 Sub CalSheetExtractor(StatusCell, IdCounter)
  'insert all manner of formatting irrelevant to my question, here
  End Sub


I am trying to find and open a file with a variable file name in order to copy data from that file.

the file has a consistent syntax, i.e. "ExpLog_20110512_053701.txt", "ExpLog_20110513_064740.txt", etc., but the last 6 digits in the file name are a timestamp that changes daily.

I am using Excel 2007, so the filesearch function does not work.

Is there a way for me to search a folder for the filename that contains the 8-digit date (i.e. "20110513") and open that file?

I have already created the variables that express the 8-digit date, which is defined as:

year2 & month1 & day1


	VB:
	
 
input5 = InputBox("Today's Date(mm/dd/yyyy)", "Input") 
ActiveSheet.Range("A1") = input5 
 
month1 = Month(ActiveSheet.Range("A1")) 
If Len(month1) = 1 Then month1 = "0" & month1 
day1 = Day(ActiveSheet.Range("A1")) 
If Len(day1) = 1 Then day1 = "0" & day1 
year2 = Year(ActiveSheet.Range("A1")) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello,

I have a folder containing different .xls report files with the name format
"YYYY MM text.xls", where YYYY stands for year, MM for month.

I need a macro which, when run from an excel file saved in a different
loacation, based on the current date, e.g. January 7, 2009, will open the
file "2009 01 text.xls".

Thanks for your help,
Ciperian

Dear All,

I have developed a macro in excel 2003. It macro has ADO connection.

if i open this file in excel 2003, it takes 10 mints to open file. But at the same time in excel 2000 it open immediately.

Any one can advise?

Regards
Suresh
Excel VBA programmer
Seepz, Mumbai.
s_u_resh@yahoo.com

Hi all,

I have some 100 files to process, using this macro:

Columns("A:A").Select
    Application.CutCopyMode = False
    Range("A1:A10000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "M1"), unique:=True
    Range("M1:" & Range("M65536").End(xlUp).Address).Select
    Selection.Copy
    Windows("Book3").Activate
    Range("A65536").End(xlUp).Select
    Selection.Offset(1, 0).Select
    ActiveSheet.Paste
End Sub


it copies unique values from col A in an open file to a new file (book3). I can do this manually file by file and it appends values in the new file.

Is there a way to open the 100 files in excel and run the macro (with some modification) so it traverse thru the opened files one by one automatically and does this?

Someting like a for loop, may be?

Thanks,
Vasil

Excel Experts,

Each day, I want to open a file with path and name in the format of

T:StatementsMMMMCNC MMDDYY
where MMM=Current month and MMDDYY is the last business day

For example the file I opened this morning was:

TStatementsSEPMCNC 092005

For reference, I've used the following code to save a file with today's date
ActiveWorkbook.SaveAs Filename:= _
"TStatementsMyFile" & Format(Date, "yymmdd") & ".xls"

Also, I've used the following code to put the date of the last business date
in a cell:
ActiveCell.FormulaR1C1 = "=TODAY()-IF(WEEKDAY(TODAY())=2,3,1)"

So essentially what I need to do is to replace the code
Format(Date,"yymmdd") , which indicates today's date
with code that will indicate the last business day

I tried using the code I used for the formula into a FileOpen statement but
it didn't work.

Can this be done?

Thanks,
Alan

--
achidsey

Hello. I have users who, when they had Office 2000, were able to open files
that had the same name. (The files were in different directories, of course).

Now, with Office 2002, they cannot open the files. Is there any way around
this, or do they have to rename each file?

Example: A file name of FINANCIAL SUMMARY is in each directory. The
Directories are named 2000, 2001, 2002, 2003.

Thank you!
--
MB

Hello everyone,

I need to get a CSV file through FTP server located at

ftp://ftp.wwtgroups.com/RWRB/final that contains files with a date in the name, i/e.
RWRB_final_detail_20100517.csv
RWRB_final_detail_20100518.csv
RWRB_final_detail_20100519.csv
RWRB_final_detail_20100520.csv

Is it possible to make a macro (possibly stored in "This Workbook" of file) that will automatically open the file which has todays date,

so todays (June 14 2010) file would be

RWRB_final_detail_20100614.csv

Any help would be appreciated.

Thanks,
NT

Hi,

Does anybody know how to write an "import data" script where the users would select files with a File -> Open interface? thanks!

Dear Helpers,

Is there a simple solution to this problem. I want a macro that closes all open files with the exception of a specified file (which will be the active file that houses the macro).

James

I am trying to open a file using this code:

Workbooks.Open Filename:=Application.GetOpenFilename("PRODDATA file,*.xls")

When the Open File dialog window opens and I click on "Cancel", I get a Runtime error "1004" False.xls could not be found..."

How can I avoid this error?

Thanks,
Rich D

In Macros, How is it possible to record for a file name as YYYYMMDD to open
or save file names. Please inform me if possible with any other alternatives
to save or open files with similar naming convention in Macros. OR to pick up
name of the file from any cell where today() is mentioned


No luck finding an answer? You could always try Google.