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?

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

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?

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.

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

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

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!

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.

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