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

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

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.

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

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

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

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.

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

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!!!