hi all,
problem:
transferring data from workbooks that are stored in a particular directory from excel to a table in access. when done, the workbooks that have been processed are automatically moved into a directory named “processed” . all using vba.
this is the pretty complex vb problem that i need some expert solution to.
overview.
i have created a cash reconciliation application in excel saved by month for each of my bus operator clients. when this cash reconciliation is completed this is saved to a certain directory. for illustrative purposes, i would have the following workbooks at any instance in a directory:
c:\\cashreconci liation
containing workbook files:
cashreconciliat ion of bribie for march 2007-05-15
cashreconciliat ion of sunbus for march 2007-05-15
cashreconciliat ion of caboolture for march 2007-05-15
cashreconciliat ion of clarks for march 2007-05-15
the layout of each workbook are exactly the same.
each tab in each workbook represents the days in a month. for example, for cashreconciliat ion of bribie for march 2007-05-15. worksheet1 is named 1st march 2007, worksheet2 is named 2nd march 2007 and so on……
each worksheet layout is identical, except they obviously contain different data corresponding to the relevant day.
each line between rows 8 to row 250 in each each worksheet contains data about each bus driver, their driver name, driver number, cash takings, adjustment amount, adjustment reasons, tickets sold, machine number(they use to issue tickets).
now here is the challenge.. i need some complex code that will go to each sheet (representing each day of the month)…. go to each record/line and upload each row of data contained in the excel columns named:
driver name, driver number, cash takings, adjustment reasons and tickets sold
to a table i created in access named : bus operator adjustments
(field names are the same i.e driver name, driver number, cash takings, adjustment reasons and tickets sold )
This whole process needs to be done for each workbook in the directory:
c:\\cashreconci liation
for example (as above):
cashreconciliat ion of bribie for march 2007-05-15
cashreconciliat ion of sunbus for march 2007-05-15
cashreconciliat ion of caboolture for march 2007-05-15
cashreconciliat ion of clarks for march 2007-05-15
once this vba code is run for each workbook, i would like each workbook automatically moved to :
c:\\cashreconci liation\process ed
another request is to identify the most recent copied records from this whole process above in the table, using some sort of identifier like a temp file in access, then using vba, move it to the actual real file at any time., so we can do our own reconciliation. between workbooks and copied information.
That is briefly my problem identification. im a beginner vba programmer slowly getting better. need some help from anyone out there that has done something similar/ or someone that knows vba like a pro..
I done some research and know we can use ADO or the DoCmd.TransferS preadsheet function.
I just don’t know how to actually write this synax and the best way to do this.
thankyou sooooo much in advance for reading this post and hopefully coming up with a solution for me..
thankyou again to all….
2  1845  Rabbit 12,516
Recognized Expert Moderator MVP
Why would you have to do it one element at a time? Can't you just import the entire excel table, do whatever you need to do with that table, and then move the file?
This can probably be done with 5-10 lines of code.
 ADezii 8,834
Recognized Expert Expert
hi all,
problem:
transferring data from workbooks that are stored in a particular directory from excel to a table in access. when done, the workbooks that have been processed are automatically moved into a directory named “processed” . all using vba.
this is the pretty complex vb problem that i need some expert solution to.
overview.
i have created a cash reconciliation application in excel saved by month for each of my bus operator clients. when this cash reconciliation is completed this is saved to a certain directory. for illustrative purposes, i would have the following workbooks at any instance in a directory:
c:\\cashreconci liation
containing workbook files:
cashreconciliat ion of bribie for march 2007-05-15
cashreconciliat ion of sunbus for march 2007-05-15
cashreconciliat ion of caboolture for march 2007-05-15
cashreconciliat ion of clarks for march 2007-05-15
the layout of each workbook are exactly the same.
each tab in each workbook represents the days in a month. for example, for cashreconciliat ion of bribie for march 2007-05-15. worksheet1 is named 1st march 2007, worksheet2 is named 2nd march 2007 and so on……
each worksheet layout is identical, except they obviously contain different data corresponding to the relevant day.
each line between rows 8 to row 250 in each each worksheet contains data about each bus driver, their driver name, driver number, cash takings, adjustment amount, adjustment reasons, tickets sold, machine number(they use to issue tickets).
now here is the challenge.. i need some complex code that will go to each sheet (representing each day of the month)…. go to each record/line and upload each row of data contained in the excel columns named:
driver name, driver number, cash takings, adjustment reasons and tickets sold
to a table i created in access named : bus operator adjustments
(field names are the same i.e driver name, driver number, cash takings, adjustment reasons and tickets sold )
This whole process needs to be done for each workbook in the directory:
c:\\cashreconci liation
for example (as above):
cashreconciliat ion of bribie for march 2007-05-15
cashreconciliat ion of sunbus for march 2007-05-15
cashreconciliat ion of caboolture for march 2007-05-15
cashreconciliat ion of clarks for march 2007-05-15
once this vba code is run for each workbook, i would like each workbook automatically moved to :
c:\\cashreconci liation\process ed
another request is to identify the most recent copied records from this whole process above in the table, using some sort of identifier like a temp file in access, then using vba, move it to the actual real file at any time., so we can do our own reconciliation. between workbooks and copied information.
That is briefly my problem identification. im a beginner vba programmer slowly getting better. need some help from anyone out there that has done something similar/ or someone that knows vba like a pro..
I done some research and know we can use ADO or the DoCmd.TransferS preadsheet function.
I just don’t know how to actually write this synax and the best way to do this.
thankyou sooooo much in advance for reading this post and hopefully coming up with a solution for me..
thankyou again to all….
I just want to make sure that I am crystal clear on exactly what you are requesting and illustrating: - You have 4 Workbooks representing 4 bus operator clients.
- Each Workbook consists of 31 individual Worksheets named 1st March 2007, 2nd March 2007,...31st March 2007.
- The data from all 124 Worksheets has to transferred to and appended to an Access Table named Bus Operator Adjustments.
- Only Rows 8 through 250 on each Worksheet contain relevant data and need to be imported.
- Once an individual Workbook has been processed, it should automatically be moved to a Directory named C:\CashReconcil iation\Processe d.
- The most recently imported Records from this process need to somehow be uniquely identified, so that at any given time they can be moved back into the actual file so that you can perform your own reconciliation.
- Is this correct? Did I miss anything?
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: J Daniel Melton |
last post by:
Hello,
I am using late binding in a managed VC++ .NET 2003 application. I used KB
302902 (for C#) as a starting point and converted it to managed C++. I built
a managed class that is intantiated by a form. The class can instantiate
Excel, open a new workbook, add a new worksheet, get a range, and put some
data in it. So, I have basically just duplicated what KB 302902 does, except
I am working out of a managed C++ class.
The problem...
|
by: kkbahuguna |
last post by:
Dim Appxl as Excel.application
Dim WBook as Excel.workbooks
Dim WSheet as Excel.worksheet
FileName = "c:\Temp\New.xls"
Set Appxl=CreateObject("Excel.Application")
Set WBook = Appxl.Workbooks
Set WSheet = WBook.Add.Worksheets.item(1)
|
by: kkbahuguna |
last post by:
Dim Appxl as Excel.application
Dim WBook as Excel.workbooks
Dim WSheet as Excel.worksheet
FileName = "c:\Temp\New.xls"
Set Appxl=CreateObject("Excel.Application")
Set WBook = Appxl.Workbooks
Set WSheet = WBook.Add.Worksheets.item(1)
|
by: mike11d11 |
last post by:
I was able to create three worksheets in my workbook, but when I go to
add the 4th I get an Invalid Index error. I must be leaving something
out to when adding 4 or more sheets. Thanks
Dim oExcel As Object
Dim oBook As Object
Dim oSheet1500 As Object
Dim oSheetARPPD As Object
Dim oSheetDFDDNA As Object
Dim oSheetDNE As Object
|
by: bfrank1972 |
last post by:
I want to be able to get a list of all custom named fields in an Excel worksheet, but I am having trouble with this. In the code below, access to a field that I named "DEALCODE" works fine - I get the value. When I try to get a collection of named cells in the loop below, I come up with only one entry: 'Portfolio Company Information'!Print_Area
The part before the ! is obviously my sheet name, but I have a whole slew of custom named cells in...
| | |
by: ukchat |
last post by:
I.m creating a dynamic query to pull out workbooks from my database the table structure is below.
Table: curricworkbooks
Columns: ID, curric, assessment, topic, workbook, filename
Example data
1 N1/E1.1 Numeracy E1 Count 1 workbooks/Num Entry 1/Unit 1/04 N1E1.1-3 Worksheets Num.pdf
10 MSS1/E1.6 Numeracy E1 Capacity 13 workbooks/Num Entry 1/Unit 13/04 MSS1E1.6 Worksheets Num.pdf
|
by: grego9 |
last post by:
I have a problem in Excel 2000. I have written some VBA code that transfers data from the current excel workbook to a file called "Deal Input2.xls". Everything transfers ok apart from the date in cell ("G28"). This gets converted into an American date in Deal input 2.xls. So even though the date in the current workbook is entered as 02/05/2008 the info gets transferred to deal input2.xls as 05/02/2008. I've played around by trying to change...
|
by: Claudia d'Amato |
last post by:
I would like to do something in a *.vbs script and all the operations should be applied
on each worksheet within an Excel file.
How do I do this?
It must be something like:
for i in (1 .. lastworksheetnumber) do
...operations
end
|
by: MarkDotNet |
last post by:
Hi
I am trying to switch Excel worksheets in VBA. I get an error saying "subscript out of range". Please Help- Here is code (Fails on last 2 lines- Note that I ommited the recordset portion of code because it works):
Dim objXLApp As Object
Dim objXLws As Object
Dim strSDocPath As String 'Full path/name of template (Source) file
Dim strTPath As String 'Full path of (Target) file
Dim strTDocPath As String 'Full path/name of...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
