Prev: MSHTA.EXE starts invisible
Next: Need help traversing a folder and/or identifying sub-folders
From: cleech on 7 Jun 2010 13:48
Hello All:

I have .vbs script that I use to loop through a series of xml files and
import specific data into .csv file. Later I import the .csv into an access
db. Ultimately, I would like to automate the entire process. So I'm looking
for some advice/recommendations.

The vbScript currently has the file locations and output file hardcoded and
I change it each time I run the script. Is there a way to pass
parameters/variables for the file location and output file to the vbScript?
And could the script then be called from vba with the parameters?

Could the data I'm taking from the xml files be loaded directly into an
access table?

Is there a good way of converting the .vbs into VBA(Access 2007)?

Sorry for the slew of questions. I'm just wondering if anyone has done
something similar and might have some advice.

Please let me know if you would like addtional info or if I can clear
anything up.
Thanks in advance for any help.


From: Tom Lavedas on 7 Jun 2010 16:05
On Jun 7, 1:48 pm, cleech <cle...(a)discussions.microsoft.com> wrote:
> Hello All:
>
> I have .vbs script that I use to loop through a series of xml files and
> import specific data into .csv file.  Later I import the .csv into an access
> db.  Ultimately, I would like to automate the entire process.  So I'm looking
> for some advice/recommendations.
>
> The vbScript currently has the file locations and output file hardcoded and
> I change it each time I run the script.  Is there a way to pass
> parameters/variables for the file location and output file to the vbScript?  
> And could the script then be called from vba with the parameters?  
>
> Could the data I'm taking from the xml files be loaded directly into an
> access table?
>
> Is there a good way of converting the .vbs into VBA(Access 2007)?  
>
> Sorry for the slew of questions.  I'm just wondering if anyone has done
> something similar and might have some advice.
>
> Please let me know if you would like add[i]tional info or if I can clear
> anything up.
> Thanks in advance for any help.

Q: Is there a way to pass parameters/variables for the file location
and output file to the vbScript?

A: See the discussion of Arguments in the WSH (offline) help file:
(URL all one line)
http://www.microsoft.com/downloads/details.aspx?FamilyId=01592C48-207D-4BE1-8A76-1C4099D7BBB9&displaylang=en

Q: Could the data I'm taking from the xml files be loaded directly
into an access table?

A: I don't see why not. It can't be too much harder than reading it
into Excel. However, I don't know the Access VBA/DOM. I'd ask that
question in an Access based ng.

Q: Is there a good way of converting the .vbs into VBA(Access 2007)?

A: In general it has been my experience that such conversion is
usually fairly straight forward. It consists of removing references
to the Application object for the particular application. For
example, if you have a line like this ...

set oXL = createobject("Excel.application")

It needs to be removed and then every line that has a reference to oXL
needs that to be removed (and the dot that follows), as well. Then
the main body of your script needs to become a names Sub. Oh, and any
reference to the WScript object will either need to be removed or
replaced with an equivalent. The major example is the WScript.Echo,
which needs to be changed to a MsgBox. Otherwise, nearly everything
else will work as written in VBS.

The VBA compiler will point out any remaining lines that fail to meet
the proper syntax. Note that the WSH Arguments question you asked
earlier does not apply, if you convert the script to VBA. You will
need to use VBA techniques for that (read a cell of create a User
Form).

Finally, I know that Excel can load XML files directly (at least it
does in the O2003 version). I'd explore whether Access 2007 can read
them directly - it just might.
_____________________
Tom Lavedas
From: Paul Randall on 8 Jun 2010 17:58
Concerning translating between VBS and VBA:
The scripting help file, SCRIPT56.CHM, contains sections that specifically
deal with features found in one that are not found in the other. Look in
the table of contents: Windows Script Technologies -> VBScript -> User's
Guide -> third and fourth items in the list.

Concerning using VBScript to use Access data files, you might want to look
at this thread:
http://groups.google.com/g/78d7fef5/t/a53c28458914569a/d/f6b5adea023e49d2

I think you should be able to do the entire job in VBA.

-Paul Randall

"cleech" <cleech(a)discussions.microsoft.com> wrote in message
news:8128718C-0732-4B19-8E44-2E722F2E30A9(a)microsoft.com...
> Hello All:
>
> I have .vbs script that I use to loop through a series of xml files and
> import specific data into .csv file. Later I import the .csv into an
> access
> db. Ultimately, I would like to automate the entire process. So I'm
> looking
> for some advice/recommendations.
>
> The vbScript currently has the file locations and output file hardcoded
> and
> I change it each time I run the script. Is there a way to pass
> parameters/variables for the file location and output file to the
> vbScript?
> And could the script then be called from vba with the parameters?
>
> Could the data I'm taking from the xml files be loaded directly into an
> access table?
>
> Is there a good way of converting the .vbs into VBA(Access 2007)?
>
> Sorry for the slew of questions. I'm just wondering if anyone has done
> something similar and might have some advice.
>
> Please let me know if you would like addtional info or if I can clear
> anything up.
> Thanks in advance for any help.
>
>


 | 
Pages: 1
Prev: MSHTA.EXE starts invisible
Next: Need help traversing a folder and/or identifying sub-folders