Importing from Excel with a chaning range

[chrisd49]

Hi Everyone

Is it possible to import an Excel spreadsheet by range when the range is different for each sheet. There is only one sheet in the workbook. It comprises of a different number of jobs in the first column, it has weeks 1 to 52 across in thje sixth row. Also is it possible to use a fixed cell in the table to name the table it will export into. I can import the whole table easily enough but am struggling to accomodate moving ranges.

Thanks in advance

ChrisD

[NeoPa]

Try linking to the spreadsheet.
That way you can read what you need and process the file accordingly.

[MMcCarthy]

Hi Everyone

Is it possible to import an Excel spreadsheet by range when the range is different for each sheet. There is only one sheet in the workbook. It comprises of a different number of jobs in the first column, it has weeks 1 to 52 across in thje sixth row. Also is it possible to use a fixed cell in the table to name the table it will export into. I can import the whole table easily enough but am struggling to accomodate moving ranges.

Thanks in advance

ChrisD

Hi ChrisD

What you are looking at is opening the worksheet in access choosing the range and inserting into an existing table or creating a new table in Access.

Just as an example the following would delete a row in a worksheet from access.

 
Dim sNow As String, sFile As String, sSheet As String 
Dim xApp As Object, xWk As Object, xWs As Object 
    sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm") 
    sFile = "C:\My Documents\DSCP ANPR" & sNow & ".xls" 
    sSheet = "VRM for Export" 
    DoCmd.TransferSpreadsheet acExport, 8, sSheet, sFile, True 
    Set xApp = CreateObject("Excel.Application") 
    Set xWk = xApp.Workbooks.Open (sFile) 
    Set xWs = xWk.Worksheets(Left(sSheet, 31)) 
    xWs.Range("A1").EntireRow.Delete 
    Set xWs = Nothing 
    xWk.Close True 
    Set xWk = Nothing 
    xApp.Quit 
    Set xApp = Nothing 
 

Now I know you want to do something different, but you have to look at questions like do you have to search for the start and end of the range or is it a predetermined list.

Do you want to Insert each row in the range in excel as a record in a new or predefined table in access.

Think about what's involved and the amount of code. Then let me know what you want to do.

[chrisd49]

Hi
Thanks for the reply. I would have attached a couple of the spreadsheets in question to help clarify what I am trying to do (except I cant see how to do it). I know one end of the range will always be A7 and the other end will always be BB7, its the number of rows which changes. I have some code which allows me to import to a table using the excel file name to create a new table its just that it imports everything and I am lost in trying to work out the ranges.
Any assistance will be much appreciated.
ChrisD

[MMcCarthy]

Hi
Thanks for the reply. I would have attached a couple of the spreadsheets in question to help clarify what I am trying to do (except I cant see how to do it). I know one end of the range will always be A7 and the other end will always be BB7, its the number of rows which changes. I have some code which allows me to import to a table using the excel file name to create a new table its just that it imports everything and I am lost in trying to work out the ranges.
Any assistance will be much appreciated.

ChrisD

If that's the only problem you can set the range in the DoCmd.TransferSpreadsheet to "A7:BB10000". I'm just using 10,000 as an example. Pick a number you are confident the row count won't go past. Then just run a query to delete all null records from the table.

Syntax:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "TableName", "pathtofile", True, "A7:BB10000"

[chrisd49]

If that's the only problem you can set the range in the DoCmd.TransferSpreadsheet to "A7:BB10000". I'm just using 10,000 as an example. Pick a number you are confident the row count won't go past. Then just run a query to delete all null records from the table.

Syntax:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "TableName", "pathtofile", True, "A7:BB10000"

Hi
Thanks for this. I tried it and get an error message which reads
error 3709 the search key was not found in any record.
I have checked the spreadsheet to ensure that all the header fields have an entry and the format is set to general for all of them.
I have pasted my code below just incase I've done something stupid and now can't see the wood for the trees.

Private Sub bImport_Click()
On Error GoTo Err_bImport_Click

Me.tbHidden.SetFocus

If IsNull(tbFile) Or tbFile = "" Then
MsgBox "Please browse and select a valid file to import.", vbCritical, "Invalid File"
Else
If Dir("C:\Wren_Jobs\") <> "" Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tbFileName, tbFile, -1, "A6:BB40"
MsgBox "Imported your file into the table."
Else
MsgBox "Your computer does not have a valid file so the import will not work.", vbInformation
End If
End If

Exit_bImport_Click:
Exit Sub

Err_bImport_Click:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_bImport_Click

End Sub

Thanks once again

[MMcCarthy]

Are tbFileName and tbFile Global variables and how and where is their value being set?

Mary

[chrisd49]

Are tbFileName and tbFile Global variables and how and where is their value being set?

Mary

Hi Mary

Many thanks for your help with this. I am embaressed to admit I forgot to do the obvious first by cutting and pasting the data range I wanted. This threw up the problem in that the third column was based on merged cells in the spreadsheet so would not import. I changed the range by one row and now it works perfectly.

Thanks very much once again

ChrisD

[MMcCarthy]

Hi Mary

Many thanks for your help with this. I am embaressed to admit I forgot to do the obvious first by cutting and pasting the data range I wanted. This threw up the problem in that the third column was based on merged cells in the spreadsheet so would not import. I changed the range by one row and now it works perfectly.

Thanks very much once again

ChrisD

Don't worry Chris

We're all done it. The nature of the programmer is to concentrate so much on the complicated stuff the simple mistakes get made.

Mary