Modifying XL Spreadsheet Before/During Import

[kvar]

I have a fairly simple Access Form to import new records when reports are run. Reports are run from an Oracle database (no, I can't just connect to it) and put into an Excel worksheet (only output option).
So, I just have to import the excel spreadsheet into Access.
The code that I'm currently using works fine (see below), however there are several things that I need to have done to the spreadsheet before Access imports it, to stop data type errors, etc.

Reports are run too frequently, and by too many different people to just have them change the formats and run code in Excel. (I tried, won't happen)

Here are the things I need to happen to the spreadsheet first:
1. Trim() ALL of the cells in the Used Range of the Sheet. (Oracle puts SO many trailing spaces in on export that a 10 character word won't fit in a text field!)
2. Delete Rows 1 and 2. (These are like the Report "Titles", the Header names are in Row 3.)
3. Delete the Last Row in the Used Range. (After the last valid record, there is a blank row, then a row with the text "End of the Report", so Access tries to import both that row and the blank one!)
4. Convert 2 of the columns to Date data types, or formats. (but not the header, just the rest of the column.)
5. Convert 7 of the columns (non-consecutive) to a Number data type or format (with no decimals)
6. And this one would be nice, but we can function without it - One column "ISBN" is numbers, however for some reason Oracle puts ' (single quotes)' around the number. It would be great to strip those.

Okay, I know that looks like a lot, but really it should only be a few commands. The way I see it I have 2 options.
1. Have VBA alter the Spreadsheet with the above updates BEFORE the DoCmd.TransferSpreadsheet. However, I can't seem to get that to work. Especially Trim, inserting new Columns, then trimming and dragging down all the cells in the columns, then pasting the Value (not formula) back into the original column, then deleting the column you had added. Very Complex....at least to me!

2. Create a "Temp" table, with all memo fields, Transfer the spreadsheet as is into that. Then run a few queries, one to trim, one to delete rows 1, 2 and Last, and convert those data types. Then An append query to take all those records and move them to the correct table. Then another query to change all the fields in "Temp" back to Memo, and finally delete all the records in the Temp table.

Either way sounds ridiculously complex to me. And regardless, I can't seem to figure out how to do either.

If anyone has any ideas/code that could make either of those options work....Please let me know. I would also GREATLY appreciate any other suggestions on another method of doing this that I haven't thought of! (Sorry about the length of the post, didn't mean to make you read a book!)
Thank you in advance!!

Private Sub FindReport_Click()

Dim fd As FileDialog
Dim objfl As Variant
Dim FileName As String

Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.ButtonName = "Select"
.AllowMultiSelect = False
.Filters.Add "Excel Files", "*.xls"
.Title = "Choose File to Import"
.InitialView = msoFileDialogViewDetails
.Show
For Each objfl In .SelectedItems
FileName = objfl
Next objfl
On Error GoTo 0
End With

SheetName = InputBox("Enter the Sheet Name")

DoCmd.TransferSpreadsheet acImport, , "NewWork", FileName, True, SheetName & "!"


End Sub

 

[Scooterbug]

I generally do what you described in the second option. I import at least 3 files a day. (2 csv files, 1 xls file). I import them into temp tables, fix the data, then append them to their respective tables. As for the steps, based on your description you would need the to the following:

1. use a delete query to get rid of the first 2 rows and the last. This can be accomplished by running a delete query where you set the criteria for whatever fields that the header/footer information is always the same.
2. The data conversion and trimming can be done when you append the data to the new table. I would use a recordset to go through the data, fix it, then add it to the new table. Something like this:

dim db as dao.database
dim rs as dao.recordset
dim rs2 as dao.recordset
dim sField1 as string
dim sField2 as string
(etc, etc)
set db = currentdb
set rs = db.openrecordset("TempData")
set rs2 = db.openrecordset("FinalTable")
 
rs.movefirst
 
do
   do until rs.eof
         if rs![HeaderField] = "StaticData" then
            .movenext
         else
             sField1 = rs![Field1]
             sField2 = rs![Field2]
             rs2.addnew
                rs2![Field1] = trim(sField1)
                rs2![Filed2] = clng(Field2)
             rs2.update
           rs.movenext
         endif
    loop
loop until rs.eof
 
rs.close
rs2.close
 
set db = nothing
set rs = nothing
set rs2 = nothing

It's air code, so untested, but should get you pointed into the right direction

 

[kvar]

Thanks for your help! Greatly appreciated.
It did in fact get me on the right path.
What I have now, is a TransferSpreadsheet copying the data from Excel (as is) to a TempTable. Then update queries to delete the unwanted rows, Trim the spaces, then an append query, then a command to delete all the rows in the Temp Table.
I set all of the fields in "TempTable" to memo, thinking that this would prevent things from not fitting until I could get the spaces trimmed.

So, all of that works grandly, except for one issue.......2 of the columns in Excel contain fields that have dates formatted as 9-Nov-09, which Access of course does recognize as a date. (and for some reason there really aren't excessive spaces in these columns, so that's not a concern.)
But using a Memo or Text field in my converts those dates to number strings that Access can't then convert back to a date.

If I set those two fields in my "TempTable" to a Date/Time value, then it doesn't import anything in those 2 columns....because of the rows that I'm later deleting (the ones that aren't real records) contain Text!

So it's just deleting everything in those two columns.

I could leave the columns as memo, import the data, then write a command to change the columns to Date/Time type. But then it just deletes the data, since it is now a random string of numbers, and not anything Access recognizes as a date.

Is there any way to get around this??

 

[Scooterbug]

Well, when you use the transferspreadsheet command, access tries to help out by "guessing" at what the fields data type should be by analyzing the first row of the spreadsheet. So in your case, since the fields that contain dates are blank, it assumes that the fields should be text instead of a date. You can do one of two things.

1. Save the file as a CSV file. When you import a CSV file (by using the TransferText command) you can specify a Import Specification. When doing that you can force access to treat a column as whatever data type you want.
2. Open up the spreadsheet before importing and either 1)Add data to the first row so that Access imports the data with the correct data type. Or 2) Delete the first few rows manually before importing so that the first data row has a date in the correct field.

 

[kvar]

Thanks, I was afraid of that. The "Powers that be" want this to be as automated as it can be. Basically, the more user involvement required, the more room for errors (this part I agree with).

If I could just figure out how to have VBA go to the Spreadsheet, and Delete the first 2 rows before the transfer, I'd be all set. I know there MUST be a way to do this.
Or to somehow use a query or something as the "Range" for the transfer, and somehow tell it to ignore any text that appears in those 2 columns.

Either way seems possible, I just can't figure out how!
So for now someone just has to delete them manually before hand. I spelled out the instructions very clearly on the form that contains the buttons doing all this.....so hopefully that will be enough!

Thank you very much for your help!

 

[boblarson]

To delete the first two rows on the spreadsheet you can use:

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object

Set objXL = CreateObject("Excel.Application")

Set xlWB = objXL.Workbooks.Open("YourFilePathAndFileNameHere")

Set xlWS = xlWB.Worksheets(1)

    xlWS.Rows("1:2").Select
    xlApp.Selection.Delete Shift:=-4162 

xlWB.Save
xlWB.Close
objXL.Quit

Set obXL = Nothing

(air code - untested)

 

[Scooterbug]

Bob to the rescue

 

[boblarson]

Bob to the rescue

Perhaps, and perhaps not Only if it works

 

[radymski]

sorry for bumping up and old topic but i need this function working

when i try to run this it says

Run-time error '424':
Object Required

Then if i debug it highlights

xlApp.Selection.Delete Shift:=-4162

please save the day! thanks

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object

Set objXL = CreateObject("Excel.Application")

Set xlWB = objXL.Workbooks.Open("YourFilePathAndFileNameHere")

Set xlWS = xlWB.Worksheets(1)

xlWS.Rows("1:2").Select
xlApp.Selection.Delete Shift:=-4162

xlWB.Save
xlWB.Close
objXL.Quit

Set obXL = Nothing

 

[boblarson]

Change

appXL

to

objXL