Hi all. I don't do code very well, but I believe what I need must be done with code, and am therefore lost.

I can use the ODBC Database to bring in the table if I want, but there are problems with the way the data exports from the AS400 and I don't know how to manipulate in Access the way I just did in Excel.

I am looking for a way to tell Access to open a blank Excel file and run a saved Excel Macro I have. Can someone help me out please?

Thanks.

Hello,

At this point I have more questions than answers for you...

How can you run a Saved Macro in a Blank Spreadsheet? Is this a new Spreadsheet, which won't have any Excel VBA in it to speak of?

What does this Excel VBA look like?

Sorry I have taken so long to reply. I open a new Excel spreadsheet and go to Tools / Macro / and then I run my saved macro.

Here's the Macro as I have copied and pasted it:

Sub Collection_Over_90()
'
' Collection_Over_90 Macro
' Macro recorded 29/05/2007 by Terri-Lyn Love
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Documents and Settings\lovetl.EBI\Application Data\Microsoft\Queries\COLLECTIONSTART2.dqy" _
, Destination:=Range("A1"))
.Name = "COLLECTIONSTART2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Columns("E:F").Select
Selection.NumberFormat = "000000"
Columns("H:J").Select
Selection.Style = "Comma"
Range("L1").Select
Selection.Style = "Comma"
ActiveCell.FormulaR1C1 = "REP"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("L2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5] = """",RC[-1],RC[-5])"
Selection.AutoFill Destination:=Range("L2:L106")
Range("L2:L106").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Range("A2").Select
End Sub


Is that what you were looking for? That's what I get when I do a 'step into' the macro.

Hello again, Terri-Lyn Love (IMG:http://www.utteraccess.com/forum/style_emoticons/default/grin.gif)

You could run all of this from Access... And I take MS Query (*shudder*) questions, all the time, e.g.,

http://www.mrexcel.com/board2/viewtopic.ph...1323365#1323365

But, I have ask why wouldn't you set up a template/working file of some sort and simply Refresh the QueryTable Object? I don't follow adding a new QueryTable Object everytime you want data...

Sorry, but I always ask this of people trying to resolve QueryTable Object quandaries, e.g.,

http://www.mrexcel.com/board2/viewtopic.php?p=632282#632282

Here's an example of Refreshing:

http://www.mrexcel.com/board2/viewtopic.php?p=617276#617276

Thanks for all of that Nate.

My ultimate goal would be to get all of this done through Access, because that is where I need this data to be. I'm only using Excel because I don't know how to do the same thing in Access, and it was easy to figure out how to do it in Excel.

As for creating a template, well, I haven't gotten that far in my thought process yet.

If you know of a way to perform this stuff in Access, I'm listening.....

TL

Hi TL,

Doing this stuff in Access... That's problem, I'm not sure what stuff is...

What is the following query doing?


I assume that it connects to a DB of some sort and queries it...? But that's about as far as can get by looking at your code. (IMG:style_emoticons/default/confused.gif)

All of that code that I posted is the Excel macro that I recorded. Once I open Excel and click on Tools / Macro / Macros / Run and run my macro, it extracts a file from the AS400 (aka i5) then moves and formats the columns as I asked it to do. Which is; padding the date columns with leading zeros, manipulating the date field to show as dd/mm/yy, comparing 2 Rep columns, and joining them into one with all the Rep's in one column.

I managed to achieve all of this in Access thanks to your prompting. I no longer need Excel to act as a go-between for the i5 and Access. I now import the file directly to Access and have created one query to make all the changes to the data that I need.

It's wonderful and I have you to thank for it. Your simple comment "You could run all of this from Access..." set me off on the right track...I just started digging and playing around and have acheived the results I need. My biggest hurdle was trying to reformat the date column as I had in Excel...well, the key was in what I wanted to do....FORMAT (duh)...that is how I changed the field to have leading zeros, and to show as 01/05/07 (dd/mm/yy) You see, the date fields were being stripped of the leading zero if the date was the 1st through the 9th of the month. I needed that zero back to do my dates properly. I had no idea how to do that in Access, and I guess, couldn't figure out how to word the question....

Anyway thank you again. Here is how I acheived my results through one Access query:

To format my date field: LastPmtDate: Format([CMDTPY],"00\/00\/00")
To join my Rep fields: REP: IIf([QS36F_ARCUST]![CMSLMN]="",[QS36F_ARCUST]![CMTERR],[QS36F_ARCUST]![CMSLMN])

This has been an awesome learning experience for me both in Excel and Access. I've enjoyed it, and thank you for your time and effort.

Lil_t

You are welcome. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

Hmmm, that's the 2nd time in recent memory that a simple statement as such helped someone with a question achieve the desired outcome. I shall have to continue making these sort of statements, I suppose.

Glad to hear you're up and running, eh. o!

P.S. - I knew you recorded your Macro, a hand-written procedure wouldn't involve selecting Ranges, Columns, etc... You don't have to Select those to work with them. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)

Without UA and people like you, lackies like me would be lost forever....Keep up the good work ! (IMG:http://www.utteraccess.com/forum/style_emoticons/default/notworthy.gif)