MACRO PROBLEMS

  • Thread starter Thread starter D VanD
  • Start date Start date
D

D VanD

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
 
Frank,

Here you go.


Sheets("FEEDER").Select
Cells.Select
Selection.ClearContents
Selection.QueryTable.Delete
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"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
 
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
 
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 said:
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






----== 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
Click to expand...
=---
 
Back
Top