Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Need some VBA code :)
New Posts  All Forums:Forum Nav:

Need some VBA code :)

post #1 of 34
Thread Starter 
I have a "master sheet" in excel, which basically needs to be copied to every sheet.

What the code needs to do is pull data from 2 rows in another seperate file, and paste it onto the master sheet copy and name each sheet after the vehicle ID from the other file.

Well call the main workbook file 1 and the other whick has the vehicle lists file 2.

Ill post to screens of each file.

Edit** I forgot to add that the vehicle number and vehicle must be pasted on those 2 rows on each sheet shown in file 1 (the 2086/kobota) and keep the same format.


Edited by SilentStryke - 12/16/08 at 7:18am
Little Smoky
(13 items)
 
  
CPUMotherboardGraphicsRAM
Intel E8400 Wolfdale 3.0GHz Gigabyte GA-EP45-UD3P EVGA GTX 260 896MB G.Skill 4GB DDR2 1000 
Hard DriveOptical DriveOSMonitor
Western Digital Caviar 500gb Samsung DVD +/- RW Windows 7 64 Bit 22 Acer LCD 5ms Response 
KeyboardPowerCaseMouse
Logitech Corsair 750WATT Coolermaster Sniper Logitech 
  hide details  
Reply
Little Smoky
(13 items)
 
  
CPUMotherboardGraphicsRAM
Intel E8400 Wolfdale 3.0GHz Gigabyte GA-EP45-UD3P EVGA GTX 260 896MB G.Skill 4GB DDR2 1000 
Hard DriveOptical DriveOSMonitor
Western Digital Caviar 500gb Samsung DVD +/- RW Windows 7 64 Bit 22 Acer LCD 5ms Response 
KeyboardPowerCaseMouse
Logitech Corsair 750WATT Coolermaster Sniper Logitech 
  hide details  
Reply
post #2 of 34
Just do it in an Access database with "Linked Tables". Then generate a report off the dataset.


It is doable in VBA but importing files can easily be broken. Also, the formatting must be the same every single time unless you program it robustly.
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
post #3 of 34
Thread Starter 
Quote:
Originally Posted by DuckieHo View Post
Just do it in an Access database with "Linked Tables". Then generate a report off the dataset.


It is doable in VBA but importing files can easily be broken. Also, the formatting must be the same every single time unless you program it robustly.
So I open an excel file in access, and it doesnt even have anywhere near the same file format as it once did. I dont see what you mean? Haha.
Little Smoky
(13 items)
 
  
CPUMotherboardGraphicsRAM
Intel E8400 Wolfdale 3.0GHz Gigabyte GA-EP45-UD3P EVGA GTX 260 896MB G.Skill 4GB DDR2 1000 
Hard DriveOptical DriveOSMonitor
Western Digital Caviar 500gb Samsung DVD +/- RW Windows 7 64 Bit 22 Acer LCD 5ms Response 
KeyboardPowerCaseMouse
Logitech Corsair 750WATT Coolermaster Sniper Logitech 
  hide details  
Reply
Little Smoky
(13 items)
 
  
CPUMotherboardGraphicsRAM
Intel E8400 Wolfdale 3.0GHz Gigabyte GA-EP45-UD3P EVGA GTX 260 896MB G.Skill 4GB DDR2 1000 
Hard DriveOptical DriveOSMonitor
Western Digital Caviar 500gb Samsung DVD +/- RW Windows 7 64 Bit 22 Acer LCD 5ms Response 
KeyboardPowerCaseMouse
Logitech Corsair 750WATT Coolermaster Sniper Logitech 
  hide details  
Reply
post #4 of 34
I think what Duckie means is that you can link the Excel files as tables in Access (you can also just import them if you want them all in the same file - either way works). Then you can create a report (click the report tab) and pull the data as you want. You can also format the reports to be all pretty and stuff.
     
CPUMotherboardGraphicsRAM
Q9450 @ 3.0 Gigabyte GA-P45-UD3P Evga 9800GTX+ 800/1943/1200 2x2GB OCZ 1066 LV 
Hard DriveOptical DriveOSMonitor
Vertex 2 40gb + 750gb Seagate 7200.10 Lite-On 24x DVD+-RW Windows 7 x64 LG 22" 1680x1050 
PowerCase
Corsair HX520w modular Coolermaster CM-690 
CPUMotherboardRAMHard Drive
Sempron LE-1250 Gigabyte GA-MA78GPM-DS2H 2GB (2x1GB) Corsair Ballistix 1066 2x750GB Western Digital RE2 Green power 
OSPowerCase
Windows Home Server w/ PP3 Ultra 400w modular Antec 300 
  hide details  
Reply
     
CPUMotherboardGraphicsRAM
Q9450 @ 3.0 Gigabyte GA-P45-UD3P Evga 9800GTX+ 800/1943/1200 2x2GB OCZ 1066 LV 
Hard DriveOptical DriveOSMonitor
Vertex 2 40gb + 750gb Seagate 7200.10 Lite-On 24x DVD+-RW Windows 7 x64 LG 22" 1680x1050 
PowerCase
Corsair HX520w modular Coolermaster CM-690 
CPUMotherboardRAMHard Drive
Sempron LE-1250 Gigabyte GA-MA78GPM-DS2H 2GB (2x1GB) Corsair Ballistix 1066 2x750GB Western Digital RE2 Green power 
OSPowerCase
Windows Home Server w/ PP3 Ultra 400w modular Antec 300 
  hide details  
Reply
post #5 of 34
Quote:
Originally Posted by SilentStryke View Post
So I open an excel file in access, and it doesnt even have anywhere near the same file format as it once did. I dont see what you mean? Haha.
You can create something called a "Linked Table" under the Table panel in Access. Point the linked table to your Excel file. When you change your Excel file, the data in Access gets updated too.

Then select the "Query" panel and create a dataset.

Then select the "Report" panel and create a report off the "Query".



Hmmmm.... nevermind, that gets too complicated..... let me write something up quick for you.
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
post #6 of 34
This other workbook.... Will it be a new one each time or will the data in it be just updated? Is the number of rows dynamic or set? Excel is capable of doing external workbook referencing.
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
post #7 of 34
Thread Starter 
Thanks duckie itd help me so much if you wrote something up.
The data in file 2 never changes its 2 columns, one of Vehicle ID's and Vehicle Description (shown in the second pic).
Each vehicle must simply be copied into the 2 columns on file 1 (while maintaining all the special stuff) and a seperate sheet for each, which is named after the vehicle ID.

Thanks so much if you can work something out in VBA or some other way cause I know nothing about access


Edit** This is a one time deal after all the sheets are made there going to be printed out.
Edited by SilentStryke - 12/16/08 at 11:31am
Little Smoky
(13 items)
 
  
CPUMotherboardGraphicsRAM
Intel E8400 Wolfdale 3.0GHz Gigabyte GA-EP45-UD3P EVGA GTX 260 896MB G.Skill 4GB DDR2 1000 
Hard DriveOptical DriveOSMonitor
Western Digital Caviar 500gb Samsung DVD +/- RW Windows 7 64 Bit 22 Acer LCD 5ms Response 
KeyboardPowerCaseMouse
Logitech Corsair 750WATT Coolermaster Sniper Logitech 
  hide details  
Reply
Little Smoky
(13 items)
 
  
CPUMotherboardGraphicsRAM
Intel E8400 Wolfdale 3.0GHz Gigabyte GA-EP45-UD3P EVGA GTX 260 896MB G.Skill 4GB DDR2 1000 
Hard DriveOptical DriveOSMonitor
Western Digital Caviar 500gb Samsung DVD +/- RW Windows 7 64 Bit 22 Acer LCD 5ms Response 
KeyboardPowerCaseMouse
Logitech Corsair 750WATT Coolermaster Sniper Logitech 
  hide details  
Reply
post #8 of 34
Quote:
Originally Posted by SilentStryke View Post
Thanks duckie itd help me so much if you wrote something up.
The data in file 2 never changes its 2 columns, one of Vehicle ID's and Vehicle Description (shown in the second pic).
Each vehicle must simply be copied into the 2 columns on file 1 (while maintaining all the special stuff) and a seperate sheet for each, which is named after the vehicle ID.

Thanks so much if you can work something out in VBA or some other way cause I know nothing about access
Hmmmm... do you know how to use the Macro recorder in Excel? Figure out your steps one-by-one, start the recorder, perform them, and then stop recording. Then hit CTRL+F11 to open up the code.

Drop the code here and I can figure out exactly what you are doing and optimize + make it more robust.


...wait, this is a one time deal? Now I'm really confused of what you want to do....

Take sheet two and place a counter in front. Copy down as text the block of cells below. Sort by the counter. Now you have each vehicle in double rows?
Edited by DuckieHo - 12/16/08 at 11:35am
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
post #9 of 34
Thread Starter 
Quote:
Originally Posted by DuckieHo View Post
Hmmmm... do you know how to use the Macro recorder in Excel? Figure out your steps one-by-one, start the recorder, perform them, and then stop recording. Then hit CTRL+F11 to open up the code.

Drop the code here and I can figure out exactly what you are doing and optimize + make it more robust.


...wait, this is a one time deal?
Never used the macro thing. I'm willing to give it a shot if it'll help though.
To be honest im more of a 3dsmax/maya type of guy, it is my area of expertise you could say.

And yea its a one time deal, just a simple copy paste for a few hundred vehicles. Except there all going to be on the same master template shown in the first picture, copied down from B11 to whatever the bottom one is. Then the little tab thing at the bottom will be named automatically by whatever vehicle ID is on that sheet.

Get what im trying to say?


Edit** See the 2086/kobota? in file 1 in the image, and how its copied down to fill in the columns?
Well file 2 just has 1 vehicle ID and Description so itll have to pulled and copied down the same as the master. And where "master" is on the tab will be replaced by the vehicle ID.
Edited by SilentStryke - 12/16/08 at 11:38am
Little Smoky
(13 items)
 
  
CPUMotherboardGraphicsRAM
Intel E8400 Wolfdale 3.0GHz Gigabyte GA-EP45-UD3P EVGA GTX 260 896MB G.Skill 4GB DDR2 1000 
Hard DriveOptical DriveOSMonitor
Western Digital Caviar 500gb Samsung DVD +/- RW Windows 7 64 Bit 22 Acer LCD 5ms Response 
KeyboardPowerCaseMouse
Logitech Corsair 750WATT Coolermaster Sniper Logitech 
  hide details  
Reply
Little Smoky
(13 items)
 
  
CPUMotherboardGraphicsRAM
Intel E8400 Wolfdale 3.0GHz Gigabyte GA-EP45-UD3P EVGA GTX 260 896MB G.Skill 4GB DDR2 1000 
Hard DriveOptical DriveOSMonitor
Western Digital Caviar 500gb Samsung DVD +/- RW Windows 7 64 Bit 22 Acer LCD 5ms Response 
KeyboardPowerCaseMouse
Logitech Corsair 750WATT Coolermaster Sniper Logitech 
  hide details  
Reply
post #10 of 34
Thread Starter 
Ill do some manually and upload a screen.
Little Smoky
(13 items)
 
  
CPUMotherboardGraphicsRAM
Intel E8400 Wolfdale 3.0GHz Gigabyte GA-EP45-UD3P EVGA GTX 260 896MB G.Skill 4GB DDR2 1000 
Hard DriveOptical DriveOSMonitor
Western Digital Caviar 500gb Samsung DVD +/- RW Windows 7 64 Bit 22 Acer LCD 5ms Response 
KeyboardPowerCaseMouse
Logitech Corsair 750WATT Coolermaster Sniper Logitech 
  hide details  
Reply
Little Smoky
(13 items)
 
  
CPUMotherboardGraphicsRAM
Intel E8400 Wolfdale 3.0GHz Gigabyte GA-EP45-UD3P EVGA GTX 260 896MB G.Skill 4GB DDR2 1000 
Hard DriveOptical DriveOSMonitor
Western Digital Caviar 500gb Samsung DVD +/- RW Windows 7 64 Bit 22 Acer LCD 5ms Response 
KeyboardPowerCaseMouse
Logitech Corsair 750WATT Coolermaster Sniper Logitech 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Need some VBA code :)