Excel Macro runs on my computer but no one else

We are on slightly different networks but its very open

Page 1 of 1

3 Replies - 8385 Views - Last Post: 05 November 2009 - 11:09 AM Rate Topic: -----

#1 guyfromri  Icon User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 836
  • Joined: 16-September 09

Excel Macro runs on my computer but no one else

Post icon  Posted 02 November 2009 - 01:25 PM

Ok, so I have this macro I created to open all the workbooks we need, copy the right sheet from each workbook, paste it in one common workbook and close the opened workbook. It works like a charm for me. I have tried it several different ways, creating a new workbook and using the workbook I run the macro from. my problem is, when I try to run it on anyone elses computer, it doesn't work. I get stuck at the line "thisworkbook.activate". I left the string in there that I tried previously so you could see the error I got the first time. That was "strnfile....." for some reason it won't recognize the original workbook. Any ideas? Thanks in advance for any help!

Sub OpenAllIn1()

With Application
	.ScreenUpdating = False
	.DisplayAlerts = False
	.EnableEvents = False
End With

MsgBox ("Do not do anything while this is running. It may take up to a minute depending on the speed of your computer. It will tell you when it has finished"), vbExclamation
strndrive = InputBox("Please enter the letter of the drive you go to when you want to view the grids")

'**	Dim strnfile As String
	
	
'**	strnfile = InputBox("Enter Your File Name. Although this is a temporary file, it will be saved to My Documents. If you want to delete it, you have to do so manually.", "File Name", "Name Your File")
	
'**	Set newbook = Workbooks.Add
'**	   With newbook
'**		.Title = "Temp Grid"
'**		.Subject = "All In One"
'**		.SaveAs Filename:=strnfile
'**	End With
	
'**	With Application.FileDialog(msoFileDialogFolderPicker)
'**		.AllowMultiSelect = True
'**		.InitialFileName = ""
'**		.Show
'**		strnfile = .SelectedItems(1)
'**	End With
	
	Workbooks.Open (strndrive & ":\'***THIS IS WHERE I PUT MY FILE  PATH. FOR ALL INTENTS AND PURPOSES I REMOVED IT")
	Sheets("grid").Select
	Cells.Copy
	ThisWorkbook.Activate
	Sheets("source group").Select
	Cells.PasteSpecial
	Workbooks("punch list and grid-'**NAME OF MY FILE").Close

'(((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
'))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
'AND THEN I REPEAT THAT LAST 7 LINES OF CODE ONCE FOR EVERY WORKSHEET. I HAVE TO OPEN
'AND CLOSE A TOTAL OF 35 FILES.
'))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
'(((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((

	
   
   
	With Application
	.ScreenUpdating = True
	.DisplayAlerts = True
	.EnableEvents = True
	End With


	Sheets("cp grids").Activate
	Range("a26") = "The last time you imported all grids was " & Now()
	MsgBox ("Done!")

End Sub



Is This A Good Question/Topic? 0
  • +

Replies To: Excel Macro runs on my computer but no one else

#2 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Excel Macro runs on my computer but no one else

Posted 02 November 2009 - 04:37 PM

Since workbooks are on a network server, perhaps you could Use UNC (Universal Naming Convention) and not be concerned with each user's drive mapping, you address the server by name.
Example (Fritz is the server name):
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open("\\Fritz\Admin\STWMAT\Central Region Materials\PAVEMENT MANAGEMENT\airports\Report Production\AASP.xlsx")

I don't see why you need the Activate code. I have never used it. Also, don't have to Select or Activate sheets or cells to manipulate them. Address sheets by name or index, cells by reference. The cell cursor never moves.
Copy/Paste example
Worksheets("Sheet1").Range("A1").Copy
Worksheets("Sheet1").Paste Destination:=Worksheets("Sheet1").Range("B1")
Application.CutCopyMode = False
Careful, if cells have formulas with relative referencing, the formulas will be copied but referenced cells will be adjusted, absolute referencing will not.

Should not have to open/close the workbook for each worksheet copy/paste. Use loop structure, something like:
' Declare Current as a worksheet object variable.
Dim Current As Worksheet
' Loop through all of the worksheets in the active workbook.
For Each Current In Worksheets
   ' Insert your code here.
Next
If you don't want to address every worksheet, refer to only those sheets that are index 1 through 5, in a For Next loop. Depending on your workbook structure, there may be other ways to identify the sheets you want to address.

This post has been edited by June7: 02 November 2009 - 05:21 PM

Was This Post Helpful? 1
  • +
  • -

#3 guyfromri  Icon User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 836
  • Joined: 16-September 09

Re: Excel Macro runs on my computer but no one else

Posted 05 November 2009 - 08:39 AM

Thank you very much. The only problem I have now is all of the work books are in a different folder...so for instance when I open the folder it looks like

workbooks.open("y:\implementation\conversions\partner 1\punch list")
'copy/paste action...close it out'
workbooks.open("y:\implementation\conversions\partner 2\punch list")


'each one is in a different folder on the network and I only need one 'worksheet from each. Are you saying that I don't have to open the 'workbooks to copy the sheets?

This post has been edited by guyfromri: 05 November 2009 - 08:40 AM

Was This Post Helpful? 0
  • +
  • -

#4 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Excel Macro runs on my computer but no one else

Posted 05 November 2009 - 11:09 AM

No, the workbooks have to be opened, not necessarily visible. In preparing my post I lost track that you copy/paste only one sheet per workbook. Guess need to clarify why running from other computers. Multiple users, each user accessing the same set of 35 workbooks, and performing copy/paste on all 35? That doesn't sound right, what do I not understand?

You want all 35 to copy/paste to one new workbook? Looks like the pathing is standardized with each folder having a number suffix. Use a For Next loop to run the code that will open/close each wb, perform the copy/paste. Build the wb open string by using the For index as the number suffix for the folder name.
For i = 1 to 35
   strWB = "\\servername\implementation\conversions\partner "& i & "\punch list"
   Set oBook = oExcel.Workbooks.Open(strWB)
   'copy/paste code
   'probably need looping structure here to increment the sheet index if you want each paste on a new sheet
   'or if copy/paste is only one column, use index to increment the paste column and put all on one sheet
   'can also use code to set the column label
   oBook.Close False
Next
Is 'punch list' the wb name or last folder? Need wb name with .xls or .xlsx or .xlsm suffix. Assumes 35 workbooks exist, might need error handling for case if not.

This post has been edited by June7: 05 November 2009 - 11:28 AM

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1