Import excel files xls/xlsx/xlsm into access by vba code

drtopserv
drtopserv used Ask the Experts™
on
Hi,
I`d like to import excel extentionS into table in access i have the code to do that.
the issue is that the code import the file/files with row contain null/empty value.
how may i discard importing empty rows?
In out way : if all the row is empty/null don`t import it .
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
DoCmd.TransferSpreadsheet  acImport,acSpreadsheetTypeExcel12Xml, "Table1", "C:\Winxp\testing.xls", True, "" 

Commented:
DoCmd.TransferSpreadsheet  acImport,"", "Table1", "C:\Winxp\testing.xls", True, "" 

Author

Commented:
Yes it works :} thnxx:}
but what that  "" Means? in the range
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Commented:
after acImport is Excel spreadsheet type, for example Excel 2003 or Excel 2010 or xml type
like
acSpreadsheetTypeExcel12Xml
acSpreadsheetTypeExcel12

after true "" is Range
A1:B3



Commented:
more explain Range

A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

Author

Commented:
still can`t follow,
if i came to import the excel without writing "" i`ll get all the sheet include empty rows.
which parameter that tell that u wanna import only cells cotains data without empty rows?
Commented:

Sub DeleteBlankRows2()

'Deletes the entire row within the selection if _

 some of the cells WITHIN THE SELECTION contain no data.

On Error Resume Next

Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete

On Error GoTo 0

Endsub
thne use below code next
DoCmd.TransferSpreadsheet  acImport,"", "Table1", "C:\Winxp\testing.xls", True, ""

Author

Commented:
does ur code work work in access vba?

Commented:
no, you need to run first run above macro in Excel
NorieAnalyst Assistant

Commented:
There is no setting that will stop blank rows/records being imported.

Why not run a delete query after the import to delete the blank rows/records?
Commented:
You can use below code in VBA in the form command button

Private Sub Command0_Click()
DoCmd.TransferSpreadsheet  acImport,"", "Table1", "C:\Winxp\testing.xls", True, "" 
Dim test As String
test = "Delete From HOU_tblUsers Where [YourEmptyFieldName] Is Null"
DoCmd.RunSQL (test)
MsgBox "Delete Empty rows"
End Sub

You need to enter  Field name empty Fieldname [YourEmptyFieldName]

Commented:
before run need to enabled security settings
Commented:
test = "Delete From [Your Tablename] Where [YourEmptyFieldName] Is Null"

Author

Commented:
thnx,ur code help me out "}

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial