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 :smile::smile:
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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 Question
Help on complicated VBA code
Replies
3
Views
749
superfb
S
A
  • Question Question
VBA to save file with a different name and / or append suffixes based on conditions
Replies
1
Views
1K
akshay27
A
willow1985
  • Question Question
Help with VBA code - Open Files as read only and don't update links
Replies
3
Views
7K
DanteAmor
DanteAmor
C
  • Question Question
How to modify code to update multiple workbook files which has the same format but different data?
Replies
0
Views
899
Corried
C
P
  • Question Question
Advice with VBA Code to Move Files
Replies
3
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

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