Hello,

Here is the situation. I created a macro to pull information from one
worksheet and into another based on date. This part works fine, the problem
I run into is the last column is not capturing all the information. The
field contains entries like this:

686
YDF
YEW
752
710
715

It grabs all the entries with numbers, but not the all letter entries. Any
ideas. Both columns are formatted as TEXT.

The funny thing is when I import this data into FoxPro, it grabs the letter
entries, but not the numbers.

Hi
you may post the relevant parts of your macro. Otherwise it's quite
difficult to provide solutions to you

--
Regards
Frank Kabel
Frankfurt, Germany

D VanD wrote:
Hello,

Here is the situation. I created a macro to pull information from
one
worksheet and into another based on date. This part works fine, the
problem I run into is the last column is not capturing all the
information. The field contains entries like this:

686
YDF
YEW
752
710
715

It grabs all the entries with numbers, but not the all letter
entries. Any ideas. Both columns are formatted as TEXT.

The funny thing is when I import this data into FoxPro, it grabs the
letter entries, but not the numbers.

Frank,

Here you go.


Sheets("FEEDER").Select
Cells.Select
Selection.ClearContents
Selection.QueryTable.Delete
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=Excel Files;DBQ=S:\SRS\Central Error
Correction\KOP-2.xls;DefaultDir=S:\SRS\Central Error
Correction;DriverId=22;MaxBufferS" _
), Array("ize=2048;PageTimeout=5;")), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT `KOP$`.DATE, `KOP$`.`RPT TYPE`, `KOP$`.USER, `KOP$`.`CLAIM
#`, `KOP$`.TRANSACTION, `KOP$`.ERROR, `KOP$`.ACTION, `KOP$`.STATUS,
`KOP$`.`REQ #`, `KOP$`.FCO" & Chr(13) & "" & Chr(10) & "FROM `S:\SRS\Central
Error Correction\" _
, _
"KOP-2`.`KOP$` `KOP$`" & Chr(13) & "" & Chr(10) & "WHERE
(`KOP$`.DATE={ts '2004-02-01 00:00:00'} And `KOP$`.DATE<={ts '2004-02-29
00:00:00'})" _
)
.Name = "Query from Excel Files_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sheets("FEEDER").Select
Sheets("FEEDER").Copy
ActiveWorkbook.SaveAs Filename:= _
"S:\SRS\Central Error Correction\KOP User.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWindow.Close
End Sub

The problem is with the FCO field in the Select statement




"Frank Kabel" wrote in message
...
Hi
you may post the relevant parts of your macro. Otherwise it's quite
difficult to provide solutions to you

--
Regards
Frank Kabel
Frankfurt, Germany

D VanD wrote:
Hello,

Here is the situation. I created a macro to pull information from
one
worksheet and into another based on date. This part works fine, the
problem I run into is the last column is not capturing all the
information. The field contains entries like this:

686
YDF
YEW
752
710
715

It grabs all the entries with numbers, but not the all letter
entries. Any ideas. Both columns are formatted as TEXT.

The funny thing is when I import this data into FoxPro, it grabs the
letter entries, but not the numbers.

It's not something a simple as the column width is it?

Text is ranged left and numerics ranged right, are there spurious spaces,
unprintable characters in the data?

I cannot see anythnig worng with the structure of the Query, so it must be a
format problem.

Cheers
Nigel

"D VanD" wrote in message
...
Frank,

Here you go.


Sheets("FEEDER").Select
Cells.Select
Selection.ClearContents
Selection.QueryTable.Delete
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=Excel Files;DBQ=S:\SRS\Central Error
Correction\KOP-2.xls;DefaultDir=S:\SRS\Central Error
Correction;DriverId=22;MaxBufferS" _
), Array("ize=2048;PageTimeout=5;")), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT `KOP$`.DATE, `KOP$`.`RPT TYPE`, `KOP$`.USER, `KOP$`.`CLAIM
#`, `KOP$`.TRANSACTION, `KOP$`.ERROR, `KOP$`.ACTION, `KOP$`.STATUS,
`KOP$`.`REQ #`, `KOP$`.FCO" & Chr(13) & "" & Chr(10) & "FROM
`S:\SRS\Central
Error Correction\" _
, _
"KOP-2`.`KOP$` `KOP$`" & Chr(13) & "" & Chr(10) & "WHERE
(`KOP$`.DATE={ts '2004-02-01 00:00:00'} And `KOP$`.DATE<={ts '2004-02-29
00:00:00'})" _
)
.Name = "Query from Excel Files_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sheets("FEEDER").Select
Sheets("FEEDER").Copy
ActiveWorkbook.SaveAs Filename:= _
"S:\SRS\Central Error Correction\KOP User.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWindow.Close
End Sub

The problem is with the FCO field in the Select statement




"Frank Kabel" wrote in message
...
Hi
you may post the relevant parts of your macro. Otherwise it's quite
difficult to provide solutions to you

--
Regards
Frank Kabel
Frankfurt, Germany

D VanD wrote:
Hello,

Here is the situation. I created a macro to pull information from
one
worksheet and into another based on date. This part works fine, the
problem I run into is the last column is not capturing all the
information. The field contains entries like this:

686
YDF
YEW
752
710
715

It grabs all the entries with numbers, but not the all letter
entries. Any ideas. Both columns are formatted as TEXT.

The funny thing is when I import this data into FoxPro, it grabs the
letter entries, but not the numbers.







----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

Any other ideas, it is the wierdest thing.

Here is how data is received.

1. Data exported from FoxPro to Excel
2. User then transfers Excel data to another spreadsheet. Work is done on
file and at the end of month. Goto Step 3
3. Run macro which transfers data to another workbook
All letter entries are not picked up after macro is ran
4. Data imported back into FoxPro from the spreadsheet in Step 3
Number entries are not picked up, but the all letter entries are

I tried doing all of this using SUMPRODUCT because I need a count, but I am
dealing with about 10,000 lines of data and I have four conditions and it
takes FOREVER.

Any ideas?

Thanks


"Nigel" wrote in message
...
It's not something a simple as the column width is it?

Text is ranged left and numerics ranged right, are there spurious spaces,
unprintable characters in the data?

I cannot see anythnig worng with the structure of the Query, so it must be
a
format problem.

Cheers
Nigel

"D VanD" wrote in message
...
Frank,

Here you go.


Sheets("FEEDER").Select
Cells.Select
Selection.ClearContents
Selection.QueryTable.Delete
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=Excel Files;DBQ=S:\SRS\Central Error
Correction\KOP-2.xls;DefaultDir=S:\SRS\Central Error
Correction;DriverId=22;MaxBufferS" _
), Array("ize=2048;PageTimeout=5;")), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT `KOP$`.DATE, `KOP$`.`RPT TYPE`, `KOP$`.USER,
`KOP$`.`CLAIM
#`, `KOP$`.TRANSACTION, `KOP$`.ERROR, `KOP$`.ACTION, `KOP$`.STATUS,
`KOP$`.`REQ #`, `KOP$`.FCO" & Chr(13) & "" & Chr(10) & "FROM
`S:\SRS\Central
Error Correction\" _
, _
"KOP-2`.`KOP$` `KOP$`" & Chr(13) & "" & Chr(10) & "WHERE
(`KOP$`.DATE={ts '2004-02-01 00:00:00'} And `KOP$`.DATE<={ts
'2004-02-29
00:00:00'})" _
)
.Name = "Query from Excel Files_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sheets("FEEDER").Select
Sheets("FEEDER").Copy
ActiveWorkbook.SaveAs Filename:= _
"S:\SRS\Central Error Correction\KOP User.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
_
CreateBackup:=False
ActiveWindow.Close
End Sub

The problem is with the FCO field in the Select statement




"Frank Kabel" wrote in message
...
Hi
you may post the relevant parts of your macro. Otherwise it's quite
difficult to provide solutions to you

--
Regards
Frank Kabel
Frankfurt, Germany

D VanD wrote:
Hello,

Here is the situation. I created a macro to pull information from
one
worksheet and into another based on date. This part works fine, the
problem I run into is the last column is not capturing all the
information. The field contains entries like this:

686
YDF
YEW
752
710
715

It grabs all the entries with numbers, but not the all letter
entries. Any ideas. Both columns are formatted as TEXT.

The funny thing is when I import this data into FoxPro, it grabs the
letter entries, but not the numbers.







----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---