Code that opens a file and uses data from that file to replace data in another

[SilverUnicrn]

I’m working in Excel 2016 and I’ve written the below code, but I keep getting errors and I’m unsure how to fix them. While working in the workbook “Budget & Execution Tool”, I want to run a code that will clear all data from the tab “Budget Changes” in the “Budget & Execution Tool”, open a second workbook, “Budget Changes Tab Only”, and then copy and paste the new data from the "Budget_Changes" tab in “Budget Changes Tab Only” to the tab “Budget Changes” in the “Budget & Execution Tool”, and then close the “Budget Changes Tab Only” workbook so that I can continue working in the “Budget & Execution Tool”. (Note: I have changed the name and location of the files to post online as they are for work.) Any help is greatly appreciated.


The error message I get is Run-time error ‘9’:
Subscript out of range


When I debug, it highlights the line beginning with “Set x =”.






'Replace Data on Budget Changes Tab


Sub clearSheet()


Sheets("Budget Changes").Cells.Delete



'Copy Budget Changes Tab


Dim x As Workbook, y As Workbook

Dim ws1 As Worksheet, ws2 As Worksheet

Set x = Workbooks("C:\Desktop\Budget & Execution Tool")
Set y = Workbooks.Open("C:\Desktop\Budget Changes Tab Only")


Set ws1 = x.Sheets("Budget Changes")
Set ws2 = y.Sheets("Budget_Changes")


ws1.Cells.Copy ws2.Cells

y.Close True
x.Close False


End Sub

[mattreingold]

If the macro executes from the "Budget and Execution Tool" workbook, you can assign x in a different manner.

You could say: Set x = Workbooks("Budget & Execution Tool") - you don't need to specify the path to the current workbook.

Another, perhaps more useful way would be to specify the desired directory (folder where the files are located) and then you can choose the workbook to open and the variable 'xlFile' will automatically have the file names and paths, you could then effectively do this: Set x = Workbooks(xlFile)

See code below for an example


ChDir "C:\User\Folder"
' Open folder where excel files are located
xlFile = Application.GetOpenFilename("All Excel Files (*.csv*)," & _
"*.xls*", 1, "Select Excel File", "Open", False)

' Set worksheet variables according to files chosen
Set x = Workbooks(xlFile)

[mattreingold]

ChDir would of course be the file path of the folder's location - you can change the .csv to .xlsx or .xml (depending on the desired file types in the locations)

[SilverUnicrn]

Thank you. I tried using Set x = Workbooks("Budget & Execution Tool") and got the same error.

I am executing from the "Budget and Execution Tool" workbook, and would like this to execute in a way that doesn't require you to select a file from a dialog box as it will always be the same file but may not always be the same user, so the less they have to do, the better.

[mattreingold]

You could also try this:


'//
Dim x As Workbook
Dim t As Worksheet


Set x = ActiveWorkbook
Set t = x.Sheets("Sheet1")
'//

This will set x to the active workbook (duh) and then you can also set variables to sheets within your workbook.

This is tested and should work, however if your code is opening other workbooks (if you have any other Workbooks.Open statements in your code) then you need to place the 'Set x = ActiveWorkbook' before you open any other workbooks (opening other workbooks through VBA will set that workbook to the 'active workbook').

I hope this makes sense, and if not please keep asking questions!!

[mattreingold]

Also, if it is of any use, you can then use the 't' variable to point to the sheet and manipulate its contents further as such:


t.[C16].Value = "This string will be inserted in cell C16 of worksheet t"

Where t is the desired worksheet. You can also set the cells to any value (not just a string) therefore you can also do this:


t.[C16].Value = myVariable

You can obviously change the cell, C16 was my example. Hope this helps!

[SilverUnicrn]

I'm sorry. I'm a complete newbie when it comes to VBA. My entire code was above. So to use your suggestion, where would I insert it into the code? What would I have to take out, if anything?

[mattreingold]

Is the drive you are trying to access a network drive? If so that is a whole different beast.

If not, I tested this and it should work:


Sub RunReport()


Sheets("Budget Changes").Cells.Delete


Dim x As Workbook, y As Workbook


Dim ws1 As Worksheet, ws2 As Worksheet


Set x = Workbooks("Budget & Execution Tool")
Set y = Workbooks.Open("Budget Changes Tab Only")


Set ws1 = x.Sheets("Budget Changes")
Set ws2 = y.Sheets("Budget_Changes")


ws2.Cells.Copy ws1.Cells


Workbooks("Budget Changes Tab Only").Close


End Sub


If there are any further errors please state which and and what lines, I hope this helps!

[mattreingold]

I omitted file paths because both workbooks were in the same folder and being accessed from the first (which is in the folder).

You can replace that with your file paths. Again, if there accessed from a network drive, that is a different ballgame.

C:\Users\username\Documents\Budget & Execution Tool (Or whatever the path is) would work fine.

[SilverUnicrn]

Thank you, I really appreciate the time you've taken and how helpful you've been. Unfortunately, they will be on a network drive though. How does that change it?

[mattreingold]

So it depends on the drive letter you are using. The problem arises because other users on your network (who may use this program on their machines) may have the drive mapped to a different letter then you.

People often set the directory to be the server share rather then the drive letter to work around this. What I have found, however, is that if I set my chDir to:


ChDir "S:"
' Open folder where excel files are located
xlFile = Application.GetOpenFilename("All Excel Files (*.csv*)," & _
"*.xls*", 1, "Select Excel File", "Open", False)

' Set worksheet variables according to files chosen
Set x = Workbooks(xlFile)

S:\ is the drive letter I have my network drive mapped to, using the code above, this simply opens file explorer from which you can navigate to whatever folder it is that you need, this is somewhat fuzzy to me on why or how it works, so I don't know if it will work for you. I stuck with this after banging my head for some while, partly because it works and I don't want to touch it, but also because I am the only one running this code, therefore mapping it to 'S:\' works for me.

If other users are going to use this program, or setting ChDir to your network drive letter doesnt work, you may have to look up how to map to a server share instead, something I toyed with for a while to no success...

You can read on that here (https://www.stackoverflow.com/questions/631401/access-network-share-from-within-vbscript-eg-filesystemobject) - I hope the former works, let me know!

[mattreingold]

This all matters, however, only if they are not found in the same location. If the file you are running is in the same folder as 'Budget Changes Tab Only' then you can run the code as I posted a few posts ago, just setting the workbooks to their names (no file paths are needed if they are from the same folder as the file executing the code).

Let me know how it runs and any errors!

[SilverUnicrn]

Thank you so much for all of your help! I went back through and double checked everything and it's working perfectly now! It was actually this code that is combination of what I had and what you gave me that did the trick. I must sheepishly admit that it didn't work the first time because I had a typo in it where I replaced the correct file names. I really do appreciate you working through this with me.


'Replace Data on Budget Changes Tab
Sub RunReport()


Sheets("Budget Changes").Cells.Delete

Dim x As Workbook, y As Workbook

Dim ws1 As Worksheet, ws2 As Worksheet

Set x = Workbooks("Budget & Execution Tool")
Set y = Workbooks.Open("C:\Desktop\Budget Changes Tab Only")


Set ws1 = x.Sheets("Budget Changes")
Set ws2 = y.Sheets("Budget_Changes")

ws2.Cells.Copy ws1.Cells


Workbooks("Budget Changes Tab Only").Close

End Sub

[mattreingold]

More then happy to help!

If you ever have another question don't hesitate to post.