I have an excel file I need to import into 2 tables in access 03. The spreadsheet does not have any headers so it's a bit tough to import but the spreadsheet will always be in the same format. I have the code down in theory but don't use VBA too often so not sure how it would translate into VBA. Can anyone help? Below is a better description:
Sample data:
Project 55555
Project Manager Doe, John
Resource Provider 80006 WASHINGTON
BILLINGS DESIGN MECHANICAL ENG
3022................ 3021
83................... 384
The top part of the data goes into the Project Information Table, The bottom part as well as the Project ID goes into the Project Hours table. The bottom data can be only 1 record or many records, in this example it has 2 records.
So Project Info would have a record of:
55555 | Doe, John | 80006 Washington
Project Hours would have 2 records:
55555 | Billings Design | 3022 | 83
55555 | Mechanical Eng | 3021 | 384
Below is what I've come up with but need help with syntax to put it into access.
R = Row
C = Column
i = counter
j = counter
i = 1
J = 1
Do While C(i)R(j) <> "Grand Total"
if C(i)R(J) = "Project"
Append C(i + 1)R(j) to ProjectHours.Project, ProjectInfo.Project
Append C(i + 1)R(J + 1) to ProjectInfo.ProjectManager
Append C(i + 1)R(j + 2) to ProjectInfo.RecourceProvider
Append C(i)R(J + 4) to ProjectHours.SupportingOffice
Append C(i)R(j + 5) to ProjectHours.SupportingOffice2
Append C(i)R(j + 6) to ProjectHours.Hours
If C(i + 1)R(j + 4) <> Null
Append C(i + 1)R(J + 4) to ProjectHours.SupportingOffice
Append C(i + 1)R(j + 5) to ProjectHours.SupportingOffice2
Append C(i + 1)R(j + 6) to ProjectHours.Hours
i = i + 1
Else
end If
i = 1
r = r + 8
End If
Thanks a lot with any help provided.
Sample data:
Project 55555
Project Manager Doe, John
Resource Provider 80006 WASHINGTON
BILLINGS DESIGN MECHANICAL ENG
3022................ 3021
83................... 384
The top part of the data goes into the Project Information Table, The bottom part as well as the Project ID goes into the Project Hours table. The bottom data can be only 1 record or many records, in this example it has 2 records.
So Project Info would have a record of:
55555 | Doe, John | 80006 Washington
Project Hours would have 2 records:
55555 | Billings Design | 3022 | 83
55555 | Mechanical Eng | 3021 | 384
Below is what I've come up with but need help with syntax to put it into access.
R = Row
C = Column
i = counter
j = counter
i = 1
J = 1
Do While C(i)R(j) <> "Grand Total"
if C(i)R(J) = "Project"
Append C(i + 1)R(j) to ProjectHours.Project, ProjectInfo.Project
Append C(i + 1)R(J + 1) to ProjectInfo.ProjectManager
Append C(i + 1)R(j + 2) to ProjectInfo.RecourceProvider
Append C(i)R(J + 4) to ProjectHours.SupportingOffice
Append C(i)R(j + 5) to ProjectHours.SupportingOffice2
Append C(i)R(j + 6) to ProjectHours.Hours
If C(i + 1)R(j + 4) <> Null
Append C(i + 1)R(J + 4) to ProjectHours.SupportingOffice
Append C(i + 1)R(j + 5) to ProjectHours.SupportingOffice2
Append C(i + 1)R(j + 6) to ProjectHours.Hours
i = i + 1
Else
end If
i = 1
r = r + 8
End If
Thanks a lot with any help provided.
Last edited:
