Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Prompt for filename in excel macro VBA
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default Prompt for filename in excel macro VBA
Hi Folks,

I'm setting up a basic macro to copy data from one spreadsheet to the
other, the only issue I have is the sheet that the data is being copied
to will have a name that changes on a weekly basis. Is there anyway to
prompt to enter the filename? or any other way around this. My code is
attached below, its the "Windows("060313_hc.xls").Activate" where the
filename will change. Thanks in adavance


Sub UpdateCurrentWeek()
'
' UpdateCurrentWeek Macro
' Updates current week data with information from Mi Central
'

'
Sheets("ASC").Select
ChDir "\\w2k6001\shared\csdgapp\miteam\Manpower"
Workbooks.Open Filename:= _
"\\w2k6001\shared\CSDGAPP\miTeam\Manpower\AManpowe rhcv0.2.xls"
Sheets("ASC").Select
Range("D7").Select
ActiveWindow.TabRatio = 0.943
ActiveWindow.SmallScroll ToRight:=5
ActiveWindow.SmallScroll Down:=69
Range("D7:Q106").Select
Selection.Copy
Windows("060313_hc.xls").Activate
Range("D7").Select
ActiveSheet.Paste
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Leeds ASC").Select
Range("D7").Select
ActiveWindow.SmallScroll Down:=57
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
Sheets("Leeds ASC").Select
ActiveWindow.SmallScroll Down:=-12
Range("D7").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("ASC").Select
Range("A1").Select
Sheets("Leicester").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Leicester").Select
ActiveWindow.SmallScroll Down:=-6
Range("D7:T7").Select
ActiveWindow.SmallScroll Down:=84
Range("D7:T95").Select
ActiveWindow.SmallScroll Down:=-21
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
Range("D7").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("Oldbury").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Oldbury").Select
ActiveWindow.SmallScroll Down:=-9
Range("D7").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveWindow.SmallScroll Down:=81
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
ActiveWindow.SmallScroll Down:=-12
Range("D7").Select
ActiveSheet.Paste
Range("A1").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Stockport").Select
Range("D7").Select
ActiveWindow.SmallScroll ToRight:=5
ActiveWindow.SmallScroll Down:=66
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
ActiveWindow.SmallScroll Down:=-21
Sheets("Stockport").Select
Range("D7").Select
ActiveSheet.Paste
Sheets("Uddingston").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Uddingston").Select
Range("D7").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveWindow.SmallScroll Down:=72
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
Range("D7").Select
ActiveSheet.Paste
Windows("AManpowerhcv0.2.xls").Activate
ActiveWindow.Close
Range("A1").Select
Sheets("ASC").Select
Range("A1").Select
End Sub

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 718
Default Prompt for filename in excel macro VBA
filename = InputBox("Enter file name")

HTH
--
AP
a écrit dans le message de
oups.com...
Hi Folks,

I'm setting up a basic macro to copy data from one spreadsheet to the
other, the only issue I have is the sheet that the data is being copied
to will have a name that changes on a weekly basis. Is there anyway to
prompt to enter the filename? or any other way around this. My code is
attached below, its the "Windows("060313_hc.xls").Activate" where the
filename will change. Thanks in adavance


Sub UpdateCurrentWeek()
'
' UpdateCurrentWeek Macro
' Updates current week data with information from Mi Central
'

'
Sheets("ASC").Select
ChDir "\\w2k6001\shared\csdgapp\miteam\Manpower"
Workbooks.Open Filename:= _
"\\w2k6001\shared\CSDGAPP\miTeam\Manpower\AManpowe rhcv0.2.xls"
Sheets("ASC").Select
Range("D7").Select
ActiveWindow.TabRatio = 0.943
ActiveWindow.SmallScroll ToRight:=5
ActiveWindow.SmallScroll Down:=69
Range("D7:Q106").Select
Selection.Copy
Windows("060313_hc.xls").Activate
Range("D7").Select
ActiveSheet.Paste
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Leeds ASC").Select
Range("D7").Select
ActiveWindow.SmallScroll Down:=57
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
Sheets("Leeds ASC").Select
ActiveWindow.SmallScroll Down:=-12
Range("D7").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("ASC").Select
Range("A1").Select
Sheets("Leicester").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Leicester").Select
ActiveWindow.SmallScroll Down:=-6
Range("D7:T7").Select
ActiveWindow.SmallScroll Down:=84
Range("D7:T95").Select
ActiveWindow.SmallScroll Down:=-21
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
Range("D7").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("Oldbury").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Oldbury").Select
ActiveWindow.SmallScroll Down:=-9
Range("D7").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveWindow.SmallScroll Down:=81
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
ActiveWindow.SmallScroll Down:=-12
Range("D7").Select
ActiveSheet.Paste
Range("A1").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Stockport").Select
Range("D7").Select
ActiveWindow.SmallScroll ToRight:=5
ActiveWindow.SmallScroll Down:=66
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
ActiveWindow.SmallScroll Down:=-21
Sheets("Stockport").Select
Range("D7").Select
ActiveSheet.Paste
Sheets("Uddingston").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Uddingston").Select
Range("D7").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveWindow.SmallScroll Down:=72
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
Range("D7").Select
ActiveSheet.Paste
Windows("AManpowerhcv0.2.xls").Activate
ActiveWindow.Close
Range("A1").Select
Sheets("ASC").Select
Range("A1").Select
End Sub



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default Prompt for filename in excel macro VBA
I would use the GetOpenFilename method, See VBA help.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ardus Petus" wrote in message
...
filename = InputBox("Enter file name")

HTH
--
AP
a écrit dans le message de
oups.com...
Hi Folks,

I'm setting up a basic macro to copy data from one spreadsheet to the
other, the only issue I have is the sheet that the data is being copied
to will have a name that changes on a weekly basis. Is there anyway to
prompt to enter the filename? or any other way around this. My code is
attached below, its the "Windows("060313_hc.xls").Activate" where the
filename will change. Thanks in adavance


Sub UpdateCurrentWeek()
'
' UpdateCurrentWeek Macro
' Updates current week data with information from Mi Central
'

'
Sheets("ASC").Select
ChDir "\\w2k6001\shared\csdgapp\miteam\Manpower"
Workbooks.Open Filename:= _
"\\w2k6001\shared\CSDGAPP\miTeam\Manpower\AManpowe rhcv0.2.xls"
Sheets("ASC").Select
Range("D7").Select
ActiveWindow.TabRatio = 0.943
ActiveWindow.SmallScroll ToRight:=5
ActiveWindow.SmallScroll Down:=69
Range("D7:Q106").Select
Selection.Copy
Windows("060313_hc.xls").Activate
Range("D7").Select
ActiveSheet.Paste
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Leeds ASC").Select
Range("D7").Select
ActiveWindow.SmallScroll Down:=57
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
Sheets("Leeds ASC").Select
ActiveWindow.SmallScroll Down:=-12
Range("D7").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("ASC").Select
Range("A1").Select
Sheets("Leicester").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Leicester").Select
ActiveWindow.SmallScroll Down:=-6
Range("D7:T7").Select
ActiveWindow.SmallScroll Down:=84
Range("D7:T95").Select
ActiveWindow.SmallScroll Down:=-21
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
Range("D7").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("Oldbury").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Oldbury").Select
ActiveWindow.SmallScroll Down:=-9
Range("D7").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveWindow.SmallScroll Down:=81
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
ActiveWindow.SmallScroll Down:=-12
Range("D7").Select
ActiveSheet.Paste
Range("A1").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Stockport").Select
Range("D7").Select
ActiveWindow.SmallScroll ToRight:=5
ActiveWindow.SmallScroll Down:=66
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
ActiveWindow.SmallScroll Down:=-21
Sheets("Stockport").Select
Range("D7").Select
ActiveSheet.Paste
Sheets("Uddingston").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Uddingston").Select
Range("D7").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveWindow.SmallScroll Down:=72
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
Range("D7").Select
ActiveSheet.Paste
Windows("AManpowerhcv0.2.xls").Activate
ActiveWindow.Close
Range("A1").Select
Sheets("ASC").Select
Range("A1").Select
End Sub





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default Prompt for filename in excel macro VBA
Sorry guys, I'm a bit thick when it comes to things like this, what
code would i insert into the part with <<<<<< next to it as i'm
assuming this is the part i need to change, thanks:

Sheets("ASC").Select
ChDir "\\w2k6001\shared\csdgapp\miteam\Manpower"
Workbooks.Open Filename:= _
"\\w2k6001\shared\CSDGAPP\miTeam\Manpower\AManpowe rhcv0.2.xls"
Sheets("ASC").Select
Range("D7").Select
ActiveWindow.TabRatio = 0.943
ActiveWindow.SmallScroll ToRight:=5
ActiveWindow.SmallScroll Down:=69
Range("D7:Q106").Select
Selection.Copy
Windows("060313_hc.xls").Activate <<<<<<<<<<<
Range("D7").Select
ActiveSheet.Paste




Bob Phillips wrote:
I would use the GetOpenFilename method, See VBA help.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ardus Petus" wrote in message
...
filename = InputBox("Enter file name")

HTH
--
AP
a écrit dans le message de
oups.com...
Hi Folks,

I'm setting up a basic macro to copy data from one spreadsheet to the
other, the only issue I have is the sheet that the data is being copied
to will have a name that changes on a weekly basis. Is there anyway to
prompt to enter the filename? or any other way around this. My code is
attached below, its the "Windows("060313_hc.xls").Activate" where the
filename will change. Thanks in adavance


Sub UpdateCurrentWeek()
'
' UpdateCurrentWeek Macro
' Updates current week data with information from Mi Central
'

'
Sheets("ASC").Select
ChDir "\\w2k6001\shared\csdgapp\miteam\Manpower"
Workbooks.Open Filename:= _
"\\w2k6001\shared\CSDGAPP\miTeam\Manpower\AManpowe rhcv0.2.xls"
Sheets("ASC").Select
Range("D7").Select
ActiveWindow.TabRatio = 0.943
ActiveWindow.SmallScroll ToRight:=5
ActiveWindow.SmallScroll Down:=69
Range("D7:Q106").Select
Selection.Copy
Windows("060313_hc.xls").Activate
Range("D7").Select
ActiveSheet.Paste
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Leeds ASC").Select
Range("D7").Select
ActiveWindow.SmallScroll Down:=57
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
Sheets("Leeds ASC").Select
ActiveWindow.SmallScroll Down:=-12
Range("D7").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("ASC").Select
Range("A1").Select
Sheets("Leicester").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Leicester").Select
ActiveWindow.SmallScroll Down:=-6
Range("D7:T7").Select
ActiveWindow.SmallScroll Down:=84
Range("D7:T95").Select
ActiveWindow.SmallScroll Down:=-21
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
Range("D7").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("Oldbury").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Oldbury").Select
ActiveWindow.SmallScroll Down:=-9
Range("D7").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveWindow.SmallScroll Down:=81
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
ActiveWindow.SmallScroll Down:=-12
Range("D7").Select
ActiveSheet.Paste
Range("A1").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Stockport").Select
Range("D7").Select
ActiveWindow.SmallScroll ToRight:=5
ActiveWindow.SmallScroll Down:=66
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
ActiveWindow.SmallScroll Down:=-21
Sheets("Stockport").Select
Range("D7").Select
ActiveSheet.Paste
Sheets("Uddingston").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Uddingston").Select
Range("D7").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveWindow.SmallScroll Down:=72
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
Range("D7").Select
ActiveSheet.Paste
Windows("AManpowerhcv0.2.xls").Activate
ActiveWindow.Close
Range("A1").Select
Sheets("ASC").Select
Range("A1").Select
End Sub




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Prompt for filename in excel macro VBA

put this after the selection.copy statement

filename = Application.GetOpenFilename("Excel files (*.xls), *.xls")
Windows(filename).Activate

this will prompt the user to select an excel file from the Open file
dialog box and activate whichever file the user selects


--
kdp145
------------------------------------------------------------------------
kdp145's Profile: http://www.excelforum.com/member.php...o&userid=29594
View this thread: http://www.excelforum.com/showthread...hreadid=521703

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default Prompt for filename in excel macro VBA

kdp145 wrote:
put this after the selection.copy statement

filename = Application.GetOpenFilename("Excel files (*.xls), *.xls")
Windows(filename).Activate

this will prompt the user to select an excel file from the Open file
dialog box and activate whichever file the user selects


--
kdp145
------------------------------------------------------------------------
kdp145's Profile: http://www.excelforum.com/member.php...o&userid=29594
View this thread: http://www.excelforum.com/showthread...hreadid=521703

ahh, now thats looking like what i want, only problem is its giving an
error after i open the file i want. "Run-time error'9';
Subscript out of range"

Any ideas? thanks in advance

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default Prompt for filename in excel macro VBA
That is not correct, GetOpenFilename doesn't open the file, it just returns
the selected file name.

You need

Dim sFilename as string

sFilename = Application.GetOpenFilename("Excel files (*.xls), *.xls")
If sFilename < "" Then
Workbooks.Open sFilename
End If

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kdp145" wrote in
message ...

put this after the selection.copy statement

filename = Application.GetOpenFilename("Excel files (*.xls), *.xls")
Windows(filename).Activate

this will prompt the user to select an excel file from the Open file
dialog box and activate whichever file the user selects


--
kdp145
------------------------------------------------------------------------
kdp145's Profile:
http://www.excelforum.com/member.php...o&userid=29594
View this thread: http://www.excelforum.com/showthread...hreadid=521703



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 Enable Macro Prompt TheCornjerker Excel Discussion (Misc queries) 0 February 4th 08 04:59 PM
Excel macro to prompt for filename [email protected] Excel Discussion (Misc queries) 8 January 4th 07 01:31 AM
How do I insert a prompt into an Excel macro? TangoHammer Excel Discussion (Misc queries) 1 August 4th 05 09:31 PM
Prompt user for a filename J S Excel Programming 3 April 8th 05 11:15 PM
Can a MACRO prompt for the filename to open and/or save? Dave Peterson[_3_] Excel Programming 1 September 3rd 03 04:53 PM


All times are GMT +1. The time now is 09:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement