473,713 Members | 5,615 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Excel Import Problems

I'm hoping the answer to this is that I'm just doing something silly,
but I'm really scratching my head over this one. I'm importing data
from multiple workbooks, each workbook has a sheet called SubSAT and I
need to get specific data from that into the database.

I'm finding that if I have the workbook open on my desktop then the
SubSAT % complete and the High level plan % come in fine, but as soon
as the workbook is closed they come into the import table but the
target table will not accept the value. I've tried Cdbl and this
doesn't work, the difference appears to be then when it's open the
values come in as 0.69 but when it is closed they come in as 69% and
this upsets it.

I've put the offending code below, does anyone have any ideas as to
what is causing the difference in the data types, or what type
conversion will heal it?

All help much appreciated.

startcol = "A"
endcol = "M"
XLTARGET = "Temp_Data_SubS AT"
HLP_Start = 1 'high level plan start and end row are fixed and uniform
HLP_End = 16
AOD_Start = 17 'AOD list start row is fixed and uniform
AOD_End = 40 'AOD list end row is not fixed or uniform, this is as long
as I think it will get
Detail_Start = 26 'AOD detail start row, not fixed but this will be the
lowest
Detail_End = 300 'AOD detail end row, not fixed this is a guess at the
furthest it will go

DoCmd.SetWarnin gs False
DoCmd.RunSQL "delete * from Temp_Data_SubSA T" 'Clear table
DoCmd.RunSQL "delete * from CSCI_AOD_SubSAT " 'Clear table
DoCmd.RunSQL "delete * from CSCI_Build_SubS AT" 'Clear table
DoCmd.RunSQL "delete * from Temp_Data_AOD_D etail_SubSAT" 'Clear table

Set xlApp = CreateObject("E xcel.Applicatio n")
'Path of the file being imported
Workbook_Array = Array("D:\N15_C harts\FDP CTR iFACTS.xls")
'"D:\N15_Charts \FDP CTR iFACTS.xls", "D:\N15_Charts\ ADS_&_TOOLS CTR
iFACTS.xls", "D:\N15_Charts\ CMTOOLS CTR iFACTS.xls",
"D:\N15_Charts\ MSRS_&_RAP CTR iFACTS.xls", "D:\N15_Charts\ RDP & CMS CTR
iFACTS.xls", "D:\N15_Charts\ SEC CTR iFACTS.xls", "D:\N15_Charts\ WDM &
SCC CTR iFACTS.xls", "D:\N15_Charts\ CGW_&_SIS CTR iFACTS.xls",
"D:\N15_Charts\ CxSS CTR iFACTS.xls")
For Each intI In Workbook_Array
strPath = intI
'Establish workbook string
Set xlBook = xlApp.workbooks .Open(strPath, False, True)
'Loop through all the Sheets in the workbook
For Each xlSheet In xlBook.Workshee ts
On Error Resume Next
sheetRange = xlSheet.Name
If sheetRange Like "SubSAT" & "*" Then

range = startcol & HLP_Start & ":" & endcol & HLP_End

'Transfer the data from the worksheet
DoCmd.TransferS preadsheet acImport, _
acSpreadsheetTy peExcel5, _
XLTARGET, _
strPath, _
False, _
sheetRange & "!" & range

Set rst_import =
Current_DB.Open Recordset("Temp _Data_SubSAT")
Set rst_target_buil d =
Current_DB.Open Recordset("CSCI _Build_SubSAT")

rst_import.Move First
CSCI = rst_import!F1.V alue

rst_import.Move Next
rst_import.Move Next
rst_target_buil d.AddNew
rst_target_buil d![CSCI] = CSCI
rst_target_buil d![Build] = Build

rst_target_buil d!SubSAT_ETC = rst_import!F7.V alue
rst_import.Move Next
rst_target_buil d![Planned End Date] =
CDate(rst_impor t!F7.Value)
rst_import.Move Next
MsgBox "SubSAT % complete" & rst_import!F8.V alue
rst_target_buil d![SubSAT_%_Comp] = rst_import!F8.V alue
rst_import.Move Next
MsgBox "HLP % complete" & CDbl(rst_import !F9.Value)
rst_target_buil d![High_Level_Plan _%_Comp] =
CDbl(rst_import !F9.Value)
rst_target_buil d.Update
End If

Next
'MsgBox "Complete " & strPath
Next

xlBook.Applicat ion.Quit

'Error Handler used to catch error when the fields are blank
Exit Function

Exit_Handler:
On Error Resume Next

Err_Handler:
On Error Resume Next
End Function

Sep 6 '06 #1
2 3497
madeleine,

I think it is not the most easy and flexible way to import data from
Excel-worksheets but I think the main obstacle could be the numeric
datatype used in the target table. To be able to accept percentual
values this should be some form of floating point format or fractional
data accepting datatype and not some form of scaled integer type often
used when declared as numeric datatype. a cdbl-function doesn't change
the receiving field-datatype, but only will convert the source data
into double precision floating point format before deliverig it to your
target table. If this contains some form of integer datatype
(implicitly scaled integers as well) it wil throw away the fractional
data expected from a percentage-value below 100%. If a scaled type is
used with a scalefactor of 1000 (3 digits behind the decimal point) the
values first have to be muliplicated with 1000 to be accepted.

If I would import some values from Excel-sheets I would consider giving
the cells or ranges a unique and meaningful name within the spreadsheet
and importing them as tables from an Excel-sheet directly. In that case
however a setup of the Excel sheet that cannot easily be corrupted by
end-users containing other than the ecpectyed values and datatypes is
needed to prevent worthless input. It all can be done and even
automated upto some point without use of VBA. Only if full automation
and extensive detection of possible errors, full validation of the
spreadsheet and logging of all imports is needed use of VBA will not be
avoidable.

Marc

madeleine schreef:
I'm hoping the answer to this is that I'm just doing something silly,
but I'm really scratching my head over this one. I'm importing data
from multiple workbooks, each workbook has a sheet called SubSAT and I
need to get specific data from that into the database.

I'm finding that if I have the workbook open on my desktop then the
SubSAT % complete and the High level plan % come in fine, but as soon
as the workbook is closed they come into the import table but the
target table will not accept the value. I've tried Cdbl and this
doesn't work, the difference appears to be then when it's open the
values come in as 0.69 but when it is closed they come in as 69% and
this upsets it.

I've put the offending code below, does anyone have any ideas as to
what is causing the difference in the data types, or what type
conversion will heal it?

All help much appreciated.

startcol = "A"
endcol = "M"
XLTARGET = "Temp_Data_SubS AT"
HLP_Start = 1 'high level plan start and end row are fixed and uniform
HLP_End = 16
AOD_Start = 17 'AOD list start row is fixed and uniform
AOD_End = 40 'AOD list end row is not fixed or uniform, this is as long
as I think it will get
Detail_Start = 26 'AOD detail start row, not fixed but this will be the
lowest
Detail_End = 300 'AOD detail end row, not fixed this is a guess at the
furthest it will go

DoCmd.SetWarnin gs False
DoCmd.RunSQL "delete * from Temp_Data_SubSA T" 'Clear table
DoCmd.RunSQL "delete * from CSCI_AOD_SubSAT " 'Clear table
DoCmd.RunSQL "delete * from CSCI_Build_SubS AT" 'Clear table
DoCmd.RunSQL "delete * from Temp_Data_AOD_D etail_SubSAT" 'Clear table

Set xlApp = CreateObject("E xcel.Applicatio n")
'Path of the file being imported
Workbook_Array = Array("D:\N15_C harts\FDP CTR iFACTS.xls")
'"D:\N15_Charts \FDP CTR iFACTS.xls", "D:\N15_Charts\ ADS_&_TOOLS CTR
iFACTS.xls", "D:\N15_Charts\ CMTOOLS CTR iFACTS.xls",
"D:\N15_Charts\ MSRS_&_RAP CTR iFACTS.xls", "D:\N15_Charts\ RDP & CMS CTR
iFACTS.xls", "D:\N15_Charts\ SEC CTR iFACTS.xls", "D:\N15_Charts\ WDM &
SCC CTR iFACTS.xls", "D:\N15_Charts\ CGW_&_SIS CTR iFACTS.xls",
"D:\N15_Charts\ CxSS CTR iFACTS.xls")
For Each intI In Workbook_Array
strPath = intI
'Establish workbook string
Set xlBook = xlApp.workbooks .Open(strPath, False, True)
'Loop through all the Sheets in the workbook
For Each xlSheet In xlBook.Workshee ts
On Error Resume Next
sheetRange = xlSheet.Name
If sheetRange Like "SubSAT" & "*" Then

range = startcol & HLP_Start & ":" & endcol & HLP_End

'Transfer the data from the worksheet
DoCmd.TransferS preadsheet acImport, _
acSpreadsheetTy peExcel5, _
XLTARGET, _
strPath, _
False, _
sheetRange & "!" & range

Set rst_import =
Current_DB.Open Recordset("Temp _Data_SubSAT")
Set rst_target_buil d =
Current_DB.Open Recordset("CSCI _Build_SubSAT")

rst_import.Move First
CSCI = rst_import!F1.V alue

rst_import.Move Next
rst_import.Move Next
rst_target_buil d.AddNew
rst_target_buil d![CSCI] = CSCI
rst_target_buil d![Build] = Build

rst_target_buil d!SubSAT_ETC = rst_import!F7.V alue
rst_import.Move Next
rst_target_buil d![Planned End Date] =
CDate(rst_impor t!F7.Value)
rst_import.Move Next
MsgBox "SubSAT % complete" & rst_import!F8.V alue
rst_target_buil d![SubSAT_%_Comp] = rst_import!F8.V alue
rst_import.Move Next
MsgBox "HLP % complete" & CDbl(rst_import !F9.Value)
rst_target_buil d![High_Level_Plan _%_Comp] =
CDbl(rst_import !F9.Value)
rst_target_buil d.Update
End If

Next
'MsgBox "Complete " & strPath
Next

xlBook.Applicat ion.Quit

'Error Handler used to catch error when the fields are blank
Exit Function

Exit_Handler:
On Error Resume Next

Err_Handler:
On Error Resume Next
End Function
Sep 7 '06 #2
Marc

Thanks for providing me with a better insight to the joys of Excel and
importing. What I'm finding most bizarre is the fact that if the
spreadsheet is closed when I run the module, it would import quite
happily, it would take the % as a floating point number and happily
convert it. If the spreadsheet was open and visible when I ran the
module it would take the % as a % and not bring it in, which I now
understand thanks to your explanation.

The only thing I could do to get round it in the end was to make sure
that I kept the spreadsheet closed to run the module (even though the
module opens it anyway), I have to assume that somehow the data is seen
differently when it is open and visible, that the Excel formatting is
not stored with the data and the data is formatted when the spreadsheet
is made visible rather than just opened.

Your explanation of why it has a problem with the data is most handy
and I will definitely be referring to it in the future.

Thanks very much.

Madeleine
MarcHG wrote:
madeleine,

I think it is not the most easy and flexible way to import data from
Excel-worksheets but I think the main obstacle could be the numeric
datatype used in the target table. To be able to accept percentual
values this should be some form of floating point format or fractional
data accepting datatype and not some form of scaled integer type often
used when declared as numeric datatype. a cdbl-function doesn't change
the receiving field-datatype, but only will convert the source data
into double precision floating point format before deliverig it to your
target table. If this contains some form of integer datatype
(implicitly scaled integers as well) it wil throw away the fractional
data expected from a percentage-value below 100%. If a scaled type is
used with a scalefactor of 1000 (3 digits behind the decimal point) the
values first have to be muliplicated with 1000 to be accepted.

If I would import some values from Excel-sheets I would consider giving
the cells or ranges a unique and meaningful name within the spreadsheet
and importing them as tables from an Excel-sheet directly. In that case
however a setup of the Excel sheet that cannot easily be corrupted by
end-users containing other than the ecpectyed values and datatypes is
needed to prevent worthless input. It all can be done and even
automated upto some point without use of VBA. Only if full automation
and extensive detection of possible errors, full validation of the
spreadsheet and logging of all imports is needed use of VBA will not be
avoidable.

Marc

madeleine schreef:
I'm hoping the answer to this is that I'm just doing something silly,
but I'm really scratching my head over this one. I'm importing data
from multiple workbooks, each workbook has a sheet called SubSAT and I
need to get specific data from that into the database.

I'm finding that if I have the workbook open on my desktop then the
SubSAT % complete and the High level plan % come in fine, but as soon
as the workbook is closed they come into the import table but the
target table will not accept the value. I've tried Cdbl and this
doesn't work, the difference appears to be then when it's open the
values come in as 0.69 but when it is closed they come in as 69% and
this upsets it.

I've put the offending code below, does anyone have any ideas as to
what is causing the difference in the data types, or what type
conversion will heal it?

All help much appreciated.

startcol = "A"
endcol = "M"
XLTARGET = "Temp_Data_SubS AT"
HLP_Start = 1 'high level plan start and end row are fixed and uniform
HLP_End = 16
AOD_Start = 17 'AOD list start row is fixed and uniform
AOD_End = 40 'AOD list end row is not fixed or uniform, this is as long
as I think it will get
Detail_Start = 26 'AOD detail start row, not fixed but this will be the
lowest
Detail_End = 300 'AOD detail end row, not fixed this is a guess at the
furthest it will go

DoCmd.SetWarnin gs False
DoCmd.RunSQL "delete * from Temp_Data_SubSA T" 'Clear table
DoCmd.RunSQL "delete * from CSCI_AOD_SubSAT " 'Clear table
DoCmd.RunSQL "delete * from CSCI_Build_SubS AT" 'Clear table
DoCmd.RunSQL "delete * from Temp_Data_AOD_D etail_SubSAT" 'Clear table

Set xlApp = CreateObject("E xcel.Applicatio n")
'Path of the file being imported
Workbook_Array = Array("D:\N15_C harts\FDP CTR iFACTS.xls")
'"D:\N15_Charts \FDP CTR iFACTS.xls", "D:\N15_Charts\ ADS_&_TOOLS CTR
iFACTS.xls", "D:\N15_Charts\ CMTOOLS CTR iFACTS.xls",
"D:\N15_Charts\ MSRS_&_RAP CTR iFACTS.xls", "D:\N15_Charts\ RDP & CMS CTR
iFACTS.xls", "D:\N15_Charts\ SEC CTR iFACTS.xls", "D:\N15_Charts\ WDM &
SCC CTR iFACTS.xls", "D:\N15_Charts\ CGW_&_SIS CTR iFACTS.xls",
"D:\N15_Charts\ CxSS CTR iFACTS.xls")
For Each intI In Workbook_Array
strPath = intI
'Establish workbook string
Set xlBook = xlApp.workbooks .Open(strPath, False, True)
'Loop through all the Sheets in the workbook
For Each xlSheet In xlBook.Workshee ts
On Error Resume Next
sheetRange = xlSheet.Name
If sheetRange Like "SubSAT" & "*" Then

range = startcol & HLP_Start & ":" & endcol & HLP_End

'Transfer the data from the worksheet
DoCmd.TransferS preadsheet acImport, _
acSpreadsheetTy peExcel5, _
XLTARGET, _
strPath, _
False, _
sheetRange & "!" & range

Set rst_import =
Current_DB.Open Recordset("Temp _Data_SubSAT")
Set rst_target_buil d =
Current_DB.Open Recordset("CSCI _Build_SubSAT")

rst_import.Move First
CSCI = rst_import!F1.V alue

rst_import.Move Next
rst_import.Move Next
rst_target_buil d.AddNew
rst_target_buil d![CSCI] = CSCI
rst_target_buil d![Build] = Build

rst_target_buil d!SubSAT_ETC = rst_import!F7.V alue
rst_import.Move Next
rst_target_buil d![Planned End Date] =
CDate(rst_impor t!F7.Value)
rst_import.Move Next
MsgBox "SubSAT % complete" & rst_import!F8.V alue
rst_target_buil d![SubSAT_%_Comp] = rst_import!F8.V alue
rst_import.Move Next
MsgBox "HLP % complete" & CDbl(rst_import !F9.Value)
rst_target_buil d![High_Level_Plan _%_Comp] =
CDbl(rst_import !F9.Value)
rst_target_buil d.Update
End If

Next
'MsgBox "Complete " & strPath
Next

xlBook.Applicat ion.Quit

'Error Handler used to catch error when the fields are blank
Exit Function

Exit_Handler:
On Error Resume Next

Err_Handler:
On Error Resume Next
End Function
Sep 11 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3212
by: Richard Holliingsworth | last post by:
Hello: Thanks for your quick response. I'm trying to import a new Excel file into an A2K table and it's truncating the data. One of the Excel columns is a text field that can be up to 2000 characters. When I import it into A2K, that field is truncated. If I try to inport the file into an EXISTING table that I define myself where that field is a MEMO field, the import crashes.
0
5700
by: Jedawi | last post by:
Hi, would anyone be able to point me in the right direction on how to import an image (.jpg file) into an Excel worksheet using VB.Net and Option Strict On. This should be straight forward however with Excel returning some weak-typed objects we are having problems. The only problem we have is with line (full code below) oExcel.ActiveSheet.Pictures.Insert("C:\Images\AB1.jpg").Select()
1
3715
by: maztoo | last post by:
Hi I've seen this problem posted on this group in the past but I am yet to find a solution to the problem. I'm trying to import a simple spreadsheet into Access but am getting a message saying 'An error occurred trying to import the file C:\blah\blah.xls. The file was not imported'. I try and import the file by going to the file menu in Access - Get External Data - Import...
0
14419
by: Grip | last post by:
Hi, I have gone throught the group and Microsoft's online help and have seen many suggestions but I am still seeking clarity: 1. I have an excel spreadsheet. Column A contains text that may be greater than 255 characters. 2. I have an access database. I link (not import) to the contents of the excel spreadsheet. In the design view in access, Column A has the data type "memo".
0
2752
by: liam_jones | last post by:
I'm very new to Python, well IronPython to precise, and have been having problems when using Excel. The problem I'm having is the closing of my Excel object. I'm able to successfully quit the Excel Application that I create, but when I open a Workbook in the Application I can't successfully Quit Excel (by this I mean I can quit it, but the Excel process isn't getting killed and I have to manually go this through Task Manager). I've...
0
1531
by: Yvonne | last post by:
I've had on going problems trying to import data from an Excel spreadsheet in to Access 2002. I checked to make sure that the field names were EXACTLY the same, but no go. The only way round it was import to a new table then append this to the table originally intended called tblMain.. Having got fed up of doing that, I just thought I'll do an export to Excel from tblMain and then re-import from Excel back into tblMain. Still wouldn't...
3
5325
by: D.Stone | last post by:
I'm trying to import an Excel spreadsheet into an existing Access table using Office 2003. Ultimately, the plan is to do it programmatically using TransferSpreadsheet, but to check that the file has no problems, I've done it manually with the Import Spreadsheet wizard. The worksheet has 43 rows, and I import a named range defined as "=Sheet1!$C:$E". The import works, but I get a table with 64K rows, all but 43 being blank!
3
3003
by: scoots987 | last post by:
What do others do if you need to import excel files into SQL Server? My main problems are 1) zipcode formatting issues. If the column is a mix of zip and zip+4, I have problems retrieving all zipcodes. 2) If the last column contains NULL no information is imported. All this with using the Management console using Import data in SQL Server 2005. I am simply trying to import the data into NEW databases.
0
3474
by: ishay44 | last post by:
Hello! I try to build (using Visual 2005 and Excel 2007) the example described in the Microsoft Help and Support "How to automate Excel from MFC and Visual C++ 2005 or Visual C++ .NET to fill or obtain data in a range using arrays" (URL: http://support.microsoft.com/kb/308407), and I run into same problem as described in the MSDN Forums "Newbie Q regarding MFC automation client app"...
0
8795
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8701
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9302
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9167
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
6620
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5942
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4459
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4714
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2509
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.