Problem importing using MS Query
 

You could post your code (NOT workbook) for comments.

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Phil" wrote in message
...
I have a spreadsheet that imports data (open orders) from
our ODBC capable accounting package. When the data is
imported the order number is imported as a text field
instead of a number (which is the format I need). This
may be because our accounting software adds leading zeros
to complete a 7 digit field. Because the spreadsheet
requires a value of the order number I import the data
starting in column B of excel and in column A I use the
=value() function to convert the order number to a
number. The problem comes when I actually import the
data. The formulas in column A get changed only at the
end of the record set (example: I Import 50 records and 1-
45 are fine value(B1), value(B2),...until about record 45
where it skips ahead several records - value(B44), value
(B45), value(B58) value value (59), etc...). I couldn't
solve this so I added a macro to import the data and added
at the end of the macro a function to re-establish the
formulas in column A. This seemed to work, but after a
few uses the macro fix doesn't work. If i go in and copy
the formulas down manually it works again, but I don't
want to do that each time.

Any advice?

Thanks
Phil

Problem importing using MS Query
 

Don,
I inserted your macro and got the same results.
Phil

-----Original Message-----
The code you presented only copies a4:c4 down. after
refreshall
try using this from any worksheet (Input worksheet I
guess)

Sub copydn()
ActiveWorkbook.RefreshAll
[header!a4].Copy Destination:=[header!a5:a1502]
[detail!a4:c4].Copy Destination:=[detail!a5:c2036]
msgbox "DONE"
End Sub

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Phil" wrote in message
...
Sub RefreshData()
'
' RefreshData Macro
' Macro recorded 8/21/2001 by pkoops
'

'
ActiveWorkbook.RefreshAll
Sheets("Header").Select
Range("A4").Select
Selection.Copy
Range("A4:A1502").Select
ActiveSheet.Paste

Sheets("Detail").Select
Range("b4").Select
Selection.Copy
Range("b5:b2036").Select
ActiveSheet.Paste
Range("c4").Select
Selection.Copy
Range("c5:c2036").Select
ActiveSheet.Paste

Range("a4").Select
Selection.Copy
Range("a5:a2036").Select
ActiveSheet.Paste
Range("A3").Select
Sheets("Input").Select
Range("d1").Select
'MsgBox "All Data Files were successfully
Refreshed",
vbExclamation, "Refresh Data Complete"
End Sub

-----Original Message-----
You could post your code (NOT workbook) for comments.

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Phil" wrote in message
...
I have a spreadsheet that imports data (open orders)
from
our ODBC capable accounting package. When the data
is
imported the order number is imported as a text field
instead of a number (which is the format I need).
This
may be because our accounting software adds leading
zeros
to complete a 7 digit field. Because the spreadsheet
requires a value of the order number I import the
data
starting in column B of excel and in column A I use
the
=value() function to convert the order number to a
number. The problem comes when I actually import the
data. The formulas in column A get changed only at
the
end of the record set (example: I Import 50 records
and
1-
45 are fine value(B1), value(B2),...until about
record
45
where it skips ahead several records - value(B44),
value
(B45), value(B58) value value (59), etc...). I
couldn't
solve this so I added a macro to import the data and
added
at the end of the macro a function to re-establish
the
formulas in column A. This seemed to work, but
after a
few uses the macro fix doesn't work. If i go in and
copy
the formulas down manually it works again, but I
don't
want to do that each time.

Any advice?

Thanks
Phil


.



.

Problem importing using MS Query
 

Then, I am going to assume that is it somewhere in the refresh part because
all the rest of this macro is doing is copying cells down...... Try without
the copy parts. At least I have helped you on that part of it.

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Phil" wrote in message
...
Don,
I inserted your macro and got the same results.
Phil

-----Original Message-----
The code you presented only copies a4:c4 down. after
refreshall
try using this from any worksheet (Input worksheet I
guess)

Sub copydn()
ActiveWorkbook.RefreshAll
[header!a4].Copy Destination:=[header!a5:a1502]
[detail!a4:c4].Copy Destination:=[detail!a5:c2036]
msgbox "DONE"
End Sub

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Phil" wrote in message
...
Sub RefreshData()
'
' RefreshData Macro
' Macro recorded 8/21/2001 by pkoops
'

'
ActiveWorkbook.RefreshAll
Sheets("Header").Select
Range("A4").Select
Selection.Copy
Range("A4:A1502").Select
ActiveSheet.Paste

Sheets("Detail").Select
Range("b4").Select
Selection.Copy
Range("b5:b2036").Select
ActiveSheet.Paste
Range("c4").Select
Selection.Copy
Range("c5:c2036").Select
ActiveSheet.Paste

Range("a4").Select
Selection.Copy
Range("a5:a2036").Select
ActiveSheet.Paste
Range("A3").Select
Sheets("Input").Select
Range("d1").Select
'MsgBox "All Data Files were successfully
Refreshed",
vbExclamation, "Refresh Data Complete"
End Sub

-----Original Message-----
You could post your code (NOT workbook) for comments.

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Phil" wrote in message
...
I have a spreadsheet that imports data (open orders)
from
our ODBC capable accounting package. When the data
is
imported the order number is imported as a text field
instead of a number (which is the format I need).
This
may be because our accounting software adds leading
zeros
to complete a 7 digit field. Because the spreadsheet
requires a value of the order number I import the
data
starting in column B of excel and in column A I use
the
=value() function to convert the order number to a
number. The problem comes when I actually import the
data. The formulas in column A get changed only at
the
end of the record set (example: I Import 50 records
and
1-
45 are fine value(B1), value(B2),...until about
record
45
where it skips ahead several records - value(B44),
value
(B45), value(B58) value value (59), etc...). I
couldn't
solve this so I added a macro to import the data and
added
at the end of the macro a function to re-establish
the
formulas in column A. This seemed to work, but
after a
few uses the macro fix doesn't work. If i go in and
copy
the formulas down manually it works again, but I
don't
want to do that each time.

Any advice?

Thanks
Phil


.



.