Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 3rd 05, 12:14 AM
jenkinspat
 
Posts: n/a
Default Opening a file with code without a set file name


Sorry for lack of details.

Im using simple Excel code in a Module here's what im using.

-----
Sub ExtractLatData()
'
'

Sheets("stby").Select
Application.ScreenUpdating = False
ChDir "C:\Pt23 Spread"
Workbooks.Open Filename:="C:\Pt23 Spread\sensor.xls"
Sheets("RSR Summary").Select
Rows("1:14").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("A:L").Select
Selection.Copy
Application.DisplayAlerts = False

ActiveWindow.Close
Sheets("RSR Report Data (Errors)").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
ActiveWorkbook.Save
Sheets("Control").Select
Application.ScreenUpdating = True
MsgBox " Finished, Exit EXCEL and open the ACCESS file to view
Report "
End Sub
-------
The sensor file name is named different every day and the user has to
change the name before running the code. Of course half of them forget
to. The file contents of the file are the same except the changes for
the test data so it just the (set) name im trying to make easier.
Or maby a code that will change the name for them, maby.

Thank you,
Pat









JulieD Wrote:
Hi

it would probably help if we could see the code, if you'ld like to copy
&
paste it (or at least the bit where the file name is referenced) in a
reply
this would give us a starting point ...
and also to know where you run the code from - is it stored in personal
and
run via tools / macro / macros in the newly created workbook or ??

Cheers
JulieD


"jenkinspat" wrote in
message
. ..-

Hi all and thanks in advance for any help.

I have excel workbooks that are produced on a daily bases by another
program. The data layout is the same for each day produced except for
the file name. What im having to do now is rename the file every day
and then run my other code to clean it up and import the data into my
new workbook.
The code that i use works great but would like to be able to fix it
so
it doesn't matter what the file name is, so that others won't have to
remember to change the name before they run the cleanup code. Any
ideas.
Thanks, Pat


--
jenkinspat-



--
jenkinspat

  #2   Report Post  
Old March 3rd 05, 03:40 PM
JulieD
 
Posts: n/a
Default

Hi

how about something like

dim fname as string

fname = inputbox("What is the name of the file?")

Sheets("stby").Select
Application.ScreenUpdating = False
ChDir "C:\Pt23 Spread"
Workbooks.Open Filename:="C:\Pt23 Spread\" & fname & ".xls"

'rest of your code
------
Cheers
JulieD


"jenkinspat" wrote in message
. ..

Sorry for lack of details.

Im using simple Excel code in a Module here's what im using.

-----
Sub ExtractLatData()
'
'

Sheets("stby").Select
Application.ScreenUpdating = False
ChDir "C:\Pt23 Spread"
Workbooks.Open Filename:="C:\Pt23 Spread\sensor.xls"
Sheets("RSR Summary").Select
Rows("1:14").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("A:L").Select
Selection.Copy
Application.DisplayAlerts = False

ActiveWindow.Close
Sheets("RSR Report Data (Errors)").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
ActiveWorkbook.Save
Sheets("Control").Select
Application.ScreenUpdating = True
MsgBox " Finished, Exit EXCEL and open the ACCESS file to view
Report "
End Sub
-------
The sensor file name is named different every day and the user has to
change the name before running the code. Of course half of them forget
to. The file contents of the file are the same except the changes for
the test data so it just the (set) name im trying to make easier.
Or maby a code that will change the name for them, maby.

Thank you,
Pat









JulieD Wrote:
Hi

it would probably help if we could see the code, if you'ld like to copy
&
paste it (or at least the bit where the file name is referenced) in a
reply
this would give us a starting point ...
and also to know where you run the code from - is it stored in personal
and
run via tools / macro / macros in the newly created workbook or ??

Cheers
JulieD


"jenkinspat" wrote in
message
. ..-

Hi all and thanks in advance for any help.

I have excel workbooks that are produced on a daily bases by another
program. The data layout is the same for each day produced except for
the file name. What im having to do now is rename the file every day
and then run my other code to clean it up and import the data into my
new workbook.
The code that i use works great but would like to be able to fix it
so
it doesn't matter what the file name is, so that others won't have to
remember to change the name before they run the cleanup code. Any
ideas.
Thanks, Pat


--
jenkinspat-



--
jenkinspat





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
Opening a file with code without a set file name jenkinspat Excel Discussion (Misc queries) 1 March 2nd 05 03:39 PM
opening file problem please help Steve Goodrich Excel Discussion (Misc queries) 2 March 1st 05 10:40 PM
Opening a file with a Macro Adam1 Chicago Excel Discussion (Misc queries) 2 February 28th 05 10:13 PM
Opening CSV file in Excel 97 Ron P Excel Discussion (Misc queries) 4 January 14th 05 10:41 PM
restrict opening Excel file residing on a shared drive to one user k-ham Excel Discussion (Misc queries) 1 January 7th 05 01:57 AM


All times are GMT +1. The time now is 10:42 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017