Opening another source file from macro code

Question

So, I'm working on a file in 1.1 Fixed Purch tab. Just basically clearing contents from certain cells and bringing updated material from BACKEND_Purchases_New_6-14-2018.xlsm file tab called "Contract Fixed". Both files are in same directory. Only one issue, I want the macro to ignore date when I'm pulling from BACKEND_Purchases_New_6-14-2018.xlsm file. How will I do that using below VBA?

Please explain in a way I can understand completely since I'm a beginner.

Sub attri_kinda_new()
 '
 ' attri_kinda_new Macro
 '

  Sheets("1.1 Fixed Purch").Select
  ActiveWindow.SmallScroll Down:=-3
  Range("B4:G4500").Select
  Selection.ClearContents
  Call Workbooks.Open(Filename:="BACKEND_Purchases_New_6-14-2018.xlsm", local:=True)
  Sheets("Contract Fixed").Select
  ActiveWindow.SmallScroll Down:=-18
  Range("AG2:AL5000").Select
  Selection.Copy
  Windows("US M2M Attribution 6-14-2018 training.xlsm").Activate
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Here is an interesting link for you to study on: https://stackoverflow.com/questions/2860797/how-do-i-open-a-file-if-i-only-know-part-of-the-file-name. I’m currently on my phone and unable to help further. Surely others here will. — JvdV, Jul 03 '18 at 18:32
Please edit all the code with 4 spaces or more (including first and last line of code) to have it layout correctly. — PaulH, Jul 03 '18 at 19:31

ShanayL · Accepted Answer · 2018-07-03T19:36:23.760

0

If you would like to choose a file to open you can use the File picker method like below:

Dim wb As Workbook

Set FileDialog = application.FileDialog(msoFileDialogFilePicker)
FileDialog.AllowMultiSelect = False
FileDialog.Title = "Select a file"
If FileDialog.Show = -1 Then
    strPath = FileDialog.SelectedItems(1)
End If

filepath = strPath

'open workbook
Set wb = Workbooks.Open(Filename:=filepath)

If you want to access a cell from Backend Purchase you can use:

wb.Sheets(index).Cells(1,1) 'Access Cell from workbook - change "index" to fit tab number

and modify it as you see fit.

If you want to copy information from one sheet to the other you could use the Range.Find method then copy and paste the results like:

Dim headernamerow as Integer
Dim headernamecolumn as Integer

'Get location first cell with data - change "index" to fit tab number
headernamerow = wb.Sheets(index).Range("A1:Z500").Find(What:="HeaderName", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns).row + 1
headernamecolumn = wb.Sheets(index).Range("A1:Z500").Find(What:="HeaderName", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column

'Paste information into 1.1 Fixed Purch tab - modify cell as needed
ThisWorkbook.Sheets("FixedPurchTab").Cells(1, 1).value = wb.Sheets(index).Cells(headernamerow,headernamecolumn).value

There may be a better way but this is the way I know.

edited Jul 03 '18 at 19:36

answered Jul 03 '18 at 19:08

ShanayL

1,217
2
14
29

ShanayL Your below code is very helpful. Now just how do I make the filename pull up without the date? There is only one of that filename in the folder (ignoring the date). I want the VBA code to ignore the date. Dim wb As Workbook Set wb = Workbooks.Open(Filename:="C:\Directory\BACKEND_Purchases_New_6-14-2018.xlsm") – Sorab Jul 03 '18 at 19:17
You cant ignore the date if you are trying to open it, its a part of the filename. If you ignore the date, the filename will no longer be valid. Are you trying to paste the filename somewhere without the date? – ShanayL Jul 03 '18 at 19:25
No, because I repeat the same process every week. Only difference is date is updated on the file. So next week's file will say "C:\Directory\BACKEND_Purchases_New_6-21-2018.xlsm"). The process I'm trying to do is the same. I don't want to update VBA everytime for filename. Is there no way to ignore date? – Sorab Jul 03 '18 at 19:28
oh ok. your title and description is a little misleading. You can use the filepicker method where a dialog box opens and you choose the file or you can set a folder and have your code navigate through files in the folder. I modified my answer. – ShanayL Jul 03 '18 at 19:33
Your VBA for selecting file works, but it debugs after that. After that last part of VBA for file selection. It should go to tab "Contract Fixed" to copy information, but it's not doing that. Last line of your VBA Code: Filepath = strPath Next line of my VBA code: Sheets("1.1 Contract Fixed").Select – Sorab Jul 03 '18 at 19:53
NVM...my bad...it worked. I didn't use the whole code properly. Thanks a bunch!!! – Sorab Jul 03 '18 at 20:09
Please enlighten me...is there a way to mark your answer as the answer or do I click Answer Your Question at the bottom and copy your response there? First day on this website :/ – Sorab Jul 03 '18 at 20:25
Ah! Just click the check mark beside my answer. Welcome to stack overflow!! – ShanayL Jul 03 '18 at 20:34
1

Sorry for the late reply...done! Your code has helped a lot!! – Sorab Jul 11 '18 at 15:36

Opening another source file from macro code

1 Answers1