Excel Import Problems

[madeleine]

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_SubSAT"
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.SetWarnings False
DoCmd.RunSQL "delete * from Temp_Data_SubSAT" 'Clear table
DoCmd.RunSQL "delete * from CSCI_AOD_SubSAT" 'Clear table
DoCmd.RunSQL "delete * from CSCI_Build_SubSAT" 'Clear table
DoCmd.RunSQL "delete * from Temp_Data_AOD_Detail_SubSAT" 'Clear table

Set xlApp = CreateObject("Excel.Application")
'Path of the file being imported
Workbook_Array = Array("D:\N15_Charts\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.Worksheets
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.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel5, _
XLTARGET, _
strPath, _
False, _
sheetRange & "!" & range

Set rst_import =
Current_DB.OpenRecordset("Temp_Data_SubSAT")
Set rst_target_build =
Current_DB.OpenRecordset("CSCI_Build_SubSAT")

rst_import.MoveFirst
CSCI = rst_import!F1.Value

rst_import.MoveNext
rst_import.MoveNext
rst_target_build.AddNew
rst_target_build![CSCI] = CSCI
rst_target_build![Build] = Build

rst_target_build!SubSAT_ETC = rst_import!F7.Value
rst_import.MoveNext
rst_target_build![Planned End Date] =
CDate(rst_import!F7.Value)
rst_import.MoveNext
MsgBox "SubSAT % complete" & rst_import!F8.Value
rst_target_build![SubSAT_%_Comp] = rst_import!F8.Value
rst_import.MoveNext
MsgBox "HLP % complete" & CDbl(rst_import!F9.Value)
rst_target_build![High_Level_Plan_%_Comp] =
CDbl(rst_import!F9.Value)
rst_target_build.Update
End If

Next
'MsgBox "Complete " & strPath
Next

xlBook.Application.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

[MarcHG]

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_SubSAT"
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.SetWarnings False
DoCmd.RunSQL "delete * from Temp_Data_SubSAT" 'Clear table
DoCmd.RunSQL "delete * from CSCI_AOD_SubSAT" 'Clear table
DoCmd.RunSQL "delete * from CSCI_Build_SubSAT" 'Clear table
DoCmd.RunSQL "delete * from Temp_Data_AOD_Detail_SubSAT" 'Clear table

Set xlApp = CreateObject("Excel.Application")
'Path of the file being imported
Workbook_Array = Array("D:\N15_Charts\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.Worksheets
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.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel5, _
XLTARGET, _
strPath, _
False, _
sheetRange & "!" & range

Set rst_import =
Current_DB.OpenRecordset("Temp_Data_SubSAT")
Set rst_target_build =
Current_DB.OpenRecordset("CSCI_Build_SubSAT")

rst_import.MoveFirst
CSCI = rst_import!F1.Value

rst_import.MoveNext
rst_import.MoveNext
rst_target_build.AddNew
rst_target_build![CSCI] = CSCI
rst_target_build![Build] = Build

rst_target_build!SubSAT_ETC = rst_import!F7.Value
rst_import.MoveNext
rst_target_build![Planned End Date] =
CDate(rst_import!F7.Value)
rst_import.MoveNext
MsgBox "SubSAT % complete" & rst_import!F8.Value
rst_target_build![SubSAT_%_Comp] = rst_import!F8.Value
rst_import.MoveNext
MsgBox "HLP % complete" & CDbl(rst_import!F9.Value)
rst_target_build![High_Level_Plan_%_Comp] =
CDbl(rst_import!F9.Value)
rst_target_build.Update
End If

Next
'MsgBox "Complete " & strPath
Next

xlBook.Application.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

[madeleine]

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_SubSAT"
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.SetWarnings False
DoCmd.RunSQL "delete * from Temp_Data_SubSAT" 'Clear table
DoCmd.RunSQL "delete * from CSCI_AOD_SubSAT" 'Clear table
DoCmd.RunSQL "delete * from CSCI_Build_SubSAT" 'Clear table
DoCmd.RunSQL "delete * from Temp_Data_AOD_Detail_SubSAT" 'Clear table

Set xlApp = CreateObject("Excel.Application")
'Path of the file being imported
Workbook_Array = Array("D:\N15_Charts\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.Worksheets
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.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel5, _
XLTARGET, _
strPath, _
False, _
sheetRange & "!" & range

Set rst_import =
Current_DB.OpenRecordset("Temp_Data_SubSAT")
Set rst_target_build =
Current_DB.OpenRecordset("CSCI_Build_SubSAT")

rst_import.MoveFirst
CSCI = rst_import!F1.Value

rst_import.MoveNext
rst_import.MoveNext
rst_target_build.AddNew
rst_target_build![CSCI] = CSCI
rst_target_build![Build] = Build

rst_target_build!SubSAT_ETC = rst_import!F7.Value
rst_import.MoveNext
rst_target_build![Planned End Date] =
CDate(rst_import!F7.Value)
rst_import.MoveNext
MsgBox "SubSAT % complete" & rst_import!F8.Value
rst_target_build![SubSAT_%_Comp] = rst_import!F8.Value
rst_import.MoveNext
MsgBox "HLP % complete" & CDbl(rst_import!F9.Value)
rst_target_build![High_Level_Plan_%_Comp] =
CDbl(rst_import!F9.Value)
rst_target_build.Update
End If

Next
'MsgBox "Complete " & strPath
Next

xlBook.Application.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