Using Shell in VBA

I am writing code to open a .csv file in access and then save this as an .xls file. I can open the file using shell but haven't been able to manipulate excel. I've tried the AppActive method but it doesn't seem to change the focus to the excel file. Here is the code I'm struggling with.

csvfile = Shell("excel.exe" & " " & TargetFile, vbNormalFocus)

AppActivate csvfile
SendKeys "%F"
SendKeys "A"
SendKeys excelfile 'rename the file as an .xls

Sponsored Links:

Related Topics

multi lined shell command?
I have some VBA in a MS Access module that properly and successfully executes a shell command that executes a batch file in the following manner:Shell("C:\runthisfile.bat")I am trying to actually place the contents of the batch file into the module. The problem is that the batch file is many lines. How would I write that?

Shell("ftp.exe -s:%0)
Shell("open myftpserver")
Shell("username)
..
..
..
probably 100 lines with each line being a separate shell command, but that doesn't work and neither does
Shell(" all lines")Is it possible to essentially shell many lines at the same time thereby making it possible to take say 100 lines resident in a batch file and just placing them all in the VBA module with shell command(s) instead of calling out the batch file that resides outside of the ms access file? It seems that I can only do single line shell commands?


reg.exe through command prompt and Shell in VBA cause different behavior
I'm running Access 2010 32bit on Win7 x64 and was mostly aware of what it means in relation to registry and how it behaves with redirection, but was not aware it will cause discrepancy inhow included Windows libraries perform.

Here's description. Windows (from XP onward I think) include one brilliant little command line app reg.exe which enables tons of tricky operations with Registry in very simple manner. For example, I need to copy a key with subkeys and values to new location (from HKLM to HKCU).

This little app does it in a single line command! and can of course be used from VBA through Shell command.

However, same line run from command prompt and through Shell function copies different keys. The one through command prompt copies asked key, the one through Shell function copies redirected key (from Wow6432Node, instead from HKLM\Software).

Of course, I don't want that! I know everything will be fine on x32 Windows, but on x64 Windows it simply reads wrongkey.

And that's exactly what I tried to avoid by this approach! Is there a way to run reg.exe from VBA as if it's run from command prompt, or force it not to redirect?


lost ability to "shell" out when database moved
I had to build a new intranet machine a while ago and just found out that at the end of my macros, I get the error: "The expression you entered has a function MS Office Access can't find" -- I believe it is related to the "run code" macro which simply does this: shell("c:\runtest.bat") --- It looks like I can't run anything with the "shell" command - as if the "shell" command doesn't exist in Access anymore ? What has happened


Using Shell in VBA
I am writing code to open a .csv file in access and then save this as an .xls file. I can open the file using shell but haven't been able to manipulate excel. I've tried the AppActive method but it doesn't seem to change the focus to the excel file.

csvfile = Shell("excel.exe" & " " & TargetFile, vbNormalFocus)

AppActivate csvfile
SendKeys "%F"
SendKeys "A"
SendKeys excelfile 'rename the file as an .xls


Telnet from VBA
Is there a way to interact with a shell from vba code ?
Like opening a telnet session, send userid, password, and other commands ?
if command returns ok then send next command ?


How to run a Macro or VBA code in another database
I am designing 2 databases (frontend and backend) and writing all my macros and vba in the backend database. In the frontend database I basically have a form for user to select options (button commands).

I want to link this command buttons from Frontend database to run VBA codes/macros in the backend database.

So far I tried 2 options and none of them are working

1) Shell command - I used the following command found on internet
Call Shell ("filepath \x Macroname, 1)
I get run time error 76

(It allows ONLY to run queries and NOT Macros)

Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).OpenDatabase("filepath")
'db.Execute "query1"
db.Execute "query2"
db.Execute "query3"

This runs query 1 and query 2 but give RUN TIME ERROR 6 (overflow for query3)


How do I open another database using VBA?
so this relates to some access updating questions I've been asking. I went to that AutoFEUpdater site and read through it, and it all seemed a bit complicated and required too much user interaction. I'm dealing with non-pc literate people here and I'd like to make the whole thing program driven.

I saw an alternate solution on the website that involves opening a separate updating database, using that database to automatically replace the old file with the new file, then reopening the database. The updater program works fine, but I'm having a hell of a time trying to figure out how to get the program to open using a command button.

I've been doing research and it seems that the "shell" command is supposed to be used, but none of the places I've looked show how to actually use it. They only talk about the problems they have. What is the syntax for using the shell command? Am I supposed to have a specific reference set up in my VBA?

I'm so close, just can't open the damned updater


Trying to excute a batch file from VBA code
I am using MS Access 2007 and I am trying, from my VBA code, to execute (or run) a Batch file. Now the batch foile works, but not from my code.
I used to be able to just do this --> Call Shell("S:\Accounting\AP\Tex Files\File List Generator.cmd ", 1)
ANd it would runit. But using this I get a message telling me that it is an invalid procedure call. So upon looking around on the net I found tha ton place said that with MS Access 2007 you need to do this:
Call Shell(Environ$("COMSPEC") & " /c S:\Accounting\AP\1099 ITEMS\W9 Folder\Corrected 2011 W9\File List Generator.cmd ", vbNormalFocus)
But while it does not error out it dooes noting eitheer. What the batch file does, when it works, is produces a list of all the file names in a folder


32 bit cmd.exe & Shell
mates

Still on the learning curve for Access 03.sp2 running in a 64 bit win7. Since there are 2 cmd.exe's with the 32 bit version in C:\Windows\sysWOW64\cmd.exe, how do I ensure that the Shell command will use this 32 version? I can see problems with the Access 03.sp2 applications facing problems when I use shell involving 32 bit dos command if nothing is done to the VB6 codes


DB Crashes on open of VBA
I have a DB that worked fine yesterday. I came in this AM and it crashes every time I try to open any module or VBA code. I have tried Compact & Repair as well as migrating to a new shell DB.

This is pretty crucial and I am at a loss as to where to begin debugging. It's not when I'm running anything. Strictly when I try to open modules/code for developing.


TransferText
I need to move comma-delimited text files into Access.

I want to loop through a directory using VBA and create new tables in Access for each text file.

Right now, my VBA shell looks like this,
......

When I use Access's import wizard and step through (Delimited >> Delimiter = Comma, Qualifier = " >> Finish), my file imports without errors.

I save an import specification with those settings (Delimited, Delimiter = Comma, Qualifier = ") and re-import the file to verify the specification. Using the wizard, (Advanced, Specification = "Import" >> OK >> Finish). My file imports without errors.

But when I run the shell above, my file imports with 279 Type Conversion errors.

This occurs whether the syntax that follows TransferText is acImport or acImportDelim.


Access 2007 crashes on the network
Our Access 2007 database is constantly crashing on the network. The database has a 2007 shell linked to 2007 data tables on the same drive. Network has 1200 pcs and about 120 users - shell is about 120mb. Our repair involves copying a clean master shell - relinking to the data tables - creating a new shortcut and deploying the new shortcut across the network. This database was running last year OK in Access 2000 - in the summer the network was upgraded to Access 2007 and our problems started. At first the database was still in Access 2000 so we converted it to 2007 - still the same problem - next we rebuilt the database in 2007 by importing the objects from the 2000 database - stil we are having the same problem. Is it being caused by a rogue installation or 2 of Access 2007 ? When the database goes down it automatically creates a backup of the shell so we know when a problem has occurred. Any suggestions would be most welcome


Xcopy - copy reports one folder to another
I am trying to copy many files (reports as pdf) from one folder to another. I tried to do that with filecopy but the customers were complaining it takes too long. I try to use the shell command that makes the copy process faster as I heared.

I took the below code:
retval = Shell("XCOPY c:\DELL6400\*.* C:\test /E", 0)

And I tried to do the same as the above but with names for the path "from" and "to" because I retrieve them from the table in loop operation as below:

retval = Shell("XCOPY ") & filePath & " " & filePath2
...


Widget compendium?
Where might I go to see a collection of really great Access VBA event procedures, macros and widgets? I read a great Linux book called, "Wicked Cool Shell Scripts" and I thought, I bet there is a similar resource out there of Access event procedures and VBA code snippets. Where should I lo


VBA to close a folder.
programmers,

I have the below section of code that opens folders.

I now want to do the reverse. Is it possible?

Call shell("explorer.exe " & str_folder,vbNormalFocus


Multiple field names in Shell
Using Access 2003, Win 7. I'm trying to use the Shell function to open a specific XML file using Oxygen. The full Oxygen path comes from a field named "txtAppPath" on a form named "frmDefaults". The full path to the file comes from a field on the current form named "txtPath". The name of the file comes from a field on the current form named "cboSourceDoc".

When I use literal values for the application and file paths, it works:

Shell "C:\Program Files (x86)\Oxygen XML Editor 12\author.exe C:\_topics\" & Me.cboSourceDoc, vbNormalFocus

When I try to substitute the field names for either of these literal strings, I get an immediate "File Not Found" message. I've tried every combination I can think of. Can Shell do what I'm trying to do? I'd appreciate any hints on the syntax, please.


Call Window Fax and Scan from MS ACCESS
I am using window 7 x64 MS Access2007, and I'd liketo know how to SHELL the Window Fax and Scan from MS Access with one button click event.

P.S. I tried to use SHELL but I got


Run Pkpak in vb
needed, need to run pkpak from VB to zip 4 files to send to another system. Used shell function will pop up window but does not run zip function.

This is what I have tried. I created a .bat file which runs the routine if clicked on but does not run if through the code.

Shell "g:/download/dl.bat


VBA. Changing the camera screen position
I'm developing a VBA/Access 2003 application that needs to open one camera (example webcam) in a desktop position (shell).

What I can't do is to change the location that the window of the camera opens (or open it as full screen for example). Or to change the dimensions of the window the camera opens.

ANY ideas or help is


Command Prompt
Can someone tell me how I can open Command Prompt and start writing command lines to the Command Prompt window in VBA?

e.g. This opens the Command Prompt window but I need to write lines to it via VBA

Shell ("cmd ")