Help with Import function

[davidjearly]

Hi folks,

I am using the following code in one of my databases, and it is working as it should but not as I would like it to.

[SIZE=2]Dim strPathFile As String
Dim strTable As String, strBrowseMsg As String
Dim strFilter As String, strInitialDirectory As String
Dim blnHasFieldNames As Boolean[/SIZE]
[SIZE=2]
blnHasFieldNames = False
[/SIZE][SIZE=2]strBrowseMsg = "Select the EXCEL file:"[/SIZE]
[SIZE=2]
strInitialDirectory = "C:\MyFolder\"

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
 
[/SIZE][SIZE=2]strPathFile = ahtCommonFileOpenSave(InitialDir:=strInitialDirectory, _
      Filter:=strFilter, OpenFile:=False, _
      DialogTitle:=strBrowseMsg, _
      Flags:=ahtOFN_HIDEREADONLY)[/SIZE]
 
[SIZE=2]If strPathFile = "" Then
      MsgBox "No file was selected.", vbOK, "No Selection"
      Exit Sub
End If

strTable = "tablename"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
      strTable, strPathFile, blnHasFieldNames

[/SIZE]

Is there any way that this can be modified to only import a named range in the selected workbook that is navigated to? The named range would be called 'IMPORT'.

Many thanks in advance for your help.

​

 

[RuralGuy]

Have you looked up TransferSpreadsheet in VBA Help yet? You have 5 arguments in your code. The 6th argument is Range and it is explained in Help.

 

[davidjearly]

Thanks! I was just about to post that I had figured it out.

The only problem I am having now is that the blank cells in my range are importing into Access as zeros. Is there any way to prevent this?

Thanks again.

 

[RuralGuy]

The only way I know of is to go into Excel and change those cells to Text cells. I believe that works and will then import them as a ZeroLengthString ("").

 

[davidjearly]

All files in the sheet are formatted as text, but it still imports blanks as '0'. Very strange...

 

[RuralGuy]

"All files in the sheet"? I don't know what that means. Highlight several empty cells and RightClick and select Format and then Text and see what happens.

 

[John.Woody]

I've just been doing something similar attaching a spreadsheet. Access decides what type of field to use based on the data in the first few rows. Microsoft have documented a work around by selecting the relavent cells and running the following macro

Sub ConvertToText()
'
   Dim cell As Object

   For Each cell In Selection
      cell.Value = " " & cell.Value
      cell.Value = Right(cell.Value, Len(cell.Value) - 1)
   Next
'
End Sub

YOur basically adding a space at the begining of the cell and then removing it, but it converts all of the cells to text and they are readable when read in access. It has worked for me hopefully it might for you

 

[davidjearly]

RuralGuy - Yeah, I meant 'cells' sorry. I do know how to format cells and obviously I have done this. To clarify, all cells in my sheet are formatted as text.

John.Woody - thanks for this. I'll look into any way that it can be ran automatically at opening of the file, and when saved.