Using Shell in VBA
csvfile = Shell("excel.exe" & " " & TargetFile, vbNormalFocus)
SendKeys excelfile 'rename the file as an .xls
Thank you for taking the time to report an issue.
What's wrong... Please write below.
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?
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?
Like opening a telnet session, send userid, password, and other commands ?
if command returns ok then send next command ?
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")
This runs query 1 and query 2 but give RUN TIME ERROR 6 (overflow for query3)
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
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
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.
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.