Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Prompt for File -- Neophite Needs HELP
I've been searching to try and fix this problem high & low on the
Internet, and I can't seem to get this sucker quite right. I know it's an easy problem, but I have nil VBA knowledge, so this is nothing more than a basic Macro. I'm trying to automate my stock screening info as much as possible, and I want to change the below Macro to prompt me for the CSV file name so it's not linked to a certain file path. I tried to do the GetFilenameOpen help dialogue in VBA, but I can't get it to work right. ANY help would be greatly appreciated. Here's the code in the Macro: Sub StockImport() ' ' StockImport Macro ' Macro recorded 09-12-2005 by ' ' Keyboard Shortcut: Ctrl+Shift+I ' Workbooks.Open Filename:= _ "C:\Documents and Settings\MyName\My Documents\Money\Stock Screener\Raw Screen Data.CSV" Range("A2:N50").Select Selection.Copy Windows("Screen Research.xls").Activate Range("A6").Select ActiveSheet.Paste Application.CutCopyMode = False Range("B4").Select ActiveCell.FormulaR1C1 = [Now] Range("B5").Select Windows("Raw Screen Data.CSV").Activate ActiveWindow.SmallScroll Down:=-33 ActiveWorkbook.Close End Sub I have lots more I want to do with this puppy, but I'm trying to take baby steps...thanks SOOO much in advance (I've used alot of brain power on this one)... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Prompt for File -- Neophite Needs HELP
I know the part I need to chg is the "C:\Documents and
Settings\MyName\My Documents\Money\Stock Screener\Raw Screen Data.CSV" -- I'd like to be able to point this to any CSV file (so it's not only limited to my computer), but when I play around with it I end up screwing up the entire macro... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Prompt for File -- Neophite Needs HELP
One way:
Public Sub StockImport() Dim wbSource As Workbook Dim sFileName As String Dim rDest As Range Application.ScreenUpdating = False sFileName = Application.GetOpenFilename If Len(sFileName) 0 Then With Workbooks("Screen Research.xls").Sheets(1) Set rDest = .Range("A6") With .Range("B4") .NumberFormat = "dd mmmm yyyy hh:mm:ss" .Value = Now End With End With Set wbSource = Workbooks.Open(Filename:=sFileName, Format:=2) With wbSource.Sheets(1).Range("A2:N50") rDest.Resize(.Rows.Count, .Columns.Count).Value = .Value End With wbSource.Close End If Application.ScreenUpdating = True End Sub In article .com, "kilo1990" wrote: I've been searching to try and fix this problem high & low on the Internet, and I can't seem to get this sucker quite right. I know it's an easy problem, but I have nil VBA knowledge, so this is nothing more than a basic Macro. I'm trying to automate my stock screening info as much as possible, and I want to change the below Macro to prompt me for the CSV file name so it's not linked to a certain file path. I tried to do the GetFilenameOpen help dialogue in VBA, but I can't get it to work right. ANY help would be greatly appreciated. Here's the code in the Macro: Sub StockImport() ' ' StockImport Macro ' Macro recorded 09-12-2005 by ' ' Keyboard Shortcut: Ctrl+Shift+I ' Workbooks.Open Filename:= _ "C:\Documents and Settings\MyName\My Documents\Money\Stock Screener\Raw Screen Data.CSV" Range("A2:N50").Select Selection.Copy Windows("Screen Research.xls").Activate Range("A6").Select ActiveSheet.Paste Application.CutCopyMode = False Range("B4").Select ActiveCell.FormulaR1C1 = [Now] Range("B5").Select Windows("Raw Screen Data.CSV").Activate ActiveWindow.SmallScroll Down:=-33 ActiveWorkbook.Close End Sub I have lots more I want to do with this puppy, but I'm trying to take baby steps...thanks SOOO much in advance (I've used alot of brain power on this one)... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Prompt for File -- Neophite Needs HELP
Do I replace my macro with everything you have up there? Sorry for the
silly question... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Prompt for File -- Neophite Needs HELP
Ok, I did replace it, and it actually found the file!! But then the
debug command came up and highlighted the following row: With Workbooks("Screen Research.xls").Sheet5.Select Looks like it's confused on where to paste the data. The workbook has about 15 worksheets in it, and the tab I want to paste the data is "Last Import" So how do I specify that particular spreadsheet? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Prompt for File -- Neophite Needs HELP
If I understand you correctly,
With Workbooks("Screen Research.xls").Sheets("Last Import) Note, there's no .Select at the end. You very, very, very rarely have to select/activate cells in order to address them - while the recorder uses selections, you should get out of the habit of doing so as soon as you can. In article .com, "kilo1990" wrote: Ok, I did replace it, and it actually found the file!! But then the debug command came up and highlighted the following row: With Workbooks("Screen Research.xls").Sheet5.Select Looks like it's confused on where to paste the data. The workbook has about 15 worksheets in it, and the tab I want to paste the data is "Last Import" So how do I specify that particular spreadsheet? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Prompt for File -- Neophite Needs HELP
Here's what I replaced it with:
With Workbooks("Screen Research.xls").Worksheet("Last Import").Select Here's the error it pops up: Runtime error '9': Subscript out of range What does that mean? I feel like it's so close... |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Prompt for File -- Neophite Needs HELP
Thanks for the tip! But it still doesn't work. Same line selected,
with the same error given...bummer 'cause I thought that would fix it... |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Prompt for File -- Neophite Needs HELP
kilo
Try this if Screen Reasearch.xls not already active. With Workbooks("Screen Research.xls") .Activate .Sheets("Last_Import").Select End With Gord Dibben Excel MVP On 17 Dec 2005 11:33:53 -0800, "kilo1990" wrote: Here's what I replaced it with: With Workbooks("Screen Research.xls").Worksheet("Last Import").Select Here's the error it pops up: Runtime error '9': Subscript out of range What does that mean? I feel like it's so close... |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Prompt for File -- Neophite Needs HELP
What was the error?
In article .com, "kilo1990" wrote: Thanks for the tip! But it still doesn't work. Same line selected, with the same error given...bummer 'cause I thought that would fix it... |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Prompt for File -- Neophite Needs HELP
Where would I stick that in?
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Prompt for File -- Neophite Needs HELP
JE,
A new error..which I consider progress! Here's the error: Runtime error '438': Object doesn't support this property or method Here's the highlighted line: With Workbooks("Screen Research old.xls").Worksheet("Last Import") |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Prompt for File -- Neophite Needs HELP
No progress - my typo. Should be
...Worksheets(... not ...Worksheet(... In article . com, "kilo1990" wrote: A new error..which I consider progress! Here's the error: Runtime error '438': Object doesn't support this property or method Here's the highlighted line: With Workbooks("Screen Research old.xls").Worksheet("Last Import") |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Prompt for File -- Neophite Needs HELP
THAT'S IT --- IT WORKS FLAWLESSLY!!!!! THANKS A BUNCH!!!
JE McGimpsey wrote: No progress - my typo. Should be ...Worksheets(... not ...Worksheet(... In article . com, "kilo1990" wrote: A new error..which I consider progress! Here's the error: Runtime error '438': Object doesn't support this property or method Here's the highlighted line: With Workbooks("Screen Research old.xls").Worksheet("Last Import") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to stop file open macro prompt after deleting all macros? | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
How to CANCEL file SAVE PROMPT when MACRO is running? | Excel Discussion (Misc queries) | |||
How do I insert a prompt into an Excel macro? | Excel Discussion (Misc queries) | |||
Prompt for file name in a macro | Excel Discussion (Misc queries) |