J
J. Trucking
Hello All,
I have been struggling with this for the last few days and have
searched the web and can't figure out why I get this error (I've found
possible solutions but I can't seem to figure out why this is
happening). I have a crosstab query that I export to Excel. It is an
employee chart which shows the hours worked on certain projects over
certain dates (In Hours). I'll try and write what it looks like:
Name Project 01/01/08 01/02/08 01/03/08
01/04/08 01/05/08 01/06/08 etc....
John Doe 200 8
8 8 5
100
8 8 3
Jane Smith 202 3
8
4
100 5
8 4
300
8 8
I hope this makes sense. I use a form in my DB for a user to select a
date range. They hit a Command Button and from here I use
TransferSpreadsheet. I can get this to work if I make the date range
fairly short, say 15 days. But as soon as I go over this, I get a
runtime error 3190 "Too Many Fields". So I tried the date range of an
entire month, and I simply ran the query (w/o the export). I went in
and found that I had 29 rows by 29 columns. But out of this, only
about 175 fields were filled in. Am I over a limit?....I've heard
that the limit for fields is anywhere from 127 - 255 fields. Is Excel
running in the background which is causing this? I am allowing the
user to select the directory to save the file to (using API Save Box)
when using TransferSpreadsheet if that makes a difference. The export
goes into a template on a certain worksheet. The code I use is as
follows (I used to have a "kill" line but since the user selects where
the file will be saved, the db always responded by telling me "File
Not Found" so I took it out):
' I haven't shown declared variables
Set db = CurrentDb
pathdir = CurrentProject.Path
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open(pathdir &
"\TemplateFile.xlt")
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)",
"*.xls")
strInputFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter,_
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
objXLBook.SaveAs (strInputFileName)
objXLBook.Close
DoCmd.TransferSpreadsheet acExport,_
acSpreadsheetTypeExcel9, "qryCrosstabEmployees",strInputFileName, True
I really need to be able to automatically export this data into a
worksheet that I can format. Is there some sort of a work around to
this or am I doing something wrong. Any help would be greatly
appreciated. Thanks in advance.
John
I have been struggling with this for the last few days and have
searched the web and can't figure out why I get this error (I've found
possible solutions but I can't seem to figure out why this is
happening). I have a crosstab query that I export to Excel. It is an
employee chart which shows the hours worked on certain projects over
certain dates (In Hours). I'll try and write what it looks like:
Name Project 01/01/08 01/02/08 01/03/08
01/04/08 01/05/08 01/06/08 etc....
John Doe 200 8
8 8 5
100
8 8 3
Jane Smith 202 3
8
4
100 5
8 4
300
8 8
I hope this makes sense. I use a form in my DB for a user to select a
date range. They hit a Command Button and from here I use
TransferSpreadsheet. I can get this to work if I make the date range
fairly short, say 15 days. But as soon as I go over this, I get a
runtime error 3190 "Too Many Fields". So I tried the date range of an
entire month, and I simply ran the query (w/o the export). I went in
and found that I had 29 rows by 29 columns. But out of this, only
about 175 fields were filled in. Am I over a limit?....I've heard
that the limit for fields is anywhere from 127 - 255 fields. Is Excel
running in the background which is causing this? I am allowing the
user to select the directory to save the file to (using API Save Box)
when using TransferSpreadsheet if that makes a difference. The export
goes into a template on a certain worksheet. The code I use is as
follows (I used to have a "kill" line but since the user selects where
the file will be saved, the db always responded by telling me "File
Not Found" so I took it out):
' I haven't shown declared variables
Set db = CurrentDb
pathdir = CurrentProject.Path
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open(pathdir &
"\TemplateFile.xlt")
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)",
"*.xls")
strInputFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter,_
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
objXLBook.SaveAs (strInputFileName)
objXLBook.Close
DoCmd.TransferSpreadsheet acExport,_
acSpreadsheetTypeExcel9, "qryCrosstabEmployees",strInputFileName, True
I really need to be able to automatically export this data into a
worksheet that I can format. Is there some sort of a work around to
this or am I doing something wrong. Any help would be greatly
appreciated. Thanks in advance.
John