Linking Excel Spreadsheet to Table- Please Help!

[Breanna]

I have found an issue as I am doing this.

I created another workbook in excel to use to import the data to access. In excel there will be the main page to enter data then a hidden page that stores the data in table format. by using the =a1 sort of thing the hidden page auto populates the data from the main page. but if something is left blank on the main page the hidden page is entering 0. Do you know how to set it up so that it is just blank if there is no data in the first page? Because when I go to import to acccess I am going to have a bunch of fields that i do not need. or is there a way when I import it to leave rows out?

 

[CJ_London]

A couple of solutions:

1. modify your excel formula to not show zero (something like =if(isblank(B3),"",B3)
2. not sure if it will work for the import, but in excel options>advanced>display options, you can untick the box for 'show a zero in cells that have a zero value'
3. don't worry about it at this stage, it can be handled during the import

I am going to have a bunch of fields that i do not need

you do need them, it is just they will not have any data - as per the input sheet

 

[Breanna]

I have the excel sheet that I am importing into my table in access. On my excel sheet I set the options to not show anything in the cell if it is blank. (the sheet I am importing takes data from a different sheet with the information) My question is that when I import it access automatically sets the time to 12:00:00am even though it is showing blank in excel. This is making it difficult to sift through the data in access because it looks like an employee worked that day when they really didnt. (the excel sheet is used as a timesheet like we have been discussing in this thread)

How to I set it up so that if the excel cell is blank then when it imports over the access table shows a blank cell as well?

 

[CJ_London]

assuming you have linked to the 'hidden' excel sheet and using an append query then since there is no time, you don't need to add to your table since it is a 'non' record.

So modify your append query to not bring anything through where the time is 0

 

[Breanna]

assuming you have linked to the 'hidden' excel sheet and using an append query then since there is no time, you don't need to add to your table since it is a 'non' record.

So modify your append query to not bring anything through where the time is 0

I dont have anything linked. I just import the spreadsheet to the table.

 

[CJ_London]

not sure how you are importing but what you will need to do in that case is to delete the records you don't want - e.g. where intime=0

 

[Breanna]

not sure how you are importing but what you will need to do in that case is to delete the records you don't want - e.g. where intime=0

I am clicking the excel button from external data then it brings up like an import wizard or something and I follow the steps there to import the data to the table. So I have been deleting the rows I do not need. I was just wishing there was some way to avoid that but Im not thinking there is.

I know this is an access forum but do you know how to fix the excel sheet so that it only allows them to imput certain data but not actually change anything? or what words I should search for in order to do this.

You have been an awesome help with this so thank you for everything so far. I hope I havent been too annoying or anything.

 

[CJ_London]

You would be better off linking to the spreadsheet and using an append query

I am clicking the excel button from external data

One of the options here is to link to the worksheet - do this and you will see a table created (probably called sheet1) where you can see the data in the spreadsheet.

Now write an append query, the code of which will be something like

 INSERT INTO myTable (User, inTime, OutTime, CostCode)
 SELECT User, inTime, OutTime, CostCode
 From Sheet1

But this will import all the data as your existing import does but you only need to include from the import all records where the time element of inTime<>0. To do this you add a criteria

WHERE DatePart("h",inTime)<>0

So your total code will be

 INSERT INTO myTable (User, inTime, OutTime, CostCode)
 SELECT User, inTime, OutTime, CostCode
 From Sheet1
 WHERE DatePart("h",inTime)<>0

fix the excel sheet so that it only allows them to imput certain data

try googling 'excel validation' there are hundreds of posts