By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,433 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Importing from Excel with a chaning range

P: 6
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
Nov 21 '06 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,492
Try linking to the spreadsheet.
That way you can read what you need and process the file accordingly.
Nov 21 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim sNow As String, sFile As String, sSheet As String 
  3. Dim xApp As Object, xWk As Object, xWs As Object 
  4.     sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm") 
  5.     sFile = "C:\My Documents\DSCP ANPR" & sNow & ".xls" 
  6.     sSheet = "VRM for Export" 
  7.     DoCmd.TransferSpreadsheet acExport, 8, sSheet, sFile, True 
  8.     Set xApp = CreateObject("Excel.Application") 
  9.     Set xWk = xApp.Workbooks.Open (sFile) 
  10.     Set xWs = xWk.Worksheets(Left(sSheet, 31)) 
  11.     xWs.Range("A1").EntireRow.Delete 
  12.     Set xWs = Nothing 
  13.     xWk.Close True 
  14.     Set xWk = Nothing 
  15.     xApp.Quit 
  16.     Set xApp = Nothing 
  17.  
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.
Nov 21 '06 #3

P: 6
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
Nov 22 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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"
Nov 22 '06 #5

P: 6
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
Nov 23 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Are tbFileName and tbFile Global variables and how and where is their value being set?

Mary
Nov 23 '06 #7

P: 6
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
Nov 23 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 23 '06 #9

Post your reply

Sign in to post your reply or Sign up for a free account.