VBA: Open A Workbook With Constant Path But Different File Name

C

CarloPat89

New Member
Joined
Sep 28, 2016
Messages
11
Hi everyone,
I'm having an issue with <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>, I need to open a workbook with constant path but the file name id linked to a cell (which will change every week).
My code is as follow:


Dim wk1 As Workbook


Application.ScreenUpdating = False

Set wk1 = ThisWorkbook
'
Range("E18").Select
Workbooks.Open(wk1.Path & "/" & "Carlo.xlsx").Activate
Range("E18:AK22").Select
Selection.Copy
Windows("4. retail_input.xlsx").Activate
Range("E18:AK22").Select
Range("E36").Select
Workbooks.Open(wk1.Path & "/" & "Carlo.xlsx").Activate
Range("E36:AK36").Select
Application.CutCopyMode = False
Selection.Copy
Windows("4. retail_input.xlsx").Activate
Range("E36:AK36").Select
ActiveSheet.Paste Link:=True

Workbooks.Open(wk1.Path & "/" & "Carlo.xlsx").Save
Workbooks.Open(wk1.Path & "/" & "Carlo.xlsx").Close


I would like not to use the filename Carlo.xlsx but insted i'd like to link the filename to the cell H62 of sheet1,
Can someone help me?
Thanks a lot :):)
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Sure that's an easy one ;):
Code: 
Dim wk1 As Workbook[COLOR=#ff0000], FileName As String[/COLOR]


Application.ScreenUpdating = False

Set wk1 = ThisWorkbook
[COLOR=#ff0000]FileName = Sheet1.Range("H62").Value[/COLOR]
Range("E18").Select
Workbooks.Open(wk1.Path & "/" & [COLOR=#ff0000]FileName & "[/COLOR].xlsx").Activate
Range("E18:AK22").Select
Selection.Copy
Windows("4. retail_input.xlsx").Activate
Range("E18:AK22").Select
Range("E36").Select
Workbooks.Open(wk1.Path & "/" & [COLOR=#ff0000]FileName & "[/COLOR].xlsx").Activate
Range("E36:AK36").Select
Application.CutCopyMode = False
Selection.Copy
Windows("4. retail_input.xlsx").Activate
Range("E36:AK36").Select
ActiveSheet.Paste Link:=True

Workbooks.Open(wk1.Path & "/" & [COLOR=#ff0000]FileName & "[/COLOR].xlsx").Save
Workbooks.Open(wk1.Path & "/" & [COLOR=#ff0000]FileName & "[/COLOR].xlsx").Close
 
Last edited:
Upvote 0
Thank you very much, it works perfectly!! =D

Can I ask you another thing?
Basically every time i open up a file there pop up, as a lot of formulas are connected to other sources, hence an update is asked.

When I run the VBA code it stops to make me click on the popup, is there a way to speed up this process? as I am going to use your code with a lot of files =)

Thanks aggain,
Carlo
 
Upvote 0
BQardi said:
Sure that's an easy one ;):
Code: 
Dim wk1 As Workbook[COLOR=#ff0000], FileName As String[/COLOR]


Application.ScreenUpdating = False

Set wk1 = ThisWorkbook
[COLOR=#ff0000]FileName = Sheet1.Range("H62").Value[/COLOR]
Range("E18").Select
Workbooks.Open(wk1.Path & "/" & [COLOR=#ff0000]FileName & "[/COLOR].xlsx").Activate
Range("E18:AK22").Select
Selection.Copy
Windows("4. retail_input.xlsx").Activate
Range("E18:AK22").Select
Range("E36").Select
Workbooks.Open(wk1.Path & "/" & [COLOR=#ff0000]FileName & "[/COLOR].xlsx").Activate
Range("E36:AK36").Select
Application.CutCopyMode = False
Selection.Copy
Windows("4. retail_input.xlsx").Activate
Range("E36:AK36").Select
ActiveSheet.Paste Link:=True

Workbooks.Open(wk1.Path & "/" & [COLOR=#ff0000]FileName & "[/COLOR].xlsx").Save
Workbooks.Open(wk1.Path & "/" & [COLOR=#ff0000]FileName & "[/COLOR].xlsx").Close
Click to expand...


Thank you very much, it works perfectly!! =D

Can I ask you another thing?
Basically every time i open up a file there pop up, as a lot of formulas are connected to other sources, hence an update is asked.

When I run the VBA code it stops to make me click on the popup, is there a way to speed up this process? as I am going to use your code with a lot of files =)

Thanks again,
 
Upvote 0
Can you do me a favor? Try out this code. It should do the exact same thing (I also implemented the automatic updating of the opened workbook, so the popup won't show):
Code: 
Application.ScreenUpdating = False
With Workbooks.Open(ThisWorkbook.Path & "/" & Sheet1.Range("H62").Value & ".xlsx", xlUpdateLinksAlways) [COLOR=#008000]'You can insert ", xlUpdateLinksAlways" in your original code, if this code doesn't work as expected...[/COLOR]
    .Range("E36:AK36").Copy
    Windows("4. retail_input.xlsx").Range("E36:AK36").Paste Link:=True
    .Close True
End With
Application.CutCopyMode = False
 
Upvote 0
BQardi said:
Can you do me a favor? Try out this code. It should do the exact same thing (I also implemented the automatic updating of the opened workbook, so the popup won't show):
Code: 
Application.ScreenUpdating = False
With Workbooks.Open(ThisWorkbook.Path & "/" & Sheet1.Range("H62").Value & ".xlsx", xlUpdateLinksAlways) [COLOR=#008000]'You can insert ", xlUpdateLinksAlways" in your original code, if this code doesn't work as expected...[/COLOR]
    .Range("E36:AK36").Copy
    Windows("4. retail_input.xlsx").Range("E36:AK36").Paste Link:=True
    .Close True
End With
Application.CutCopyMode = False
Click to expand...


Unfortunately it doesn't work :(
But the xlUpdateLinksAlways works =) thanks!

One last question similar to the last one, I still have popups saying that some file cannot be updated... Do you have a code also for this ??
Thanks a lot really!!
Carlo
 
Upvote 0
Sure, but do you just want to ignore the updating of such workbooks or do you need to treat it in some special way?
If ignore:
Code: 
Application.DisplayAlerts = False
You can just put it in the beginning of your code, f. ex. under Application.ScreenUpdating = False
Important! Remember to activate display alerts again at the end of your code (just before End Sub):
Code: 
Application.DisplayAlerts = True
Note! The data will actually not be updated, display alerts will just suppres pop ups (all pop ups)

If treat it in some special way, we would need to write some more code.
Let me know...
 
Upvote 0
BQardi said:
Sure, but do you just want to ignore the updating of such workbooks or do you need to treat it in some special way?
If ignore:
Code: 
Application.DisplayAlerts = False
You can just put it in the beginning of your code, f. ex. under Application.ScreenUpdating = False
Important! Remember to activate display alerts again at the end of your code (just before End Sub):
Code: 
Application.DisplayAlerts = True
Note! The data will actually not be updated, display alerts will just suppres pop ups (all pop ups)

If treat it in some special way, we would need to write some more code.
Let me know...
Click to expand...


like this is perfect!!

Thanks a lot!! I'll write you again if I have other issues =)
Thank you again
Carlo
 
Upvote 0
BQardi said:
You're welcome Carlo, pleasure to be of assistance...:)
Click to expand...

Hi BQardi!
Actually I need again your help =) I promise this is gonna be my last question !


Sub Macro5()
'
' Macro5 Macro
'


'
Dim wk1 As Workbook, FileName As String


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False

Set wk1 = ThisWorkbook
FileName = Range("H62").Value

Range("E18").Select
Workbooks.Open(wk1.Path & "/" & FileName & ".xlsx").Activate
Range("F9").Select
Range("M19").GoalSeek Goal:= wk1.Sheets("CED Domenica").Select Range("D7"), ChangingCell:=Range("X19")


Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.AskToUpdateLinks = True

End Sub


I can't connect the goal to a cell in another file, can you help me?
Thanks a lot,
Carlo
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Help on complicated VBA code
Replies
3
Views
632
superfb
S
A
  • Question
VBA to save file with a different name and / or append suffixes based on conditions
Replies
1
Views
925
akshay27
A
willow1985
  • Question
Help with VBA code - Open Files as read only and don't update links
Replies
3
Views
6K
DanteAmor
DanteAmor
C
  • Question
How to modify code to update multiple workbook files which has the same format but different data?
Replies
0
Views
777
Corried
C
P
  • Question
Advice with VBA Code to Move Files
Replies
3
Views
942
Fluff
Fluff

Forum statistics

Threads
1,215,063
Messages
6,122,930
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top