Manipulate Excel Data

[jo15765]

I am attempting to import an excel spreadsheet into access 2013. The issue that I have is that the column that I need to import varies, meaning it is never the same, whether it be A, B, C, D etc. What I was thinking was, let's set the search text then scan the Excel file, locate the text in the header row, then scan that row to locate the last row. Lastly, use that syntax to import that column to the last row into the database.

Meaning -> let's say that the data exists in column B and the last row is 1123 then I would want to import like such:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbltempDass", MypathDass, True, "B1: B1123"



And of course the range (the last data range) would be variables....Just do not know how to code it in Access VBA

[aytee111]

You don't need to set the range while importing. Bring in all the data and do the manipulating in Access.

[jo15765]

You don't need to set the range while importing. Bring in all the data and do the manipulating in Access.

Import the entire spreadsheet and manipulate it in access? Meaning just run delete queries to remove the uneeded columns/rows?

[aytee111]

You would import it into a temporary table and append the data to an already designed table. If the columns in Excel are truly empty then only one column will be imported.

[jo15765]

Once I import the data into a temp table in access how do I know which column of the temp table to append to the prod table? W/O eyeballing it? I was wanting to do this through VBA with no manual intervention

[aytee111]

Like I said, if only one column contains data then only one column will be imported. Otherwise, if the columns contain extra blank characters you could write a routine to read each column that has been imported to see if it contains valid data. First, ascertain whether this is necessary tho.

[jo15765]

Excellent, thank you!