How to make Access run a macro in the excel output file

[sidewayze40]

Thank you in advance for any help on this... I have built a benchmarking program in access... once the user makes their selection of the items to compare, the program will output an excel file. The file has column headers for each possibility on the form and thus has lots of empty columns. I am looking for a way to have this code ran on the excel file immediately after the output.

Sub DeleteEmptyRowsAndColumns()
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r

LastColumn = ActiveSheet.UsedRange.Column - 1 + _
ActiveSheet.UsedRange.Columns.Count
Application.ScreenUpdating = False
For c = LastColumn To 1 Step -1
If Application.CountA(Columns(c)) = 1 Then Columns(c).Delete
Next c
End Sub

Is this possible?

 

[sidewayze40]

BUMP!

 

[kjv1611]

2 ways of doing it:
1. Put the code in the Excel workbook - if it's a static file you're using, or a template, should be easy enough. The only issue I've found with this method (beginning with Excel 2007) is that you'll end up getting prompts from Excel about saving the workbook as macro-enabled or not... if you're also saving the workbook in code.

2. Use the Excel Application object within Access. This should be easy enough to accomplish, and this way, you keep the code out of Excel altogether.

Here's an example of how I would do with your above code if I were to run it from Access:
1. In the Access VB Editor window, go to Tools - References, and make sure you've a reference to Microsoft Excel ##.# Object Library where the ##.# part will depend upon your version of Office.

2. Put the below code into a module:

Sub DeleteEmptyRowsAndColumns()
	Dim xlApp As Excel.Application
	Dim wb As Excel.Workbook
	Dim ws As Excel.Worksheet
	Dim lngLastRow As Long [GREEN]'Didn't see your row variable dimmed, so added that[/GREEN]
	Dim r as Long [GREEN]'Another one not declared.[/GREEN]
	Dim intLastColumn As Integer [GREEN]'Not declared in your example[/GREEN]
	Dim c As Integer [GREEN]'Not previously declared[/GREEN]
	
	Set xlApp = New Excel.Application
	Set wb = xlApp.Workbooks.Open("C:\YourWorkBookNamePath\YourWorkBookName.xlsx") 
		[GREEN]'Important that all Excel objects are created under your main xlApp object, 
		'else you'll wind up with leftover Excel processes in memory after closing out the program.[/GREEN]
	Set ws = wb.ActiveSheet
	
	xlApp.DisplayAlerts = False [GREEN]'If there are any messages in Excel you want to hide during this process[/GREEN]
	
	lngLastRow = ws.UsedRange.Row - 1 + _
		ws.UsedRange.Rows.Count
	xlApp.ScreenUpdating = False [GREEN]'Here again - have to use the Excel application, since code will be in Access
		'I would probably move the ScreenUpdating line up to where the DisplayAlerts line is if it were me.[/GREEN]
	For r = lngLastRow To 1 Step -1
		If xlApp.CountA(ws.Rows(r)) = 0 Then ws.Rows(r).Delete
	Next r

	intLastColumn = ws.UsedRange.Column - 1 + _
		ws.UsedRange.Columns.Count
	[GREEN]'xlApp.ScreenUpdating = False 'You already set this to False, so this is a wasted line[/GREEN]
	For c = intLastColumn To 1 Step -1
		If xlApp.CountA(ws.Columns(c)) = 1 Then ws.Columns(c).Delete
	Next c
	
[GREEN]'CleanUp[/GREEN]
	Set ws = Nothing
	Set wb.Close [GREEN]'If you want to close the workbook - and you'll want to specify whether or not to save..[/GREEN]
	Set wb = Nothing [GREEN]'Whether leaving the workbook open or not[/GREEN]
	xlApp.Quit [GREEN]'If you want to quit the workbook and exit Excel after code is run[/GREEN]
	Set xlApp = Nothing [GREEN]'Whether leaving Excel open or not[/GREEN]
	
[GREEN]'This next bit of cleanup is probably unnecessary, but I've just gotten into the habit of clearing out all variables as best I can[/GREEN]
	lngLastRow = 0
	r = 0
	intLastColumn = 0
	c = 0
End Sub

3. Call the code now in whatever action that was opening the workbook or doing whatever before you wanted this code run, like this:

Private Sub MyButton_Click()
     DeleteEmptyRowsAndColumns
End Sub

By the way, as documented in the code, I noticed none of your variables are declared. When you do this, you'll end up using more memory than is necessary for the small macro, b/c the variables are set to Variant which uses more memory than Integer or Long, b/c it's much more flexible. You shouldn't allow that for something like Row and Column numbers.

I didn't test the code, but I've been using the Excel Object in Access VBA code a decent amount, myself, of late.

 

[sidewayze40]

Thank you for the reply.. i have a couple more questions. Previously when the user clicked the button to compare their choices it would run a Marco that ran through all of the queries and such and then output the .xlsx file which was called tbl_benchmark and autostart the file. Now i changed this so it will run and event procedure which i have

DoCmd.RunMacro "mcr_benchamrk"

DeleteEmptyRowsAndColumns

End Sub


The macro runs just like it did before but the module does not seem to do anything. I'm clearly doing something wrong and just need some more direction! Thanks again for your help this will be soo much cleaner for the users when i can get this to work.

 

[sidewayze40]

BUMP!

 

[kjv1611]

If you're just going to run the macro from vba, you could just as well put the whole thing in vba...

DoCmd.SetWarnings False
DoCmd.OpenQuery "Query1"
DoCmd.OpenQuery "Query2"
DoCmd.OpenQuery "Query3"
'...etc...
DoCmd.SetWarnings True
DoCmd.Transfer

DoCmd.TransferSpreadsheet '..fill in the details..

Application.FollowHyperlink "FileAddress"

Something to that extent.

 

[kjv1611]

Well, I used Application.FollowHyperlink

The other method would be to use the Excel object (have to enable it under Tools - References), and create a new workbook, and open that way... you just have to make sure you close out the object(s) you create to avoid having additional Excel processes floating around after you close the workbook.

I think with the FollowHyperlink method you may get a security warning/prompt dependent upon your settings.

 

[TheAceMan1]

Howdy kjv1611 . . .

[green]'Important that all Excel objects are created under your main xlApp object,
'[purple]else you'll wind up with leftover Excel processes in memory[/purple] after closing out the program.[/green]

Any idea why these left over processes?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[kjv1611]

When I've ran it in the past, say I did so like this:

Private Sub BuildExcelStuff()
  Dim xlApp As Excel.Application
  Dim wb As Excel.Workbook
  Dim ws As Excel.Worksheet

  Set xlApp = New Excel.Application
  Set wb = [higlight]xlApp[/higlight].Workbooks.Open("d:\MyWorkbookPath\WorkBook.xlsx")
  Set ws = wb.ActiveSheet

  'Do Stuff

  Set ws = Nothing
  wb.Close
  Set wb = Nothing
  xlApp.Quit 'If you need to close it
  Set xlApp = Nothing

Everything opens, closes, and goes away all nice and pretty.

On the other hand, if you do it like this, for instance:

Private Sub BuildExcelStuff()
  Dim xlApp As Excel.Application
  Dim wb As Excel.Workbook
  Dim ws As Excel.Worksheet

  Set xlApp = New Excel.Application
  Set wb = [higlight]Excel[/higlight].Workbooks.Open("d:\MyWorkbookPath\WorkBook.xlsx")
  Set ws = wb.ActiveSheet

  'Do Stuff

  Set ws = Nothing
  wb.Close
  Set wb = Nothing
  xlApp.Quit 'If you need to close it
  Set xlApp = Nothing

Then another instance of Excel is automatically created, and thus when you close everything else... but no extra instance in code to close the extra Excel instance (for the Workbook object), then you've essentially created at least 2 different instances of Excel, open in memory, but only are able to close 1. When closing the Workbook in the second instance, the actual instance of Excel in memory is still left open.

You know, I wonder, if that could only be because of the way the PERSONAL.XLS or PERSONAL.XLSB file is opened beginning with Excel '07. When you have a personal macro workbook, with '07 (and I imagine '10), when you open any other workbook, it lists the PERSONAL.XLS/XLSB file in your recent items, and you can't just flat close your one workbook to close the entire application. If you close the one workbook, Excel still sits there looking at you, waiting for your next action. To me, that was really annoying, which is why I added the Exit button to the Quick Access Toolbar.

Is that making any sense?

 

[sidewayze40]

KJV1611, I feel like I need to apologize and thank you in advance! For the life of me I cannot get this to work. Here's what i am doing... I took your code(just changing my file location), plugged it into a module and tried to call it up as i mentioned before. Onclick the event procedure runs the macro... Now from here what should i have the macro do? either autostart the excel file or just save it to a certain location and file name? Then in the event procedure i have it call up the module just using

DeleteEmptyRowsAndColumns

from here whether i have the macro autostart or not nothing happens to the file but i get a pop up from Excel that is titled File Now Available, and in the pop up it says "tbl_benchmark" is now available for editing. Choose read-Write to open for editing. then there are two button either Read-Write or Cancel. If i choose Read-Write the file doesn't open excel just freaks out and locks up.

Then i tried to use your second suggestion(except i left the macro to run as is, i have so many queries i didn't want to have to type them all out). So i had this

DoCmd.RunMacro "mcr_benchmark"

Application.FollowHyperlink "C:\Users\apaparone\Desktop\tbl_benchmark.xlsx"

and then i tried lots of different ways to either call up the module with some changes made to the code or just by running the code right below it. Again, i'm sorry for not understanding this yet, it's definitely beyond my skill set at this point. Google hasnt been able to help me figure this out either so i have to ask you for some more help!

 

[kjv1611]

My guess is that from all your trying, you've gotten some Excel instances of that workbook stuck in memory.. So, I'd suggest doing 1 of 2 things, up to you:

1. Close any open Excel workbooks, then open Task Manager, and kill off any remaining Excel processes (on process tab).

2. Other option would be to close out of everything and reboot, that'll make sure everything is cleared out of memory.. then try your testing.

The resaon I say that is that it appears you're opening the workbook, then trying to open it again...

For instance, if you're opening the workbook from VBA AND the macro, then it's trying to open twice... then it's closing it from the macro or the VBA, and that's when it says "now ready for editing"..

Otherwise, what part of the code doesn't work? Any error messages, what?

 

[sidewayze40]

Eureka! and I'm an idiot! After all of that I finally realized that I wasn't saving it after the module ran. Sorry about the wasted time, and thank you for the code, it works perfectly.

Now one last piece... I have the macro and module saving to a location under \Documents\tbl_benchamrk.xlsx. What would I need to add to the code in order for it to grab the username of any user and save it to their \Documents location?

 

[kjv1611]

To get the username, you can simply use Environ("UserName")

However, if you're just wanting to save to the location, I'd suggest you don't need even to do that, but to rather use the built-in Windows helps. That way, it'll be less likely to matter whether the person is running Windows XP, Vista, or 7.

Here's a reference as to what I'm talking about:

http://en.kioskea.net/faq/951-vba-vb6-my-documents-environment-variables

--specific, except it's saving to Desktop - you should be able to adapt to anything else.

http://msdn.microsoft.com/en-us/library/aa365247(v=vs.85).aspx

--by Microsoft - covers quite a bit of information, though doesn't directly tell how to put it into VBA that I noticed.

I don't have the issue of dealing with this myself, b/c I always have saves made to a network share drive... so far.