Compile Error

[Matthew Scheperle]

I have a spreadsheet that when you click a button it exports the data to a
text file. The VB code compiles just fine and it works in Excel 2003.

I open the same spreadsheet on a pc with Office 2007 and click the button
and immediately get a compile error.

Any thoughts?

 

[Matthew Scheperle]

The spreadsheet I have is a 2003 version and works fine with 2003. However,
when I open the spreadsheet with Excel 2007 and click on the button to export
data it gives the message:

Compile error in hidden module: sheet 1.

So I unprotect the sheet and workbook and get the message

Compile error: can't find project or library.

I know the code below works and compiles just fine in 2003 however it does
not work in excel 2007.

I am not familiar enough with what REFERENCE to choose or add-in that would
help ...

Private Sub save_Click()
Dim FName As String
Dim RowTxt As String
Dim currPath As String
Dim naic As String
Dim taxyear As String

Dim i As Integer
Dim j As Integer

naic = UCase$(Range("B4:B4").Value)
comp = UCase$(Range("B6:B6").Value)
mode_f = Range("D44").Value
taxyear = Range("D66").Value

currPath = ActiveWorkbook.Path
FName = currPath & "\" & naic & "_TAXDATA.TXT"

Open FName For Output As #1
i = 1
j = 13
Do While i < 10000

RowTxt = naic & "||" & comp & "||" & taxyear & "||" & mode_f & "||" &
Range("A" & j).Value & "||" & Range("D" & j).Value & "||" & Range("E" &
j).Value & "||"

If (Range("A" & j).Value) <> "" And Range("D" & j).Value <> "" And
Range("E" & j).Value <> "" Then
Print #1, RowTxt
End If

i = i + 1
j = j + 1
Loop

Close #1

MsgBox ("Formated Data is stored in " & FName)

End Sub

 

[Shane Devenshire]

Hi,

Remove the button and add a new button linked to the code. The button
probably is looking for the 2003 file.

You still haven't told us which line is causing the error.

Here are a few changes that will simplify your code, but have nothing to do
with the problem.

naic = UCase(Range("B4"))
comp = UCase(Range("B6"))
mode_f = Range("D4")
taxyear = Range("D6")

In fact you can take all the .Value entries out.

I compiled your code on my machine and it did not return a compile error.

 

[Matthew Scheperle]

Hi,

It does not seem to like any of the code. I tried to compile it and it gave
this message.

Compile Error: Can't find project or library.

I tried going to the object browser and did not know which item to check and
which to uncheck.

I tried creating a new button and copied the code from the old button.
However I am more of a novice in the VB area.

I may just need to record a macro that follows the steps of exporting the
data...

Thanks,

Matt