Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 15th, 2005, 02:01 PM
Authorized User
 
Join Date: Oct 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default How do I call Excel script from Access?

I am trying to automate a process that right now requires that we take raw data from SAP, parse it with Excel for clean import to Access, process the data down in Access, then export to Excel again for reporting. My current problem in trying to automate this is that I cannot figure out how to execute the Excel script for the parsing, and for the reporting. They are two separate workbooks. The reason I am using code in the reporting workbook is the manual updating of the columns and various fields is tedious and prone to being missed. I want to be able to launch all of the functions from Access or one Excel spreadsheet. Hopefully with a 1 or 2 button type procedure.

Any ideas?

  #2 (permalink)  
Old August 15th, 2005, 02:25 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

What are you doing with the data that you can't use Access for ETL? Access is much better at this than Excel. What does the raw data look like?

mmcdonal
  #3 (permalink)  
Old August 15th, 2005, 02:26 PM
Authorized User
 
Join Date: Oct 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well, I seem to have found something that works:

objXL.Application.Run "DaySlide"

Where objXL is my Excel.Application object, and DaySlide is my desired function. I just don't understand how I might call something that is in a different module, or does this method find all functions in the objXL object? Now I get an error that says RESUME.XLW already exists in this location. The objXL.Application.Save code also fails.

Any ideas now?

  #4 (permalink)  
Old August 15th, 2005, 02:29 PM
Authorized User
 
Join Date: Oct 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by mmcdonal
 What are you doing with the data that you can't use Access for ETL? Access is much better at this than Excel. What does the raw data look like?

mmcdonal
The data is a delimited text file that has columns of data after a 7-8 row header of unwanted junk. I only use 4 columns out of about 50 that are in the raw data. The actual column headers are separated from the data by a blank row.

EDIT: If you know of an easy way to import this to Access I would love to hear it so I can eliminate the 1st step of using Excel.
  #5 (permalink)  
Old August 15th, 2005, 02:30 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Once you access the application object and the application file, you should be able to reference all the functions.
Try quitting the application instance between calls.


mmcdonal
  #6 (permalink)  
Old August 15th, 2005, 06:41 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Well, if it were me, I would parse the text file rather than running it through Excel first.

1. Use Scripting.FileSystemObject to open the text file for reading.

2. Read one line at a time until the first n characters = the first field's name. For example: If Left(strLine, 7) = "Column1" Then...

3. Skip the next line: If strLine = "" Then...

4. Split the remaining lines into an array one line at a time:

LineArray = Split(strLine, ",")

5. Take the columns that you need for your Access table

strCol0 = LineArray(0)
strCol22 = LineArray(22)
strCol40 = LineArray(40)
etc... but you can do this with the recordset already open...

Establish a connection to the current database
Open a recordset on the table you want to populate (you may have to create a new table each time from scratch. If so, use a template table and copy and rename it to the current database, then populate it.) Alternatively, add more data to an existing table and put a time signtaure on it so you know what dataset it was pulled from.

6. So alternate would be:

rst("Column1") = LineArray(1)
rst("Column14") = LineArray(14)
etc.

7. Then have your processing queries lined up and run those:

DoCmd.OpenQuery "qryYourQueryname"

etc.

8. Then push it out to Excel.

I use all these techniques and pull data directly from text files, and make pulls from SQL using time signatures (views, actually) for local processing this way.

I think this is more elegant than slinging the data through multiple applications. Maybe we could get your queries and reports looking right so you wouldn't need Excel at all.

Let us know if you need help on the code.

HTH




mmcdonal
  #7 (permalink)  
Old August 16th, 2005, 08:17 AM
Authorized User
 
Join Date: Oct 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Awesome. Absolutely awesome. I am going to try this as soon as I get a chance. I've never messed with the more complex ways of importing/parsing data in Access, but this makes sense to me so far. I'll let you know how it turns out!

Thanks!

  #8 (permalink)  
Old August 16th, 2005, 01:18 PM
Authorized User
 
Join Date: Oct 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok, so my file is tab delimited, and VBA is having a hard time opening the file. I can get it into an "object" variable, but I don't know where to go from there. I used the Excel.CSV class to get it loaded because I couldn't figure out any other way to get VBA to even recognize it.

  #9 (permalink)  
Old August 16th, 2005, 01:28 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Try this:
'----- Code Starts -----

Dim fso
Dim objStream
Dim objFile
Dim YourArray

Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists("C:\YourData.txt") Then
    Set objStream = fso.OpenTextFile("C:\YourData.txt", 1, False, 0)
End If

Do While Not objStream.AtEndOfStream
    strLine = objStream.ReadLine
    YourArray = Split(strLine, " ")
...
Loop
'----- Code Ends -----

Please note that the space between the "" in the Split() statement is a <Tab>.


mmcdonal


Similar Threads
Thread Thread Starter Forum Replies Last Post
Partial page update with Script call backs prak_p2p_wrx BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 2 March 14th, 2008 05:38 AM
Proxy script to call web services with Ajax 2mk Ajax 0 March 4th, 2008 04:58 AM
Call and/or start C# WinApp from script spif2001 C# 2005 0 September 20th, 2007 02:38 AM
Call Script function in server controll qadeerahmad General .NET 1 September 26th, 2004 11:09 PM
Call and run CGI script from a PHP script ... how? dbruins BOOK: Beginning PHP4/PHP 5 ISBN: 978-0-7645-4364-7; v5 ISBN: 978-0-7645-5783-5 1 June 10th, 2003 03:09 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.