Import Macro in PERSONAL.XLS will not import to my main document

M

mike

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
 
D

Dave Peterson

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.
 
M

mike

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.
















--

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.
 
D

Dave Peterson

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

mike

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.
 
M

mike

Then my guess is that it's something that you're doing manually.







--

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.
 
D

Dave Peterson

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.
 
M

mike

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.







--

Dave Peterson- Hide quoted text -

- Show quoted text -

I'll bet that is what it is. I'll check it out... thanks!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top