Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Macro in PERSONAL.XLS will not import to my main document
Hello,
I am a complete newb with Macros, FYI. I created a Macro in my PERSONAL.XLS document that imports from an external file into my worksheet. However, it only imports to the PERSONAL.XLS and won't import to my main document. Can anybody help me out? Sorry if I didn't word something correctly. Here is my code: Sub ImportFromReceiptFile() Dim RowNdx As Long Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer Dim FName As String Dim Sep As String FName = "C:\temp\ImportFile.txt" Sep = "|" Application.ScreenUpdating = False SaveColNdx = ActiveCell.Column RowNdx = ActiveCell.Row Open FName For Input Access Read As #1 While Not EOF(1) Line Input #1, WholeLine If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If ColNdx = SaveColNdx Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) While NextPos = 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cells(RowNdx, ColNdx).Value = TempVal Pos = NextPos + 1 ColNdx = ColNdx + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend RowNdx = RowNdx + 1 Wend Application.ScreenUpdating = True Close #1 End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Macro in PERSONAL.XLS will not import to my main document
When you run the macro, make sure that the worksheet that should get the data is
active. Then alt-f8 and run your macro. It's gonna write to that activesheet. mike wrote: Hello, I am a complete newb with Macros, FYI. I created a Macro in my PERSONAL.XLS document that imports from an external file into my worksheet. However, it only imports to the PERSONAL.XLS and won't import to my main document. Can anybody help me out? Sorry if I didn't word something correctly. Here is my code: Sub ImportFromReceiptFile() Dim RowNdx As Long Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer Dim FName As String Dim Sep As String FName = "C:\temp\ImportFile.txt" Sep = "|" Application.ScreenUpdating = False SaveColNdx = ActiveCell.Column RowNdx = ActiveCell.Row Open FName For Input Access Read As #1 While Not EOF(1) Line Input #1, WholeLine If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If ColNdx = SaveColNdx Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) While NextPos = 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cells(RowNdx, ColNdx).Value = TempVal Pos = NextPos + 1 ColNdx = ColNdx + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend RowNdx = RowNdx + 1 Wend Application.ScreenUpdating = True Close #1 End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Macro in PERSONAL.XLS will not import to my main document
On Oct 29, 4:53 pm, Dave Peterson wrote:
When you run the macro, make sure that the worksheet that should get the data is active. Then alt-f8 and run your macro. It's gonna write to that activesheet. mike wrote: Hello, I am a complete newb with Macros, FYI. I created a Macro in my PERSONAL.XLS document that imports from an external file into my worksheet. However, it only imports to the PERSONAL.XLS and won't import to my main document. Can anybody help me out? Sorry if I didn't word something correctly. Here is my code: Sub ImportFromReceiptFile() Dim RowNdx As Long Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer Dim FName As String Dim Sep As String FName = "C:\temp\ImportFile.txt" Sep = "|" Application.ScreenUpdating = False SaveColNdx = ActiveCell.Column RowNdx = ActiveCell.Row Open FName For Input Access Read As #1 While Not EOF(1) Line Input #1, WholeLine If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If ColNdx = SaveColNdx Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) While NextPos = 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cells(RowNdx, ColNdx).Value = TempVal Pos = NextPos + 1 ColNdx = ColNdx + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend RowNdx = RowNdx + 1 Wend Application.ScreenUpdating = True Close #1 End Sub -- Dave Peterson- Hide quoted text - - Show quoted text - That's what I thought it would do, but it's not. I open a new document and run the macro as you said, but it imports into the PERSONAL.XLS document instead of the new document. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Macro in PERSONAL.XLS will not import to my main document
I don't see anything in the code that would cause this. Either you have
something else going on in the macro or you're doing something manually that changes the activesheet. mike wrote: <<snipped That's what I thought it would do, but it's not. I open a new document and run the macro as you said, but it imports into the PERSONAL.XLS document instead of the new document. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Macro in PERSONAL.XLS will not import to my main document
On Oct 30, 8:40 am, Dave Peterson wrote:
I don't see anything in the code that would cause this. Either you have something else going on in the macro or you're doing something manually that changes the activesheet. I posted the macro in it's entirety. mike wrote: <<snipped That's what I thought it would do, but it's not. I open a new document and run the macro as you said, but it imports into the PERSONAL.XLS document instead of the new document. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Macro in PERSONAL.XLS will not import to my main document
Then my guess is that it's something that you're doing manually.
mike wrote: On Oct 30, 8:40 am, Dave Peterson wrote: I don't see anything in the code that would cause this. Either you have something else going on in the macro or you're doing something manually that changes the activesheet. I posted the macro in it's entirety. mike wrote: <<snipped That's what I thought it would do, but it's not. I open a new document and run the macro as you said, but it imports into the PERSONAL.XLS document instead of the new document. -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Macro in PERSONAL.XLS will not import to my main document
On Oct 30, 12:38 pm, Dave Peterson wrote:
Then my guess is that it's something that you're doing manually. mike wrote: On Oct 30, 8:40 am, Dave Peterson wrote: I don't see anything in the code that would cause this. Either you have something else going on in the macro or you're doing something manually that changes the activesheet. I posted the macro in it's entirety. mike wrote: <<snipped That's what I thought it would do, but it's not. I open a new document and run the macro as you said, but it imports into the PERSONAL.XLS document instead of the new document. -- Dave Peterson -- Dave Peterson- Hide quoted text - - Show quoted text - I'm not sure what "manually" I could be doing. I open a blank document, and I hit ALT+F8 to bring up my list of macros. I select this macro. It import to the wrong sheet. Not a lot of manual things going on there. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Macro in PERSONAL.XLS will not import to my main document
Your description sounds like it should work perfectly.
Sorry, I don't have another guess. Ok. One more guess. Where is your code located? Is it in a general module or is it behind a worksheet in your personal.xls workbook? If it's behind a worksheet, then move it to a plain old module--not behind a worksheet and not behind ThisWorkbook. mike wrote: On Oct 30, 12:38 pm, Dave Peterson wrote: Then my guess is that it's something that you're doing manually. mike wrote: On Oct 30, 8:40 am, Dave Peterson wrote: I don't see anything in the code that would cause this. Either you have something else going on in the macro or you're doing something manually that changes the activesheet. I posted the macro in it's entirety. mike wrote: <<snipped That's what I thought it would do, but it's not. I open a new document and run the macro as you said, but it imports into the PERSONAL.XLS document instead of the new document. -- Dave Peterson -- Dave Peterson- Hide quoted text - - Show quoted text - I'm not sure what "manually" I could be doing. I open a blank document, and I hit ALT+F8 to bring up my list of macros. I select this macro. It import to the wrong sheet. Not a lot of manual things going on there. -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Macro in PERSONAL.XLS will not import to my main document
On Oct 31, 11:13 am, Dave Peterson wrote:
Your description sounds like it should work perfectly. Sorry, I don't have another guess. Ok. One more guess. Where is your code located? Is it in a general module or is it behind a worksheet in your personal.xls workbook? If it's behind a worksheet, then move it to a plain old module--not behind a worksheet and not behind ThisWorkbook. mike wrote: On Oct 30, 12:38 pm, Dave Peterson wrote: Then my guess is that it's something that you're doing manually. mike wrote: On Oct 30, 8:40 am, Dave Peterson wrote: I don't see anything in the code that would cause this. Either you have something else going on in the macro or you're doing something manually that changes the activesheet. I posted the macro in it's entirety. mike wrote: <<snipped That's what I thought it would do, but it's not. I open a new document and run the macro as you said, but it imports into the PERSONAL.XLS document instead of the new document. -- Dave Peterson -- Dave Peterson- Hide quoted text - - Show quoted text - I'm not sure what "manually" I could be doing. I open a blank document, and I hit ALT+F8 to bring up my list of macros. I select this macro. It import to the wrong sheet. Not a lot of manual things going on there. -- Dave Peterson- Hide quoted text - - Show quoted text - I'll bet that is what it is. I'll check it out... thanks!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import .xps document into Excel 2007 | Excel Discussion (Misc queries) | |||
A way to import a .pdf document into Excel? | Excel Discussion (Misc queries) | |||
Import Personal.XLS from 2003 to 2007? | Excel Discussion (Misc queries) | |||
Import a word document | Excel Worksheet Functions | |||
How do you import data from one Excel document to another? | Excel Discussion (Misc queries) |